CODEWITHSUNDEEP
mysqlsql
Sue Hall
Sue Hall

Reputation: 3

sql query case dateadd

Can I use GetDate and DateAdd in a SQL Query Select Case statement? Part of my query is as follows but it only returns OVER60 as the result regardless of client's Date of Birth. Is there a better way to accomplish this? I'm a novice at this and it's my first question to Stack Overflow so I thank you for any insight.

SELECT  G.ClientID, CONVERT(VARCHAR(10), ClientDob, 101) AS ClientDob
, CASE 
        WHEN (G.ClientDob > GETDATE() AND G.ClientDob < dateadd(year, -13, GetDate()))THEN 'Under13' 
        WHEN (G.ClientDob >= DATEADD(YEAR, -13, GETDATE()) AND G.ClientDob < DATEADD(year, -20, GETDATE()))THEN '13-19'
        WHEN (G.ClientDob >= DATEADD(Year, -20, GETDATE()) AND G.ClientDob < DATEADD(Year, -30, GETDATE())) THEN '20-29'
        WHEN (G.ClientDob >= DATEADD(YEAR, -30, GETDATE()) AND G.ClientDob < DATEADD(YEAR, -45, GETDATE())) THEN '30-44'
        WHEN (G.ClientDob >= DATEADD(Year, -45, GETDATE()) AND G.ClientDob < DATEADD(YEAR, -60, GETDATE())) THEN '45-59'
        Else 'OVER60' 
END AS AgeCategory

Upvotes: 0

Views: 777

Answers (1)

Hart CO
Hart CO

Reputation: 34784

Assuming SQL Server despite MySQL tag This should do it:

SELECT  G.ClientID, CONVERT(VARCHAR(10), ClientDob, 101) AS ClientDob
, CASE 
    WHEN (G.ClientDob > dateadd(year, -13, GetDate()))THEN 'Under13' 
    WHEN (G.ClientDob > DATEADD(year, -20, GETDATE()))THEN '13-19'
    WHEN (G.ClientDob > DATEADD(Year, -30, GETDATE())) THEN '20-29'
    WHEN (G.ClientDob > DATEADD(YEAR, -45, GETDATE())) THEN '30-44'
    WHEN (G.ClientDob > DATEADD(YEAR, -60, GETDATE())) THEN '45-59'
    Else 'OVER60' 
END AS AgeCategory

You had < and > confusion, also there's no need for negating prior criteria in subsequent WHEN criteria, the first matching criteria gets used and none others are evaluated.

Note: As xQbert noted, the above assumes no future birthdates exist, if future birthdays can exist in your data (they shouldn't be allowed imo), you would want to handle those explicitly.

Upvotes: 1

Related Questions