如何按以下要求从Server检索数据？

Question

我有一张这样的桌子：

  CustName    Country    RecordedTime
 ---------------------------------------------
  Alex        Australia  2018-Jun-01 08:00 AM
  Alex        China      2018-Jun-01 10:00 AM
  Alex        Japan      2018-Jun-01 11:00 AM
  John        Australia  2018-Jun-01 08:00 AM
  John        China      2018-Jun-02 08:00 AM
  Bob         Australia  2018-Jun-02 09:00 AM
  Bob         Brazil     2018-Jun-03 09:50 AM

如果记录在系统中是全新的，那么它应该在审计和历史字段(结果集中的两个额外字段)中显示给定日期的'ADD‘& ' new’。

如果记录当天被编辑了两次，那么它应该为给定的日期显示两个条目，分别在审核字段中添加‘& 'CHANGE’和‘在History状态字段中的’& 'CURRENT‘。

例如，这就是我的结果应该出现的方式；

当我将输入日期改为2018-6月-01时，输出应该如下：

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
   Alex        Australia  2018-Jun-01 08:00 AM   ADD      NEW
   Alex        China      2018-Jun-01 10:00 AM   CHANGE   BEFORE
   Alex        Japan      2018-Jun-01 11:00 AM   CHANGE   CURRENT
   John        Australia  2018-Jun-01 08:00 AM   ADD      NEW

当我将输入日期传递为2018-6月-02时，输出应如下所示：

   CustName    Country    RecordedTime           Audit    History
  -----------------------------------------------------------------
   John        China      2018-Jun-02 08:00 AM   CHANGE   CURRENT
   Bob         Australia  2018-Jun-02 09:00 AM   ADD      NEW

当我将输入日期改为2018-6月-02时，输出应如下所示：

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
   Bob         Brazil     2018-Jun-03 09:50 AM   CHANGE   CURRENT

我尝试了许多方法，但我仍然缺少一些方案来实现这一点。谁能帮我弄清楚这件事吗？

Answer 1

我只需使用case表达式。

select t.*,
       (case when seqnum = 1 then 'ADD' else 'CHANGE' end) as audit,
       (case when seqnum = 1 then 'NEW'
             when seqnum_day = 1 then 'CURRENT'
             else 'BEFORE'
        end) as history
from (select t.*,
             row_number() over (partition by custname order by recordedtime) as seqnum,
             row_number() over (partition by custname, cast(recordedtime as date) order by recordedtime desc) as seqnum_day
      from t
     ) t;

方形小提琴：http://sqlfiddle.com/#!18/43c08/27

Answer 2

你可以试试。

具有Windows功能的CASE WHEN和RANK

;WITH CTE (CustName,Country,RecordedTime,rn) AS(
  SELECT *,RANK() OVER(PARTITION BY CustName ORDER BY RecordedTime) rn
  FROM T
)
SELECT t.*,
       (CASE WHEN rn = 1 then 'ADD' ELSE 'CHANGE' END) 'Audit',
       (CASE 
          WHEN rn = 1  then 'NEW'
          WHEN t2.mRn = rn then 'CURRENT' 
       ELSE 'BEFORE' END) 'History'
FROM CTE t LEFT JOIN  (
  SELECT MAX(rn) mRn,CustName  FROM CTE GROUP BY CustName
) t2 on t2.mRn = t.rn and t2.CustName = t.CustName
WHERE CONVERT(char(10), RecordedTime,126) = '2018-06-02'

方形小提琴：http://sqlfiddle.com/#!18/43c08/26