如何根据以下示例从SQL Server检索数据？

小编典典

如何根据以下示例从SQL Server检索数据？

sql

我有这样一张table：

 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
  Bob         Africa     2018-Jun-03 11:50 AM
  Bob         India      2018-Jun-03 12:55 AM

如果创建了新记录，并且该记录在同一天被更改了几次，则输出将成为更改后的记录的第一个和最后一个记录；如果同一天对现有记录进行了几次更改，则输出应在第一个更改和当前更改之前改变

例如：如果输入日期为2018年6月1日，则输出应如下所示；

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

如果输入日期为2018年6月2日，则输出应如下所示;

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

如果输入日期为2018年6月3日，则输出应为以下内容;

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
   Bob         Australia  2018-Jun-02 09:00 AM   CHANGE   BEFORE
   Bob         India      2018-Jun-03 12:55 AM   CHANGE   CURRENT

任何帮助深表感谢。

阅读 259

2021-04-07

共1个答案

小编典典

也许您可以使用以下解决方案 **[See working demo](http://rextester.com/KPPS98807)**

declare @d date='2018-Jun-03'

; with Indexer as 
(
    select 
        *, 
        rn= row_number() over(partition by CustName order by RecordedTime),
        rn2=row_number() over(partition by CustName order by RecordedTime desc)
    from records
)
,GetValidCustomerRecords as
(
    select 
        CustName,
        Country,
        RecordedTime,
        Audit   = case when cast(RecordedTime as date)=@d and rn=1 then 'add' else 'change' end,
        History = case 
                    when cast(RecordedTime as date)=@d and rn=1 
                    then 'new' 
                    when cast(RecordedTime as date)<@d and rn=1 
                    then 'before'
                    else 'current' end
    from Indexer i 
    where CustName in
    (
    select 
        distinct CustName 
    from records
    where cast(RecordedTime as date)=@d
    ) 
    and (rn=1 or rn2=1) and cast(RecordedTime as date)<=@d
)

select * from GetValidCustomerRecords
order by CustName, RecordedTime

2021-04-07