CODEWITHSUNDEEP
sqlsql-servercasesql-view
Sara Logan
Sara Logan

Reputation: 55

Code reported date change in SQL case expression

CASE  
   WHEN CAST([SYS_Created_On] as time) >= CAST('16:00' as time) 
      THEN DATEADD(dd, 1, CAST([SYS_Created_On] AS DATE))
      ELSE CAST([SYS_Created_On] AS DATE)  
END AS [Reported Date],
CASE 
   WHEN CAST([resolved_at] as time) >= CAST('16:00' as time) 
      THEN DATEADD(dd, 1, AS([resolved_at] AS DATE)) 
      ELSE CAST([resolved_at] AS DATE) 
END AS [Resolved Date]

I get data from Monday - Friday everyday after 4PM. Can someone help me how to code this to be if the reported date is Friday after 4 or Saturday or Sunday have a reported date of Monday.

enter image description here

Upvotes: 0

Views: 110

Answers (1)

Srinika Pinnaduwage
Srinika Pinnaduwage

Reputation: 1042

If I understood you correctly, you need to have a Reported Date as on coming Monday, in case the data received and the time is after 4:00 PM, or SYS_Created_On is a Saturday or Sunday you need to get the date corresponding to Monday

While there are different ways which are more elegant (in the sense that lesser code), the following is more descriptive.

CASE  
    WHEN CAST([SYS_Created_On] as time) >= CAST('16:00' as time) and DATENAME(WEEKDAY,CAST([SYS_Created_On] AS DATE)) = 'Friday' 
      THEN DATEADD(dd, 3, CAST([SYS_Created_On] AS DATE))
    WHEN DATENAME(WEEKDAY,CAST([SYS_Created_On] AS DATE)) = 'Saturday' 
      THEN DATEADD(dd, 2, CAST([SYS_Created_On] AS DATE))
    WHEN DATENAME(WEEKDAY,CAST([SYS_Created_On] AS DATE)) = 'Sunday' 
      THEN DATEADD(dd, 1, CAST([SYS_Created_On] AS DATE))
    WHEN CAST([SYS_Created_On] as time) >= CAST('16:00' as time) 
      THEN DATEADD(dd, 1, CAST([SYS_Created_On] AS DATE))
      ELSE CAST([SYS_Created_On] AS DATE)  
END AS [Reported Date],

Upvotes: 1

Related Questions