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;
- 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"
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.
