Reputation: 811
How to query this output in SQL server
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;
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.
Note: There is similar question in here just for reference - How to retrieve data from SQL Server based on below example?
Upvotes: 2
Views: 361
Answers (4)
Reputation: 2434
Good day,
Please check if the below solution solves all your needs. I tested it with your data and with some more rows, but it is always best to re-check. In first glance it seems like it returns the requested result. I will add some explanation later on
The query I am using it this:
DECLARE @Date DATE = '2018-06-12';
with MyCTE as (
SELECT
t.CustName,t.Country,t.RecordedTime,t.CurrNo, D = CONVERT(DATE, RecordedTime)
,RN_D = ROW_NUMBER()
OVER (partition by t.CustName order by t.CurrNo desc)
,RN = ROW_NUMBER()
OVER (partition by t.CustName order by t.CurrNo)
,RN_Old = ROW_NUMBER()
OVER (partition by t.CustName, (CASE WHEN CONVERT(DATE, RecordedTime) < @Date then 0 else 1 END) order by t.CurrNo desc)
,Cnt = COUNT(*)
OVER (partition by t.CustName)
,CntToday = COUNT(CASE WHEN CONVERT(DATE, RecordedTime) = @Date THEN 1 ELSE NULL END)
OVER (partition by t.CustName)
FROM Test t
where
-- returns rows untill current date
CONVERT (DATE, RecordedTime) <= @Date
-- only if relevnat to current date
and EXISTS (
SELECT * FROM test t0
where CONVERT (DATE, RecordedTime) = @Date and t0.CustName = t.CustName
)
)
,MyCTE2 as (
select
CustName, Country, RecordedTime, D, CurrNo, RN_D, RN, Cnt, t2.c, History, CntToday, RN_Old
from MyCTE t1
left JOIN (select * from (values(1, 'NEW'),(1, 'BEFORE')) t2(c, History) ) t2
on t1.CurrNo = t2.c
and CntToday > 1
and D = @Date
where
RN_D = 1
or (RN = 1 and D = @Date)
or (RN_Old = 1 and D < @Date)
)
,MyCTE3 as (
select CustName, Country, RecordedTime
-- unmarke the bellow comment in order to get the accessories columns I used
-- This is recommended to understand the line-of-thinking
--, D, c, RN_D, RN, CurrNo, Cnt, CntToday, RN_Old
, History = CASE
WHEN CurrNo = 1 and Cnt = 1 then 'NEW'
WHEN RN_D = 1 then 'CURRENT'
else ISNULL(History,'BEFORE')
END
from MyCTE2
)
select CustName, Country, RecordedTime--, D, c, RN_D, RN, CurrNo, Cnt, CntToday, RN_Old
,Audit = CASE when History='New' then 'ADD' else 'CHANGE' END
, History
from MyCTE3
To make it simpler to test I insert the entire query into table function
DROP FUNCTION IF EXISTS dbo.F
GO
CREATE FUNCTION dbo.F(@Date DATE)
RETURNS TABLE AS RETURN (
--DECLARE @Date DATE = '2018-06-12';
with MyCTE as (
SELECT
t.CustName,t.Country,t.RecordedTime,t.CurrNo, D = CONVERT(DATE, RecordedTime)
,RN_D = ROW_NUMBER()
OVER (partition by t.CustName order by t.CurrNo desc)
,RN = ROW_NUMBER()
OVER (partition by t.CustName order by t.CurrNo)
,RN_Old = ROW_NUMBER()
OVER (partition by t.CustName, (CASE WHEN CONVERT(DATE, RecordedTime) < @Date then 0 else 1 END) order by t.CurrNo desc)
,Cnt = COUNT(*)
OVER (partition by t.CustName)
,CntToday = COUNT(CASE WHEN CONVERT(DATE, RecordedTime) = @Date THEN 1 ELSE NULL END)
OVER (partition by t.CustName)
FROM Test t
where
-- returns rows untill current date
CONVERT (DATE, RecordedTime) <= @Date
-- only if relevnat to current date
and EXISTS (
SELECT * FROM test t0
where CONVERT (DATE, RecordedTime) = @Date and t0.CustName = t.CustName
)
)
,MyCTE2 as (
select
CustName, Country, RecordedTime, D, CurrNo, RN_D, RN, Cnt, t2.c, History, CntToday, RN_Old
from MyCTE t1
left JOIN (select * from (values(1, 'NEW'),(1, 'BEFORE')) t2(c, History) ) t2
on t1.CurrNo = t2.c
and CntToday > 1
and D = @Date
where
RN_D = 1
or (RN = 1 and D = @Date)
or (RN_Old = 1 and D < @Date)
)
,MyCTE3 as (
select CustName, Country, RecordedTime
-- unmarke the bellow comment in order to get the accessories columns I used
-- This is recommended to understand the line-of-thinking
--, D, c, RN_D, RN, CurrNo, Cnt, CntToday, RN_Old
, History = CASE
WHEN CurrNo = 1 and Cnt = 1 then 'NEW'
WHEN RN_D = 1 then 'CURRENT'
else ISNULL(History,'BEFORE')
END
from MyCTE2
)
select CustName, Country, RecordedTime--, D, c, RN_D, RN, CurrNo, Cnt, CntToday, RN_Old
,Audit = CASE when History='New' then 'ADD' else 'CHANGE' END
, History
from MyCTE3
--order by CustName, RecordedTime
)
GO
Using the function it simpler to make multiple test, but probably in the production you will want to use the direct query
-- Test
select * from F('2018-06-01') order by CustName , RecordedTime
select * from F('2018-06-02') order by CustName , RecordedTime
select * from F('2018-06-03') order by CustName , RecordedTime
select * from F('2018-06-10') order by CustName , RecordedTime
select * from F('2018-06-11') order by CustName , RecordedTime
select * from F('2018-06-12') order by CustName , RecordedTime
select * from F('2018-06-13') order by CustName , RecordedTime
select * from F('2018-06-14') order by CustName , RecordedTime
/**************** Update at 2018-08-19 14:05 Israel Time ****************/
I notice that some more information is important to add for the sake of the participate in the thread. I hope this will be useful
NOTE! tested on Microsoft SQL Server 2017 Developer Edition
First, let's compare percentage of resources uses according to the Execution Plans of three queries: (1) My solution, (2) maulik kansara seconds solution after update the first solution, and (3) maulik kansara first solution
Now lets check the image of the EP of maulik kansara seconds solution:
This query scans the table 11 times!
** Important! EP is not the only parameter that suggestes which query we should choose, but this is probably the first information we should check. In addition we should check IO statistics and Time statistics, and more...
Credit: The image was taken using sentryone tool. There is a free version which can gives most of what DBAs need. I am using the full version which I got for free as Microsoft MVP, so Thanks ;-)
Upvotes: 1
Reputation: 48139
You have a bunch of if/and/buts going on in your original request, but maybe having an ALTERNATE VIEW of the data might be a better solution that you just did not think of or have been offered, so I will.
Why have individual lines showing both the before and after, why not do on a single returned line. If there is no change, why show? Maybe that is a later question.
To get my solution, I have a simple WITH from the test data that I am using as a baseline for my query. You have your own CurrNo which may not always start with 1, and could even possibly skip a sequence number by some accident. So my WITH declaration gets a sequential row number by customer and ordered by the CurrNo. This will ALWAYS return per customer rows 1, 2, 3, 4 even if the CurrNo values may be 5, 8, 9, 11 (exaggerated starting number and accidental skipped values). You could even change the order by to the RecordedTime to ensure logging based on the DATE per customer activity.
;with baseData as
(
select
T.*,
ROW_NUMBER() OVER (PARTITION BY CustName ORDER BY CurrNo) AS CustOrder
from
Test T
)
So now, with the query. I have my first table which is the basis for finding any "changes" within the system. You will always start with these records. Apply a date restrictive filter as you need. The LEFT-JOIN will always be on the customer name PLUS whatever the row number is PLUS ONE joined to the NEXT of the same baseline data. So, if just looking at customer Alex and Jerry from your data, you would have the following
Customer Country Time CurrNo CustOrder
Alex Australia 2018-06-01 08:00:00 1 1
Alex China 2018-06-01 10:00:00 2 2
Alex India 2018-06-01 10:05:00 3 3
Alex Japan 2018-06-01 11:00:00 4 4
Jerry Cuba 2018-06-12 00:00:00 4 1
Jerry Brazil 2018-06-12 00:05:00 5 2
Jerry India 2018-06-12 00:10:00 7 3
Jerry USA 2018-06-12 00:15:00 9 4
So you can see the natural row number normalization between each. So Now, my left-join is by customer AND the CustOrder column which will not skip gaps, so I will get records like
Customer CustOrder NextCustOrder CurrNo NextCurrNo
Alex 1 2 1 1
Alex 2 3 2 2
Alex 3 4 3 3
Alex 4 (no 5th record) 4 4
Jerry 1 2 4 5
Jerry 2 3 5 7
Jerry 3 4 7 9
Jerry 4 (no 5th record) 9 (no next record)
Finally, I am getting the data from the first record which will always exist and IF A CORRESPONDING NEXT record exists, it will show that new changed TO value. That next record will be the next row and it's possible changed TO value and so on..
select
bd.CustName,
case when bd.CurrNo = 1 then 'ADD' else 'CHANGE' end as Audit,
bd.Country as CurrentValue,
bd.RecordedTime,
bdNext.Country as ChangedValue,
bdNext.RecordedTime ChangedTime,
bd.CurrNo,
bd.CustOrder
from
baseData bd
LEFT JOIN baseData bdNext
on bd.CustName = bdNext.CustName
AND bd.CustOrder +1 = bdNext.CustOrder;
CustName Audit CurrentValue RecordedTime ChangedValue ChangedTime CurrNo CustOrder
Alex ADD Australia 2018-06-01 08:00:00.000 China 2018-06-01 10:00:00.000 1 1
Alex CHANGE China 2018-06-01 10:00:00.000 India 2018-06-01 10:05:00.000 2 2
Alex CHANGE India 2018-06-01 10:05:00.000 Japan 2018-06-01 11:00:00.000 3 3
Alex CHANGE Japan 2018-06-01 11:00:00.000 NULL NULL 4 4
Jerry CHANGE Cuba 2018-06-12 00:00:00.000 Brazil 2018-06-12 00:05:00.000 4 1
Jerry CHANGE Brazil 2018-06-12 00:05:00.000 India 2018-06-12 00:10:00.000 5 2
Jerry CHANGE India 2018-06-12 00:10:00.000 USA 2018-06-12 00:15:00.000 7 3
Jerry CHANGE USA 2018-06-12 00:15:00.000 NULL NULL 9 4
If you don't want the "last value" for a record because no changes after, just change the LEFT JOIN to INNER JOIN to guarantee something changed after. But that would fail if you wanted to see all new "ADD" records without changes. You could apply that as a where clause something like
where
bd.CurrNo = 1
OR bdNext.CustOrder IS NOT NULL
Additionally you could add a date filter such as
where
bd.RecordedTime >= '2018-06-10'
AND ( bd.CurrNo = 1
OR bdNext.CustOrder IS NOT NULL )
As suggested in comment by Ronen Ariely, the above WHERE clause with the date would be applied to the WITH baseData as component, adding a
where
T.RecordedTime >= '2018-06-10'
to pre-filter the data before it gets to the rest of the join activity
Upvotes: 1
Reputation: 29647
From what I understand from the different scenario's, they can be achieved by basically only 2 rules.
- Rule 1: The records for a CustName with the RecordedTime all on the same day.
- Rule 2: The records for a CustName with the RecordedTime over more than one day.
With a few window functions and an OR in the outer query, we can have two rules to target all scenarios.
And then use CASE WHEN to calculate the "Audit" and the "History".
SELECT
CustName, Country,
FORMAT(RecordedTime, 'yyyy-MMM-dd hh:mm tt') as RecordedTime,
(CASE CurrNo
WHEN 1 then 'ADD'
ELSE 'CHANGE'
END) as [Audit],
(CASE
WHEN CurrNo = 1 then 'NEW'
WHEN ReverseCurrRN = 1 then 'CURRENT'
ELSE 'BEFORE'
END) as [History]
--, CurrNo, ReverseCurrRN, MinCurrNoPerCust, MaxCurrNoPerCustDate ,MinCurrNoPerCust, MinDatePerCust, MaxDatePerCust
FROM
(
SELECT CustName, Country, RecordedTime, CurrNo
,ROW_NUMBER() OVER (PARTITION BY CustName ORDER BY CurrNo DESC) AS ReverseCurrRN
,MIN(CurrNo) OVER (PARTITION BY CustName) AS MinCurrNoPerCust
,MAX(CurrNo) OVER (PARTITION BY CustName, cast(RecordedTime AS DATE)) AS MaxCurrNoPerCustDate
,CAST(MIN(RecordedTime) OVER (PARTITION BY CustName) AS DATE) AS MinDatePerCust
,CAST(MAX(RecordedTime) OVER (PARTITION BY CustName) AS DATE) AS MaxDatePerCust
FROM Test t
WHERE CAST(RecordedTime AS DATE) between DATEADD(day,-1,@Date) and @Date
) q
WHERE MaxDatePerCust = @Date
AND (
-- Scenario 1 & 2 & 4 & 6
(MinDatePerCust = MaxDatePerCust AND (ReverseCurrRN = 1 OR CurrNo = MinCurrNoPerCust OR (MinCurrNoPerCust = 1 AND ReverseCurrRN = 2)))
-- Scenario 2 & 3 & 5 & 7 & 8
OR (MinDatePerCust != MaxDatePerCust AND (ReverseCurrRN = 1 OR CurrNo = 1 OR CurrNo = MaxCurrNoPerCustDate))
)
ORDER BY MinDatePerCust, CustName, CurrNo;
You can test it on db<>fiddle here
Upvotes: 1
Reputation: 1107
Based on my understanding, Below is the logic for your required output
step 1
- get max(CurrNo) for given date for each custName.
- here it is cte.
step 2
- get first entered record for the mentioned date for All CustNames available in cte if any.
- in the union of above record, get last edited record for records edited less than the current date for All CustNames available in cte if any.
- here it is cte2.
step 3
- get records from #test table where currNo=maximun or currno=1 from this step, you will get last updated records and any newly added record if any for given date.
- here it is the first query after cte and cte2.
step 4
- get first added record for given date if any with Audit as change and History as Before.
- this is for custom special case requirement
- here it is the second query.
step 5
- get the first record from cte2 to get one records for previously edited record for given date.
- here it is the third query.
-
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 00: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);
select * from #Test
declare @selectedDate date='2018-06-14';
with cte as
(
select CustName,max(CurrNo) maxno,count(1) cnt
from #Test where datediff(day,RecordedTime,@selectedDate)=0
group by CustName
),cte2 as (
select top 1 t.*,cnt
from #Test t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)>0 and t.CurrNo!=c.maxno
order by t.RecordedTime desc
union select top 1 t.*,cnt
from #Test t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)=0 and t.CurrNo!=c.maxno
order by t.RecordedTime
)
select t.CustName,t.Country,t.RecordedTime,cnt
,case when t.CurrNo=1 then 'ADD' else 'CHANGE' End as Audit
,case when t.CurrNo=1 then 'NEW' when t.CurrNo=c.maxno then 'CURRENT' else 'BEFORE' end History
from #Test t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)=0 and (t.CurrNo=c.maxno or t.CurrNo=1)
union
select top 1 t.CustName,t.Country,t.RecordedTime,cnt
,'CHANGE' Audit
,'BEFORE' History
from #Test t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)=0 and t.CurrNo=1 and c.cnt>1
union
select top 1 t.CustName,t.Country,t.RecordedTime,cnt
,'CHANGE' Audit
,'BEFORE' History
from cte2 t order by RecordedTime
drop table #Test
There is a nice point by "Ronen Ariely". And below is the updated query with 2 times table scan instead of 8.
with cte as
(
select CustName,max(CurrNo) maxno,count(1) cnt
from #Test where datediff(day,RecordedTime,@selectedDate)=0
group by CustName
),cte2 as (
select * from #Test where CustName in
(
select distinct custname from cte
)
and datediff(day,RecordedTime,@selectedDate)>=0
),cte3 as (
select top 1 t.*,cnt
from cte2 t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)>0 and t.CurrNo!=c.maxno
order by t.RecordedTime desc
union select top 1 t.*,cnt
from cte2 t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)=0 and t.CurrNo!=c.maxno
order by t.RecordedTime
)
select t.CustName,t.Country,t.RecordedTime
,case when t.CurrNo=1 then 'ADD' else 'CHANGE' End as Audit
,case when t.CurrNo=1 then 'NEW' when t.CurrNo=c.maxno then 'CURRENT' else 'BEFORE' end History
from cte2 t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)=0 and (t.CurrNo=c.maxno or t.CurrNo=1)
union
select top 1 t.CustName,t.Country,t.RecordedTime
,'CHANGE' Audit
,'BEFORE' History
from cte2 t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)=0 and t.CurrNo=1 and c.cnt>1
union
select top 1 t.CustName,t.Country,t.RecordedTime
,'CHANGE' Audit
,'BEFORE' History
from cte3 t order by RecordedTime
Upvotes: 1
Related Questions
- Query to get output as I need
- How to write sql query to get this result
- How to write a query for the following output?
- How to get below query
- How to get this kind of data?
- What SQL Server (T-SQL) Query can fetch this result?
- How to get this column result in SQL Server
- how to write SQL query for this result?
- I need a specific output
- How to get this output using SQL query