Compare previous and next rows to derive output
-
Hello,
Below is the SQL code for sample data.If Object_ID('tempdb..#Case','U') IS NOT NULL
Drop Table #Case
GO
CREATE TABLE #Case
(
Customer Varchar(20),
TranID Int,
TranDate Date,
Reason Varchar(8),
TranStatus Char(1)
)
GO
INSERT INTO #Case (
Customer
,TranID
,TranDate
,Reason
,TranStatus
)
Select 'Customer1',1,'19 Jan 2018','CHEMO','D' Union All
Select 'Customer1',2,'20 Jan 2018','CHEMO','D' Union All
Select 'Customer1',3,'21 Jan 2018','SPORT','A' Union All
Select 'Customer1',4,'22 Jan 2018','CHEMO','D' Union All
Select 'Customer2',1,'19 Jan 2018','CHEMO','A' Union All
Select 'Customer2',2,'20 Jan 2018','CHEMO','D' Union All
Select 'Customer2',3,'21 Jan 2018','CHEMO','A' Union All
Select 'Customer2',4,'22 Jan 2018','SPORT','D' Union All
Select 'Customer3',1,'19 Jan 2018','CHEMO','A' Union All
Select 'Customer3',2,'20 Jan 2018','CHEMO','D' Union All
Select 'Customer3',3,'21 Jan 2018','CHEMO','D' Union All
Select 'Customer3',4,'22 Jan 2018','SPORT','D' Union All
Select 'Customer4',1,'19 Jan 2018','CHEMO','D' Union All
Select 'Customer4',2,'20 Jan 2018','CHEMO','D' Union All
Select 'Customer4',3,'21 Jan 2018','SPORT','A' Union All
Select 'Customer4',4,'22 Jan 2018','CHEMO','A' Union All
Select 'Customer5',1,'19 Jan 2018','CHEMO','D' Union All
Select 'Customer5',2,'20 Jan 2018','CHEMO','D' Union All
Select 'Customer5',3,'21 Jan 2018','SPORT','D' Union All
Select 'Customer5',4,'22 Jan 2018','CHEMO','D'The requirement is as below.
1. There are multiple transaction rows for a customer.
2. Each transaction will generate a new record with new transaction date and transaction id.
3. We want to pick all the record for those customer who satisfy any of the below criteria.
a. All customers for whom all the transaction records have reason = "Chemo" and Status = "D"
b. Customers for whom there is reason = "Chemo" and status = "D" followed by reason = "SPORT" and status = "A" as long as there is no subsequent record with reason = "Chemo" and status = "D"
c. Customers for whom there is reason = "Chemo" and status = "A" followed by reason = "CHEMO" and status = "D" as long as there is no subsequent record with reason = <ANY> (It can be Chemo or Sport) and status = "A"
-
You can try below lines of code. I hope that it will work.
;WITH CTE_Main AS (SELECT
Customer,
TranID,
TranDate,
Reason,
TranStatus,
LEAD(Reason) OVER (partition by customer ORDER BY TranDate) AS LeadReason,
LEAD(TranStatus) OVER (partition by customer ORDER BY TranDate) AS LeadStatus
FROM #Case), CTE_Case_1 AS (
SELECT
DISTINCT Customer
FROM CTE_Main M
WHERE (Reason = 'CHEMO' AND TranStatus = 'D' AND LeadReason = 'SPORT' AND LeadStatus = 'A')
AND NOT EXISTS(SELECT 1 FROM CTE_Main A WHERE A.Customer = M.Customer AND A.TranDate > M.TranDate AND A.Reason = 'CHEMO' AND A.TranStatus = 'D')
), CTE_Case_2 AS (
SELECT
DISTINCT Customer
FROM CTE_Main M
WHERE (Reason = 'CHEMO' AND TranStatus = 'A' AND LeadReason = 'CHEMO' AND LeadStatus = 'D')
AND NOT EXISTS(SELECT 1 FROM CTE_Main A WHERE A.Customer = M.Customer AND A.TranDate > M.TranDate AND A.TranStatus = 'A')
), CTE_CASE_3 AS (
SELECT
Customer
FROM #Case
GROUP BY Customer
HAVING MIN(Reason) = 'CHEMO' AND MIN(TranStatus) = 'D' AND MAX(TranStatus) = 'D' AND MAX(Reason) = 'CHEMO'
)
SELECT
CTE_Main.Customer,
TranID,
TranDate,
Reason,
TranStatus
FROM CTE_Main
LEFT JOIN CTE_Case_1 ON CTE_Main.Customer = CTE_Case_1.Customer
LEFT JOIN CTE_Case_2 ON CTE_Main.Customer = CTE_Case_2.Customer
LEFT JOIN CTE_Case_3 ON CTE_Main.Customer = CTE_Case_3.Customer
WHERE
(CTE_Case_1.Customer IS NOT NULL OR CTE_Case_2.Customer IS NOT NULL OR CTE_Case_3.Customer IS NOT NULL)
ORDER BY CTE_Main.Customer, TranDateRegards
VG -
Hi Vivek,
Thank you for your quick response on the query and efforts. As shown in the diagram, we do not want customer 4 in the output.
Why we do not want customer 4 : It has reason = "Chemo" and Status = "D" followed by reason = "Chemo" and Status = "A".Also we need Customer5 in the output as all the records have reason = "Chemo" and Status = "D".
Regards,
Akash - GroverVivek - Friday, January 19, 2018 6:28 AM
Hi Vivek,
Thank you for your quick response on the query and efforts. As shown in the diagram, we do not want customer 4 in the output.
Why we do not want customer 4 : It has reason = "Chemo" and Status = "D" followed by reason = "Chemo" and Status = "A".Also we need Customer5 in the output as all the records have reason = "Chemo" and Status = "D".
Regards,
Akash - akash_singh - Friday, January 19, 2018 7:27 AM
Customer5 has one record with "Sport"/"D", and does not qualify as having all "Chemo"/"D".
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
How about this? It's probably possible to tidy it up so it only does one table scan, instead of two, but I'm afraid I ran out of time
WITH ReasonsandStatuses AS (
SELECT
Customer
, TranID
, TranDate
, Reason
, TranStatus
, CASE WHEN Reason <> 'CHEMO' OR TranStatus <> 'D' THEN 0 ELSE TranID END AS ChemoD
, CASE WHEN Reason <> 'SPORT' OR TranStatus <> 'A' THEN 0 ELSE TranID END AS SportA
, CASE WHEN Reason <> 'CHEMO' OR TranStatus <> 'A' THEN 0 ELSE TranID END AS ChemoA
, CASE WHEN TranStatus = 'A' THEN TranID ELSE 0 END AS A
FROM #Case
)
, AreTheresubsequentReasonsandStatuses AS (
SELECT
Customer
, TranID
, TranDate
, Reason
, TranStatus
, ChemoD
, SportA
, ChemoA
, A
, COALESCE(MIN(ChemoD) OVER (PARTITION BY Customer ORDER BY TranID ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0) AS MinChemoD
, COALESCE(MAX(ChemoD) OVER (PARTITION BY Customer ORDER BY TranID ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0) AS MaxChemoD
, COALESCE(MIN(SportA) OVER (PARTITION BY Customer ORDER BY TranID ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0) AS MinSportA
, COALESCE(MAX(SportA) OVER (PARTITION BY Customer ORDER BY TranID ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0) AS MaxSportA
, COALESCE(MAX(A) OVER (PARTITION BY Customer ORDER BY TranID ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0) AS MaxA
FROM ReasonsandStatuses
)
SELECT
x.Customer
, x.TranID
, x.TranDate
, x.Reason
, x.TranStatus
FROM AreTheresubsequentReasonsandStatuses a
CROSS APPLY ( -- to make sure we capture all the rows for a customer
SELECT
Customer
, TranID
, TranDate
, Reason
, TranStatus
FROM AreTheresubsequentReasonsandStatuses
WHERE Customer = a.Customer
) x
WHERE (a.TranID = 1 AND a.MinChemoD > 1 AND a.MaxChemoD > 1) -- criterion a
OR (a.ChemoD > 0 AND a.MinSportA > 0 AND a.MaxChemoD < a.MaxSportA) -- criterion b
OR (a.ChemoA > 0 AND a.MaxChemoD > 0 AND a.MaxA < a.MaxChemoD) -- criterion cJohn
- drew.allen - Friday, January 19, 2018 8:48 AM
Adding one more thing:
Customer 4 satisfies following point
b. Customers for whom there is reason = "Chemo" and status = "D" followed by reason = "SPORT" and status = "A" as long as there is no subsequent record with reason = "Chemo" and status = "D"Regards
VG - GroverVivek - Saturday, January 20, 2018 1:15 AM
No it doesn't. It only satisfies the first part of the condition "followed by reason = "SPORT"". It fails the condition "and status = "A"".
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply