代码之家 › 专栏 › 技术社区 › Nick Edwards

带有datedfif和between的Case语句

sap-iq datediff sybase case sql

-2

Nick Edwards · 技术社区 · 8 年前

我使用的是Sybase IQ,下面的SQL代码似乎不起作用。问题在于案例陈述。。提前感谢

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'

2 回复 | 直到 8 年前

1

2

Nikhil Shetkar 8 年前

不需要每次都有case关键字和when子句。试试这个:

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'

2

1

cf_en 8 年前

你的语法有点错误。你不需要 case 对于每种情况:

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'