Case statement with datedfif and between

Question

I am using Sybase IQ, and have the following SQL code which doesn't seem to work. The issue is with the case statement.. Thanks in advance

SELECT  a.cusid, start_date, effective_dt, 
case when DATEDIFF(DAY, start_date, effective_dt) >= 5476 THEN 'Green'
case when DATEDIFF(DAY, start_date, effective_dt) between 2921 AND 4575 THEN 'Red'
case when DATEDIFF(DAY, start_date, effective_dt) BETWEEN 1096  AND 2920 THEN 'Blue'
case when DATEDIFF(DAY, start_date, effective_dt) BETWEEN 0 AND 1095 THEN 'Rose'
ELSE NULL END as tier
FROM   tablea a
INNER JOIN tableb b
    ON a.cusid = b.cusid
WHERE   b.active = 'Yes'

"doesn't seem to work" is not a problem description. Do you get errors? What are the errors? Do you get unexpected results? What do you expect, and what are the actual results? — HoneyBadger, Nov 28 '17 at 10:44

score 2 · Accepted Answer · answered Nov 28 '17 at 10:45

2

No need to have case keyword each time with when clause. Try this :

SELECT  a.cusid, start_date, effective_dt, 
case when DATEDIFF(DAY, start_date, effective_dt) >= 5476 THEN 'Green'
when DATEDIFF(DAY, start_date, effective_dt) between 2921 AND 4575 THEN 'Red'
when DATEDIFF(DAY, start_date, effective_dt) BETWEEN 1096  AND 2920 THEN 'Blue'
when DATEDIFF(DAY, start_date, effective_dt) BETWEEN 0 AND 1095 THEN 'Rose'
ELSE NULL END as tier
FROM   tablea a
INNER JOIN tableb b
    ON a.cusid = b.cusid
WHERE   b.active = 'Yes'

answered Nov 28 '17 at 10:45

Nikhil Shetkar

346
1
9

Thanks for your help – Nick Edwards Nov 28 '17 at 12:25
Pleasure is mine. :) – Nikhil Shetkar Nov 28 '17 at 12:26

score 1 · Answer 2 · answered Nov 28 '17 at 10:45

You have the syntax slightly off. You don't need to the case for each condition:

SELECT  a.cusid, start_date, effective_dt, 
CASE when DATEDIFF(DAY, start_date, effective_dt) >= 5476 THEN 'Green'
     when DATEDIFF(DAY, start_date, effective_dt) between 2921 AND 4575 THEN 'Red'
     when DATEDIFF(DAY, start_date, effective_dt) BETWEEN 1096  AND 2920 THEN 'Blue'
     when DATEDIFF(DAY, start_date, effective_dt) BETWEEN 0 AND 1095 THEN 'Rose'
  ELSE NULL END as tier
FROM   tablea a
INNER JOIN tableb b
    ON a.cusid = b.cusid
WHERE   b.active = 'Yes'

Case statement with datedfif and between

2 Answers2