Say I have a data set. I want to be able to group based upon the wrap up date. Wrap up signals the end of a group.
In other words I want to produce the following data but I can't get the SQL right. Only when there is a "wrap-up" should it signal the end of a current group and the start of a new group on the next TransCd that is not a wrap up. I am trying to generate that group column.
In the future, please provide directly usable data, like this:
CREATE TABLE #data ( customer int not null, transdate date not null, transcode varchar(30) null );
INSERT INTO #data VALUES
(1, '01/13/2023', 'Data1'),
(1, '01/26/2023', 'Data2'),
(1, '01/27/2023', 'Data3'),
(1, '01/27/2023', 'WRAP-UP'),
(1, '02/10/2023', 'Data4'),
(1, '02/10/2023', 'Data5'),
(1, '02/10/2023', 'Data6'),
(1, '02/20/2023', 'WRAP-UP'),
(1, '03/22/2023', 'Data7'),
(1, '03/22/2023', 'Data8'),
(1, '03/22/2023', 'Data9'),
(1, '03/24/2023', 'Data10'),
(1, '03/30/2023', 'WRAP-UP'),
(1, '03/31/2023', 'Data11'),
(1, '04/01/2023', 'Data12')
Actual SQL code:
;WITH cte_wrap_ups AS (
SELECT customer, transdate, ROW_NUMBER() OVER(PARTITION BY customer ORDER BY transdate) AS row_num
FROM #data
WHERE transcode = 'WRAP-UP'
)
SELECT d.*, ISNULL(oa1.row_num, (SELECT MAX(row_num) + 1 FROM cte_wrap_ups)) AS [group]
FROM #data d
OUTER APPLY (
SELECT TOP (1) cwu.*
FROM cte_wrap_ups cwu
WHERE cwu.customer = d.customer AND cwu.transdate >= d.transdate
ORDER BY cwu.transdate
) AS oa1
or with:
CREATE TABLE #t
(
Customer int NOT NULL default (1)
,TransDate date NOT NULL
,TransCode varchar(10) NOT NULL
);
/* Best to give dates in ISO format. */
INSERT INTO #t (TransDate, TransCode)
VALUES ('20230113', 'd1')
,('20230126', 'd2')
,('20230127', 'd3')
,('20230127', 'WRAP-UP')
,('20230210', 'd4')
,('20230210', 'd5')
,('20230210', 'd6')
,('20230220', 'WRAP-UP')
,('20230322', 'd7')
,('20230322', 'd8')
,('20230322', 'd9')
,('20230324', 'd10')
,('20230330', 'WRAP-UP')
,('20230331', 'd11')
,('20230401', 'd12');
select * from #t;The following should work:
WITH Boundaries
AS
(
SELECT T.Customer, T.TransDate, T.TransCode, X.OrdInDate
,CASE
WHEN LAG(T.TransCode) OVER (PARTITION BY T.Customer ORDER BY T.TransDate, X.OrdInDate)
= 'WRAP-UP'
THEN 1
ELSE 0
END AS Boundary
FROM #t T
CROSS APPLY
(
/* As a table is an unordered set, this CASE is required to guarantee WRAP-UP is after d3 on 01/27 */
VALUES (CASE WHEN T.TransCode = 'WRAP-UP' THEN 1 ELSE 0 END)
) X (OrdInDate)
)
SELECT Customer, TransDate, TransCode
,SUM(Boundary)
OVER (PARTITION BY Customer ORDER BY TransDate, OrdInDate) AS Grp
FROM Boundaries
ORDER BY TransDate, OrdInDate;