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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-06-06 : 09:23:27
|
| Kristian writes "My records have data ranges (StartDate and EndDate).StartDate and/or EndDate can be NULL in the table.I want to execute a SELECT statement with the current date and include records with nulls.Here is my first WHERE clause:--WHERE ( StartDate = NULL OR StartDate < @ChangeDate )AND ( EndDate = NULL OR @ChangeDate < EndDate ) --Is this the most efficient way, or is there a NULL setting I can use. ( I did consider using ISNULL() but this would perform more comparisons)regardsKris" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-06-06 : 09:27:20
|
| When you compare with NULL use IS instead of =WHERE ( StartDate IS NULL OR StartDate < @ChangeDate )AND ( EndDate IS NULL OR @ChangeDate < EndDate ) --MadhivananFailing to plan is Planning to fail |
 |
|
|
kristianwhittick
Starting Member
12 Posts |
Posted - 2006-06-06 : 10:50:19
|
| Sorry, that was not my question.The = was a typing mistake.My real question is, Is there a better syntax that is more efficient ?i.e. somthing like this (this is a guess)WHERE ISNULL(StartDate < @ChangeDate, TRUE)AND ISNULL(@ChangeDate < EndDate, TRUE) |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-06-06 : 10:56:36
|
You can also try this.WHERE StartDate < coalesce( @ChangeDate, StartDate )AND EndDate > coalesce( @ChangeDate, EndDate ) Not sure if this is more efficient than what madhivanan posted. Give it a try.
KH |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-06-06 : 12:55:35
|
According to this...http://www.sommarskog.se/dyn-search.html (search for 'Alternate Key Lookup'), it might be better (i.e. more efficient) to do this...WHERE ( StartDate IS NOT NULL AND StartDate < @ChangeDate )AND ( EndDate IS NOT NULL AND @ChangeDate < EndDate ) It's a funny old world  Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
|
|
|
|
|
Include NULL data in comparison