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

带有datedfif和between的Case语句

  •  -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' 
    
    推荐文章