Oskarsforest Oskarsforest - 2 months ago 11
SQL Question

How to declare a queried value and use it in CASE?

I want to convert the DOB into the number age, then assign the age value into different age groups on SQL Server.

I have found the way to do the first step, and the second step ideally I should set the first step's result as a variable and let it compare in the CASE statement. However, I don't know declare and set the variable and use it in CASE.

I got the table I want with a silly way as below:

SELECT FirstName, LastName,
CAST(
CASE
WHEN FLOOR((CAST (GetDate() AS INTEGER) - CAST(dob AS INTEGER)) / 365.25) < 18
THEN 'Under 18'
WHEN FLOOR((CAST (GetDate() AS INTEGER) - CAST(dob AS INTEGER)) / 365.25) BETWEEN 19 AND 30
THEN '19-30'
WHEN FLOOR((CAST (GetDate() AS INTEGER) - CAST(dob AS INTEGER)) / 365.25) > 30
THEN 'Over 30'
ELSE 'ERROR'
END AS VARCHAR(10)) AS Age
FROM Student


MY QUESTION: Is there a way to set "FLOOR((CAST (GetDate() AS INTEGER) - CAST(DOB AS INTEGER)) / 365.25)" as a variable and use it in the CASE statement?

*FirstName, LastName and dbo are columns in Student Table.

Thank you!

Answer

No, not in the case statement. The two traditional recommendations are to use CTEs or subqueries. But, SQL Server also supports apply:

SELECT FirstName, LastName,
       (CASE WHEN v.age <= 18 THEN 'Under 18'
             WHEN v.age <= 30 THEN '19-30'
             WHEN v.age > 30 THEN 'Over 30'
             ELSE 'ERROR'
        END) AS Age
FROM Student s OUTER APPLY
     (VALUES (FLOOR((CAST (GetDate() AS INTEGER) - CAST(dob AS INTEGER)) / 365.25) )
     ) as v(age);

Comments:

  • You don't need to cast strings to varchar(10). That is just superfluous.
  • The case is evaluated in order, so you don't need between. This makes it much easier if you want to change the boundaries.