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 |
|
martes
Starting Member
13 Posts |
 Posted - 2011-06-22 : 09:17:22
|
What I am trying to do is to determine which date band my data falls within. We have a table, Date_Bands, that is updated each evening to calculate the start and end date based upon user created rules. Queries are then run to determine the band by checking the query date against the start and end dates on the [Date_Bands] table.There is a row on Date_Bands with a [Date_Band_Id] of 18, a [Date_Band] of 'No Date', [Start_Date] of 1861-04-03 00:00:00.000 and an [End_Date] of 1961-04-03 00:00:00.000.If I run the simple query below, it returns 3 rows as I would expect, with most of the fields being NULL because there is no data on Accounts for that [Contract_Id].SELECT T1.[Contract_Id] ,T1.[Ccy_Amount] ,T3.[Date_Band] ,T3.[Date_Band_Id] ,ISNULL(T2.[Date_First]. '1900-01-01T12:01:00')FROM [Fact_Limits] AS T1LEFT OUTER JOIN [Accounts] AS T2 ON T1.[Contract_Id] = T2.[Contract_Id]LEFT OUTER JOIN [Date_Bands] AS T3 ON (ISNULL(T2.[Date_First]. '1900-01-01T12:01:00') >= T3.[Start_Date] AND ISNULL(T2.[Date_First]. '1900-01-01T12:01:00') < T3.[End_Date])WHERE CAST(T1.[Category] AS INT) < 10000 AND T1.[Contract_Id] = 123456 However, because it finds no matches, the Accounts date field (T2.[Date_First]) is NULL, so I would expect that to be set to '1900-01-01T12:01:00' which is greater than T3.[Start_Date] and less than T3.[End_Date], and then return 18 to T3.[Date_Band_Id] and 'No Date' to T3.[Date_Band]. But both columns return NULL.If I add a further test of T3.[Date_Band_Id] = 18 in the WHERE clause, nothing gets returned, for obvious reasons.I suspect it has something to do with the way NULLs are treated, but I don't have the knowledge to know if this is so, and why?Can anyone shed any light on why this is happending, and how I can overcome it? |
|
|
|
|
|
|
|
No Data being returned with LEFT OUTER JOIN