I have the following case statement that I am struggling to get to work. I can see what the problem is but am unsure on how to fix it.
CASE
WHEN DateDiff(n,Oppo_Opened,GetDate()) < CASE WHEN CONVERT(Time, Oppo_Opened) > '16:00:00' THEN 960 ELSE 60 END THEN 60 - DateDiff(n,Oppo_Opened,GetDate())
WHEN DateDiff(n,Oppo_Opened,GetDate()) < CASE WHEN CONVERT(Time, Oppo_Opened) > '14:00:00' THEN 1080 ELSE 180 END THEN 180 - DateDiff(n,Oppo_Opened,GetDate())
WHEN CONVERT(Date,DATEADD(hh,7.5,getdate())) = CONVERT(Date,Oppo_Opened) THEN DateDiff(n,CONVERT(Time, GetDate()),'17:30:00')
WHEN GetDate()< Oppo_Opened + 1 THEN DateDiff(n,GetDate(),Oppo_Opened +1)
ELSE 0
END Mins
The first 2 when statements are always using either the 60 or 180 and subtracts from the Datediff statement, but it should use either the 960/60 depending on the previous statement. I am hoping this makes sense to someone.
If I understand correctly, easiest is probably just to reuse the WHEN CASE in the THEN:
CASE
WHEN DateDiff(n,Oppo_Opened,GetDate()) < CASE WHEN CONVERT(Time, Oppo_Opened) > '16:00:00' THEN 960 ELSE 60 END
THEN CASE WHEN CONVERT(Time, Oppo_Opened) > '16:00:00' THEN 960 ELSE 60 END - DateDiff(n,Oppo_Opened,GetDate())
WHEN DateDiff(n,Oppo_Opened,GetDate()) < CASE WHEN CONVERT(Time, Oppo_Opened) > '14:00:00' THEN 1080 ELSE 180 END
THEN CASE WHEN CONVERT(Time, Oppo_Opened) > '14:00:00' THEN 1080 ELSE 180 END - DateDiff(n,Oppo_Opened,GetDate())
WHEN CONVERT(Date,DATEADD(hh,7.5,getdate())) = CONVERT(Date,Oppo_Opened)
THEN DateDiff(n,CONVERT(Time, GetDate()),'17:30:00')
WHEN GetDate()< Oppo_Opened + 1
THEN DateDiff(n,GetDate(),Oppo_Opened +1)
ELSE 0
END Mins
Thankyou, I have resolved this with your help.
I have another query:
How can I work out the number of minutes between 2 dates (ensuring that the working day 8am - 5.30pm) is catered for please.