How do I achieve this output in SQL server

See more:

SQL

SQL-Server

SQL-Server-2008R2

, +

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;

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)

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"

Quote:

Quote:

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

Quote:

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

Quote:

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

What I have tried:

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</blockquote>