Currently, I need to get the correct data to be inputted into temp table. There are 3 datetimes that I need to compare. However not all values are populated.
Example-Assume that all dates are the same
| Data1 | DateTime1 | DateTime2 | DateTime3 |
|---|---|---|---|
| First | 2020-08-24 14:00:00.000 | 2020-08-24 14:30:00.000 | 2020-08-24 15:30:00.000 |
| Second | NULL | 2020-08-24 13:00:00.000 | 2020-08-24 14:30:00.000 |
| Third | NULL | NULL | 2020-08-24 10:00:00.000 |
From the example, as you can see there are some null values in the columns. I need to get the value of DateTime1 first, if null get the value of DateTime2. If DateTime2 is also null, get DateTime3.
After getting the values, I need to compare it and see if it is less than 2 hours before putting it into the temp table.
Example, If current time is 2pm = 1400Hrs
Final Output that I want to show
| Data1 | DateTime1 | DateTime2 | DateTime3 |
|---|---|---|---|
| First | 2020-08-24 14:00:00.000 | 2020-08-24 14:30:00.000 | 2020-08-24 15:30:00.000 |
| Second | NULL | 2020-08-24 13:00:00.000 | 2020-08-24 14:30:00.000 |
Advertisement
Answer
It sounds like you want to select the rows that have a datetime field in the last two hours. You can’t use a function like COALESCE because that would prevent the use of any indexes that cover the columns. If you used eg :
WHERE COALESCE(DateTime1,DateTime2,DateTime3) >DATEADD(HOUR,-2,GETDATE())
The server wouldn’t be able to use any indexes that cover the date fields and would be forced to scan the entire table to evaluate the function output and filter condition
A simple WHERE should work, without even checking for NULL, as any comparison with NULL fails :
declare @cutoff datetime=DATEADD(HOUR,-2,GETDATE())
SELECT .....
WHERE
DateTime1 > @cutoff
OR DateTime2 > @cutoff
OR DateTime3 > @cutoff
If you want to compare only the time part, the only efficient way would be to extract the time part into separate fields using the time type and index them. Extracting the time part with , eg cast(DateTime1 as time) would prevent the use of indexing.
The following condition would work by scanning the entire table :
declare @cutoff time=cast(DATEADD(HOUR,-2,GETDATE()) as time); SELECT ... WHERE cast(COALESCE(DateTime1,DateTime2,DateTime3) as time) > @cutoff