SQLTeam.com | Weblogs | Forums

CASE When logic


#1

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.


#2

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

#3

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.