CODEWITHSUNDEEP
sqlsql-server
SRU
SRU

Reputation: 53

Grouping date ranges into rolling group of 15 days

I have data with start and end datetime on each row, I'm trying to group all rows that fall within 15days of the end datetime, reset and begin new group using the end datetime when the start date doesn't fall in 15 days.

I tried different solutions like using logic to self join and try to pick min end date after looking for start date time between end datetime and end datetime + 30, but I'm not able to identify a way to continue using end datetime from the start/previous row when the start datetime is within 15days till it finds a row that doesn't. 

with cte as
(
SELECT 1 id, '2018-12-25' col1, '2019-01-05' col2
UNION ALL
SELECT 1, '2019-03-01' col1,    '2019-03-10' col2
UNION ALL
SELECT 1, '2019-03-15' col1,    '2019-03-19' col2
UNION ALL
SELECT 1, '2019-03-22' col1,    '2019-03-28' col2
UNION ALL
SELECT 1, '2019-03-30' col1,    '2019-04-02' col2
UNION ALL
SELECT 1, '2019-04-10' col1,    '2019-04-15' col2
UNION ALL
SELECT 1, '2019-04-18' col1,    '2019-04-25' col2
), STG AS
(
SELECT A.*,  MIN(B.COL2) AS GRP_COL2
FROm CTE A
LEFT OUTER JOIN CTE B ON A.col1 BETWEEN B.col2 AND DATEADD(day, 15, B.col2)
GROUP BY A.id, A.col1, A.col2
)
SELECT A.id, A.col1, A.col2, COALESCE(GRP_COL2, A.COL2) AS GRP_COL2
FROM STG A

ORDER By 1,2
--this returns wrong result for 04/10 and 04/18 dated rows in this case

For example I have following data:

ProductId   ProductStartDt   ProductEndDt
1           2018-12-25       2019-01-05
1           2019-03-01       2019-03-10
1           2019-03-15       2019-03-19
1           2019-03-22       2019-03-28
1           2019-03-30       2019-04-02
1           2019-04-10       2019-04-15
1           2019-04-18       2019-04-25

So what I'm trying to do is start from min end date and group all rows with start date that fall in end date + 15 days range as one group and start next row as new group and continue using the end date of that row as new start for group.

Expected Output

ProductId   ProductStartDt   ProductEndDt   GroupNo
1           2018-12-25       2019-01-05     1
1           2019-03-01       2019-03-10     2
1           2019-03-15       2019-03-19     2
1           2019-03-22       2019-03-28     2
1           2019-03-30       2019-04-02     3
1           2019-04-10       2019-04-15     3
1           2019-04-18       2019-04-25     4

For group 2, the start date doesn't fall within 15days of the end date from Group 1 or start row and this will trigger start of new rows. We see that 03/15 and 03/22 are within 15 days of 03/10 and we group them into single group. 03/30 doesn't fall in the previous group and this will cause start of new group 3 till we find all start dates within the end datetime + 15days.

Upvotes: 2

Views: 194

Answers (1)

Renat
Renat

Reputation: 8982

With recursive CTE we can iterate over initial table, and decide whether current row belongs an existing group or it's a new group:

;with Product as (
    select * from (
        VALUES 
(1, '2018-12-25','2019-01-05' ),
(1, '2019-03-01','2019-03-10' ),
(1, '2019-03-15','2019-03-19' ),
(1, '2019-03-22','2019-03-28' ),
(1, '2019-03-30','2019-04-02' ),
(1, '2019-04-10','2019-04-15' ),
(1, '2019-04-18','2019-04-25' )
    ) as a1 (ProductId   ,ProductStartDt   ,ProductEndDt)
), OrderedProduct as (
    select *, ROW_NUMBER() over (order by ProductStartDt) as RowNum
    from Product
), DateGroupsInterim (RowNum, GroupNum, GrpStartDt) as (
    select RowNum, 1, ProductEndDt
    from OrderedProduct
    where RowNum=1
    union all 
    select OrderedProduct.RowNum, 
        CASE WHEN OrderedProduct.ProductStartDt <= dateadd(day, 15, dgi.GrpStartDt) 
                THEN dgi.GroupNum 
                ELSE dgi.GroupNum + 1
        END,
        CASE WHEN OrderedProduct.ProductStartDt <= dateadd(day, 15, dgi.GrpStartDt) 
                THEN dgi.GrpStartDt 
                ELSE OrderedProduct.ProductEndDt
        END
    from DateGroupsInterim dgi
        join OrderedProduct on OrderedProduct.RowNum=dgi.RowNum+1
) select OrderedProduct.ProductId, OrderedProduct.ProductStartDt, OrderedProduct.ProductEndDt, DateGroupsInterim.GroupNum as GroupNo
from DateGroupsInterim
    JOIN OrderedProduct on OrderedProduct.RowNum = DateGroupsInterim.RowNum;

Upvotes: 2

Related Questions