Devesh Sharma Devesh Sharma - 14 days ago 5
SQL Question

using a case expression inside select statement is fetching me wrong output. The output is a link attached

case
when SCHEDULES.START_DTTM - PATIENTS.DTTM_OF_BIRTH < 16 then '" 1.C"'
when SCHEDULES.START_DTTM - PATIENTS.DTTM_OF_BIRTH >= 16 and SCHEDULES.START_DTTM - PATIENTS.DTTM_OF_BIRTH < 65 then '"2.A"'
ELSE '"3.OP"'
END AS AGEGROUP


enter image description here

Answer

You are misunderstanding how SQL Server deals with date arithmetic. Taking a single line:

SCHEDULES.START_DTTM - PATIENTS.DTTM_OF_BIRTH < 16 

Since this expression uses multiple data types

(DATETIME) - (DATETIME) < (INT)

the first step is to convert them all into the same one. Since DATETIME has a higher precedence than INT it is 16 that must be converted to a datetime:

SELECT  CONVERT(DATETIME, 16)

Gives:

1900-01-17 00:00:00.000

Now, if we simply use the result of your first row:

SELECT  CONVERT(DATETIME, '20150408 11:05') - CONVERT(DATETIME, '20150319');

We get a result of

1900-01-21 11:05:00.000

Which is > 1900-01-17, so the first expression in the case statement is false. For the next bit again, some conversion needs to be to convert 65 to a datetime (1900-03-07), since this is after 1900-01-21 11:05:00.000 the second expression evaluates to true, so "2.A" is returned.

Since you want to compare years, I think your case expression should be:

SELECT  START_DTTM,
        DTTM_OF_BIRTH,
        CASE WHEN DATEADD(YEAR, 16, DTTM_OF_BIRTH) > START_DTTM THEN '" 1.C"'  
            WHEN DATEADD(YEAR, 65, DTTM_OF_BIRTH) > START_DTTM THEN '"2.A"' 
            ELSE '"3.OP"'
        END AS AGEGROUP
FROM (VALUES
        (CONVERT(DATETIME, '20150408 11:05'), CONVERT(DATETIME, '20150319')),
        (CONVERT(DATETIME, '20150505 10:35'), CONVERT(DATETIME, '20150319')),
        (CONVERT(DATETIME, '20150323 11:47'), CONVERT(DATETIME, '20150319')),
        (CONVERT(DATETIME, '20150325 14:35'), CONVERT(DATETIME, '20150319')),
        (CONVERT(DATETIME, '20150320 18:00'), CONVERT(DATETIME, '20150319')),
        (CONVERT(DATETIME, '20150226 18:00'), CONVERT(DATETIME, '19760812')),
        (CONVERT(DATETIME, '20150305 07:47'), CONVERT(DATETIME, '19760812')),
        (CONVERT(DATETIME, '20150227 16:00'), CONVERT(DATETIME, '19760812'))
    ) x (START_DTTM , DTTM_OF_BIRTH);

Which gives:

START_DTTM              DTTM_OF_BIRTH           AGEGROUP
---------------------------------------------------------
2015-04-08 11:05        2015-03-19 00:00        " 1.C"
2015-05-05 10:35        2015-03-19 00:00        " 1.C"
2015-03-23 11:47        2015-03-19 00:00        " 1.C"
2015-03-25 14:35        2015-03-19 00:00        " 1.C"
2015-03-20 18:00        2015-03-19 00:00        " 1.C"
2015-02-26 18:00        1976-08-12 00:00        "2.A"
2015-03-05 07:47        1976-08-12 00:00        "2.A"
2015-02-27 16:00        1976-08-12 00:00        "2.A"