question

maru1009 avatar image
maru1009 asked

How to achieve this in Tsql?

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);

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.


sql-server-2008sqlsql-serversql server 2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.