CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012ssms
Mar1009
Mar1009

Reputation: 811

How to retrieve data from SQL Server based on below example?

Actually I posted a similar question related to this in here How to retrieve data from SQL Server as required below? and now I needed some changes in that so please see this before suggesting

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

If new record is created and this record is changed several times in same day, then output to be the first and the last of changed records and if an existing record is changed several times the same day then output should be before of first change and current change

for example: If give 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        Japan      2018-Jun-01 11:00 AM   CHANGE   CURRENT
   John        Australia  2018-Jun-01 08:00 AM   ADD      NEW

If give input date as 2018-Jun-02 then the output should be as below;

   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

If give input date as 2018-Jun-03 then the output should be as below;

   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

Any help is much appreciated.

Upvotes: 1

Views: 84

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

Maybe you can use a solution like below See working demo

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

Upvotes: 1

Related Questions