How to retrieve data from SQL Server as required below?

Question

I have a table like this :

  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

If the record is brand new in the system then it should show 'ADD' & 'NEW' in Audit and history fields (two additional fields in result set) for the given date.

If the record got edited twice that day then it should show two entries with 'ADD' & 'CHANGE ' in Audit fields and 'BEFORE' & 'CURRENT' in History state fields respectively for the given date.

For example this is how my result should appear;

When I pass input date as 2018-Jun-01 then the output should be as below:

   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

When I pass the input date as 2018-Jun-02 then the output should be as below:

   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

When I pass input date as 2018-Jun-02 then the output should be as below:

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

I tried many ways but still I'm missing some scenarios to achieve this. Can someone please shed some light on this?

Answer 1

One way to do it is via a cte like below where we have row_number() function to track the sequence both ways.

See live demo

; with cte 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
    )
, cte2 as
(
select *, audit='New', History='Change' from cte where rn=1
    union 
select *, audit='Change', History='Current' from cte where rn2=1 and rn<>1
    union
select *, audit='Change', History='before' from cte where rn>1 and rn2<>1
)

select 
    CustName,
    Country,
    RecordedTime,
    audit,
    History
from cte2
order by  CustName,RecordedTime