#StackBounty: #sql #sql-server #sql-server-2008 #tsql #sql-server-2012 How to query this output in SQL server

Bounty: 50

I have a table with data like this:

CREATE TABLE Test
    (CustName varchar(20), Country varchar(20), RecordedTime datetime, CurrNo tinyint);

INSERT INTO Test
    (CustName, Country, RecordedTime, CurrNo)
VALUES
    ('Alex', 'Australia', '2018-06-01 08:00:00', 1),
    ('Alex', 'China', '2018-06-01 10:00:00', 2),
    ('Alex', 'India', '2018-06-01 10:05:00', 3),
    ('Alex', 'Japan', '2018-06-01 11:00:00', 4),
    ('John', 'Australia', '2018-06-01 08:00:00', 1),
    ('John', 'China', '2018-06-02 08:00:00', 2),
    ('Bob', 'Australia', '2018-06-02 09:00:00', 1),
    ('Bob', 'Brazil', '2018-06-03 09:50:00', 2),
    ('Bob', 'Africa', '2018-06-03 11:50:00', 3),
    ('Bob', 'India', '2018-06-03 11:55:00', 4),
    ('Tim', 'Brazil', '2018-06-10 00:00:00', 2),
    ('Tim', 'Cuba', '2018-06-11 00:00:00', 3),
    ('Tim', 'India', '2018-06-11 00:05:00', 4),
    ('Jerry', 'Cuba', '2018-06-12 00:00:00', 4),
    ('Jerry', 'Brazil', '2018-06-12 00:05:00', 5),
    ('Jerry', 'India', '2018-06-12 00:10:00', 7),
    ('Jerry', 'USA', '2018-06-12 00:15:00', 9)

    ('Maulik', 'Aus', '2018-06-12 00:00:00',3),
    ('Maulik', 'Eng', '2018-06-13 00:00:00',4),
    ('Maulik', 'USA', '2018-06-14 00:00:00',5),
    ('Maulik', 'Ind', '2018-06-14 00:00:00',6);

Table Result :

 CustName    Country    RecordedTime           CurrNo
 -----------------------------------------------------
  Alex        Australia  2018-Jun-01 08:00 AM    1
  Alex        China      2018-Jun-01 10:00 AM    2
  Alex        India      2018-Jun-01 10:05 AM    3
  Alex        Japan      2018-Jun-01 11:00 AM    4
  John        Australia  2018-Jun-01 08:00 AM    1
  John        China      2018-Jun-02 08:00 AM    2
  Bob         Australia  2018-Jun-02 09:00 AM    1
  Bob         Brazil     2018-Jun-03 09:50 AM    2
  Bob         Africa     2018-Jun-03 11:50 AM    3
  Bob         India      2018-Jun-03 11:55 AM    4
  Tim         Brazil     2018-Jun-10 12:00 AM    2
  Tim         Cuba       2018-Jun-11 12:00 AM    3
  Tim         India      2018-Jun-11 12:05 AM    4
  Jerry       Cuba       2018-Jun-12 12:00 AM    4
  Jerry       Brazil     2018-Jun-12 12:05 AM    5
  Jerry       India      2018-Jun-12 12:10 AM    7
  Jerry       USA        2018-Jun-12 12:15 AM    9
  Maulik      Aus        2018-Jun-12 00:00:AM    3
  Maulik      Eng        2018-Jun-13 00:00:AM    4
  Maulik      USA        2018-Jun-14 00:00:AM    5
  Maulik      Ind        2018-Jun-14 00:00:AM    6

I need the output which should cover all the below scenarios.

There is a thumb rule for how values should be present for “Audit” and “History” fields;

  1. Records should have Audit = “ADD”or “CHANGE” & History = “NEW”,”BEFORE” or “CURRENT” only for Original Accts (which means entries in table definitely starts from CurrNo = 1)

  2. Records should NOT have Audit = “ADD” & History = “NEW”
    for Migrated Accts (which means entries in table doesn’t starts from CurrNo = 1, it may starts from 2 or 3 or any ascending numbers ) for this type of accounts the Audit should have “CHANGE” and History fields should have “BEFORE”or “CURRENT”

Scenario1:
If given input date as 2018-Jun-01 then the output should be as below (i.e. When a record is added and edited multiple times in a same day)

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

Scenario2:
If given input date as 2018-Jun-02 then the output should be as below (i.e. When a record is already present in previous days and same record is edited today and any new record present today)

   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

Scenario3:
If given input date as 2018-Jun-03 then the output should be as below (i.e. When a recorded is edited multiple times the same day then it should list the last record for latest previous date and then the last record for current given date)

   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

Scenario4:
If given input date as 2018-Jun-10 then output should be as below

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
   Tim         Brazil     2018-Jun-10 12:00 AM    CHANGE   CURRENT

Scenario5:
If given input date as 2018-Jun-11 then output should be as below (i.e. similar to Scenario 2)

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
   Tim         Brazil     2018-Jun-10 12:00 AM    CHANGE   BEFORE
   Tim         India      2018-Jun-11 12:05 AM    CHANGE   CURRENT

Scenario6:
If given input date as 2018-Jun-12 then output should be as below (i.e. similar to Scenario 3)

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
    Jerry       Cuba       2018-Jun-12 12:00 AM    CHANGE   BEFORE
    Jerry       USA        2018-Jun-12 12:15 AM    CHANGE   CURRENT
    Maulik      Aus        2018-Jun-12 00:00 AM    CHANGE   CURRENT

If given input date as 2018-Jun-13 then output should be as below

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
    Maulik      Aus        2018-Jun-12 00:00 AM    CHANGE   BEFORE
    Maulik      Eng        2018-Jun-13 00:00 AM    CHANGE   CURRENT

If given input date as 2018-Jun-14 then output should be as below

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
    Maulik      Eng        2018-Jun-13 00:00 AM    CHANGE   BEFORE
    Maulik      Ind        2018-Jun-14 00:00 AM    CHANGE   CURRENT

And below is current code I’m using (Which satisfies Scenario 2 and 3, but not satisfies rest of them);

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

Any SQL experts can modify this query to satisfy all the scenarios? Much appreciated and thanks.

Note: There is similar question in here just for reference – How to retrieve data from SQL Server based on below example?


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.