join query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

join query

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-01-25 : 11:25:06

Assume the time now is 8:15:00

I would like to show the records from tblMain which do not fall within the fromTime and toTime in tblTimes.

Example:

tblTimes

ID fromTime toTime
123 1900-01-01 07:40:00 1900-01-01 09:00:00
123 1900-01-01 09:50:00 1900-01-01 10:30:00
...

tblMain
ID fieldName1 fieldName2
1
2
3
...
123
...

select * from tblMain
if (ID in tblMain is present in tblTimes and the present time falls between fromTime and toTime then do not show the ID i.e. 123 )

so the result should show:

ID fieldName1 fieldName2
1
2
3
...
...

BUT if the time now is 07:35:00
the result of the select query should be the following as the present time does not fall within the fromTime and toTime:

ID fieldName1 fieldName2
1
2
3
...
123
...

I have the following query but it does not seem correct.
declare @CurrentTime varchar(20)
set @CurrentTime = convert(varchar(10),getdate(),108)

select
...
...
from
@tblmain as r
left join tblTimes as s on r.ID = s.ID
and convert(datetime, @CurrentTime) < convert(datetime, convert(varchar(10),s.fromTime,108))
and convert(datetime, @CurrentTime) > convert(datetime, convert(varchar(10),s.toTime,108))

Any thoughts please?
Thanks

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-26 : 07:42:49

Check this and let me know if it works?

declare @CurrentTime Time
set @CurrentTime = cast(convert(varchar(10),getdate(),108) as time)

Select tblMain.*
From tblMain
Inner Join tblTimes on tblTimes.ID=tblMain.ID
And @CurrentTime < Cast (convert(varchar(5),tblTimes.FromTime,108) as time)
And @CurrentTime > Cast (convert(varchar(5),tblTimes.ToTime,108) as Time)

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-01-26 : 07:56:35

As I am doing alot of processing, I have decided to do this in stages...
And I am using between instead of > or <
Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources