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.
| Author |
Topic |
|
bsterner
Starting Member
2 Posts |
 Posted - 2007-11-26 : 15:56:53
|
| Well, not so complex for some of the experts, but it is for me.Description:1) There are 3 tables, we'll call them (main, session, & recs)2) session has FK to main3) recs has FK to session4) There can by multiple session records per day, but only one of the session records can have an associated rec record. So, main table could have 3 associated session records for today, but only one of those session records can have an associated rec entry. Again, this limit is on a per day basis.I want to query for today and say "give me the record in the main table + the record in the rec table (if there is one for today), otherwise null values for these columns. Thing I'm struggling with is not returning multiple records if there are multiple session entries or returning something if there are no session and or rec entries.I was hoping to avoid a complex case statement, but would this be the way to go?I may need to rethink my table structure. Any suggestions or code would be appreciated tremendously.Thanks in advance |
|
|
gregj
1 Post |
Posted - 2007-11-26 : 16:29:14
|
| I think this may do it:select distinct main.*, recs.* from main join session on session.key1 = main.key1 and session.date >= convert(varchar(10),getdate(),101) and session.date <= (convert(varchar(10),getdate(),101) + '23:59:59') left join recs on recs.key2 = session.key2You could probably speed it up by creating variables to calculate and store the beginning and end dates. That is, assuming you've indexed the session.date field. |
 |
|
|
bsterner
Starting Member
2 Posts |
Posted - 2007-11-26 : 17:19:39
|
| Thanks gregj. The key was using the 'and' with the left join. Simple enough.Thanks for the help |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-27 : 01:14:58
|
| and session.date >= convert(varchar(10),getdate(),101)and session.date <= (convert(varchar(10),getdate(),101) + '23:59:59')can be replaced asand session.date >= dateadd(day,datediff(day,0,getdate()),0)and session.date < dateadd(day,datediff(day,0,getdate()),1)MadhivananFailing to plan is Planning to fail |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-27 : 01:18:57
|
quote:
can SHOULD be replaced as
by specifying 23:59:59 you are still missing one whole second or 1000 millisecond. If any record falls within there, it will not be included.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
kutumbarao
Starting Member
13 Posts |
Posted - 2007-11-27 : 04:52:53
|
| HI,I hope, this condition also ok for thatCONVERT(DATETIME,CONVERT(VARCHAR(10), session.date, 101)) = CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),101)) |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-27 : 04:54:34
|
quote:
Originally posted by kutumbarao
HI,I hope, this condition also ok for thatCONVERT(DATETIME,CONVERT(VARCHAR(10), session.date, 101)) = CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),101))
This will work also but when you are applying functions to the column you discourage SQL Server to use indexes if there are any. Use madhivanan's method for better performance
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-27 : 07:27:06
|
quote:
Originally posted by kutumbarao
HI,I hope, this condition also ok for thatCONVERT(DATETIME,CONVERT(VARCHAR(10), session.date, 101)) = CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),101))
Dont compare VARCHARs; compare DATEs  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Complex Join