时间戳

首页 > 解决方案 > 用两个条件返回值的案例表达式

sql - 用两个条件返回值的案例表达式

问题描述

当 AAATLCHANGEVALUE 等于“手动速率”时，我需要返回初始 AAALTLCHANGEEASON 值。所以在这个例子中，我需要返回“需要的类”。

AAALTLCHANGEVALUE   AAALTLCHANGEREASON         CREATEDDATETIME     
ManualCoded              New Customer           09/17/2019          
Manual Rate              Class needed           09/18/2019
CWI Inspection           Reweigh                09/19/2019
Manual Rate              Insurance Added        09/20/2019

我试过这个case表达式没有成功：

case

when  [AAALTLCHANGEVALUE] = 'manual rate' then[AAALTLCHANGEREASON]
when  [AAALTLCHANGEVALUE] = 'manual rate' then MIN[CREATEDDATETIME]

else null

end

谢谢你的帮助。

标签： sqlsql-serverdata-warehouse

解决方案

首先，您选择全部 'Manual Rate'，然后确保没有其他人早于该行。

SQL 演示

 SELECT AAALTLCHANGEREASON
 FROM YourTable t1
 WHERE AAALTLCHANGEVALUE = 'Manual Rate'
   AND NOT EXISTS (SELECT 1
                   FROM YourTable t2
                   WHERE t2.CREATEDDATETIME < t1.CREATEDDATETIME
                     AND AAALTLCHANGEVALUE = 'Manual Rate')

推荐阅读