Check for data before next day 10 AM-CodePudding

Need to implement logic If actual is <= Next day 10 AM then its 1 else 0

This is my test record

Create table #tmp (ID int, Actual Datetime)

Insert into #tmp values (1,'2022-01-09 13:55:29.340')

Insert into #tmp values (2,'2022-01-07 13:55:29.340')

Insert into #tmp values (3,'2022-01-08 13:55:29.340')

select * from #tmp

Expected Output

ID  Actual                    1/0   
1   2022-01-09 13:55:29.340    0  
2   2022-01-07 13:55:29.340    1  
3   2022-01-08 13:55:29.340    1

For ID 1 it should be 0 because Actual is 06 and it is after next day 10 AM. ID 2 and 3 should be 1 because it is less than or equal to next day before 10 AM.

I am stuck in how to check 10 AM for next day. please help me on this.

CodePudding user response：

Use this query :

select *,case when Actual<=DATEADD(HOUR, 10, CAST(CAST(DATEADD(day, 1, GETDATE()) AS DATE) AS DATETIME)) then 0 else 1 end as '1/0'  from #tmp

today date is 2022-01-07 and my result is:

ID  Actual                     1/0
1   2022-01-06 13:55:29.340     0
2   2022-01-07 13:55:29.340     0
3   2022-01-08 13:55:29.340     1

If you want to compare with today then:

select *,case when Actual<=DATEADD(HOUR, 10, CAST(GETDATE() AS DATE) AS DATETIME)) then 0 else 1 end as '1/0'  from #tmp

Output:

ID  Actual                     1/0
1   2022-01-06 13:55:29.340     0
2   2022-01-07 13:55:29.340     1
3   2022-01-08 13:55:29.340     1

CodePudding user response：

You can get the next day (from current datetime) at 00:00 as follows

DECLARE @nextday DATE = DATEADD(DAY, 1, CAST(GETDATE() AS DATE))

Then you need to add addtional 10 hours. But as the result of the above is a DATE and you can't add hours to a DATE you have to cast back to DATETIME again.

DECLARE @limit DATETIME = DATEADD(HOUR, 10, CAST(@nextday AS DATETIME))

Or having it all together in one expression

DECLARE @limit DATETIME = DATEADD(HOUR, 10, CAST(DATEADD(Day , 1, CAST(GETDATE() AS DATE)) AS DATETIME))

Then you can get your required 0/1 column with a CASE

SELECT id, actual, CASE WHEN actual <= @limit THEN 1 ELSE 0 END
FROM #tmp

But be aware this will select everything that is less than the next day at 10:00 AM, ie also all past data. If you don't want that, you probably will need a lower limit too.