SQL Computed Column Case with Multiple Criteria

Question

I'm having trouble using Case statement for multiple criteria, I don't know how to nest another CASE.

DateTime column: Control_OpenDateOwner
DateTime column: Control_ClosedDateRev

Computed column:

(case when [Control_ClosedDateRev] IS NULL then '1' else '0' end)

Trying to I want to calculate when the OPENDATE is greater than 4 Days opened (using today()) and the close date is Null, put a 1 (open record and has more than 4 days opened or 0 if the record is closed or less than 4 days opened, I can't get it to work.

Note: I'm not expert on SQL, beginner. Tried this post but its kind of confusing for me. (Best way to do nested case statement logic in SQL Server)

Thank you

'today' is not a recognized built-in function name. per MATT BUSCHE code which i found simple. — Eddy V, Mar 11 '13 at 16:27
There is no such a function on sql-server. You could use `getdate()`. — Kaf, Mar 11 '13 at 16:28

Matt Busche · Accepted Answer · 2013-03-11T16:33:34.617

1

You can use AND in a CASE statement. You don't need to perform a true nested CASE statement in this scenario

(CASE WHEN [Control_ClosedDateRev] IS NULL AND DateDiff(day,opendate,getDate()) > 4 THEN '1' ELSE '0' END)

edited Mar 11 '13 at 16:33

answered Mar 11 '13 at 16:00

Matt Busche

13,789
5
34
58

i get an "validation error", 'today' is not a recognized built-in function name. – Eddy V Mar 11 '13 at 16:26

SQL Computed Column Case with Multiple Criteria

1 Answers1