Group rows with that are less than 15 days apart and assign min/max date

Question

If the protocol_opening_date of different protocols are within 15 days of each other, i need to show them as one protocol in another column named expected start date.

I don't know how to copy my table here as it looks but i'll try to explain as much as i can.

So let's say if one protocol has the start_date of 24.01.2018 and end_date of 30.01.2018 and an other one has start_date of 25.01.2018 and end_date of 10.02.2018

I need to display them as a different protocol with a start_date of 24.01 and end_date of 10.02 because they are 15 days within each other.

Another big issue is that i don't have a clue how to compare these two protocols with a third or fourth one. Because even if there is more than 2 protocols in the same date interval, i don't know how to compare them to each other.

Edit: I added a portion of the table, same colors are the protocols that's been combined into one protocol. Beklenen_baslangıc == expected_start_date

So it should be transitive? I.e. if we have one protocol every 14 days throughout a year, we should get one result the entire length of the year, combining all of these? — Damien_The_Unbeliever, Nov 28 '18 at 12:43
What RDBMs are you using (MySQL, SQL Server, etc) and what version. This could be solved easily with window functions. — Salman A, Nov 28 '18 at 12:47
We create protocols based on patients, not based on date or a spesific amount of protocols per year. @Damien_The_Unbeliever — Poyraz Kürkçüoğlu, Nov 28 '18 at 12:59
I was meaning more "if we find this pattern" rather than saying that's how it's been produced. — Damien_The_Unbeliever, Nov 28 '18 at 13:01
Any patient can have N number of protocols assigned. The thing is we need to shorten the amount of protocols created based on their creation and end dates which has a rule of being 15 days within eachother.I hope i clarified it enough. — Poyraz Kürkçüoğlu, Nov 28 '18 at 13:03
How is 15 days within each other counted? StartDate of previous compared to StartDate of next or EndDate of prev to Start Date of next? — Salman A, Nov 28 '18 at 13:07
Its the difference between StartDate1 and StartDate2 @SalmanA — Poyraz Kürkçüoğlu, Nov 28 '18 at 13:10
It's almost like a "Combination Set" . If two dates intersect in less than 15 days, we combine them. — Poyraz Kürkçüoğlu, Nov 28 '18 at 13:12
See if you can provide additional data (more dates and a description of which dates needs to be combined together). It is doable in SQL 2012. — Salman A, Nov 28 '18 at 13:20
I added a portion of the table , i hope it makes sense to you. @SalmanA — Poyraz Kürkçüoğlu, Nov 28 '18 at 13:26
It looks like 4810711 (end date 10/2 + 15 days = 25/2) overlaps 4862536 (start date 23/2). Can you confirm if that is the case? — Jim Jimson, Nov 28 '18 at 15:03

Salman A · Accepted Answer · 2018-11-28T18:38:30.090

As mentioned in comments, you could use the LAG function for this. The basic idea is to assign a 0/1 value to each row: if it is within 15 days of previous row then 0 else 1. Then use SUM() OVER () to convert the 1s and 0s to numbers which could be used for grouping.

Note that this could group much longer date ranges together e.g. 01-01, 01-11, 01-21, 02-01 and 02-11 will be grouped together although the first and last dates are more than 15 days apart.

DECLARE @T TABLE (HASTA_ID INT, PROTOKOL_ID INT, STARTDATE DATE, ENDDATE DATE);
INSERT INTO @T VALUES
(273065, 11, '2018-01-24', '2018-01-30'),
(273065, 12, '2018-01-25', '2018-02-10'),
(273065, 13, '2018-01-30', '2018-01-30'),
(273065, 14, '2018-02-23', '2018-02-28'),
(273065, 15, '2018-03-21', '2018-03-29'),
(273065, 16, '2018-05-03', '2018-05-04'),
(273065, 17, '2018-05-03', '2018-05-08'),
(273065, 18, '2018-05-14', '2018-05-22'),
(273065, 19, '2018-05-22', '2018-05-23'),
(273065, 20, '2018-09-20', '2018-09-30');

WITH CTE1 AS (
    SELECT *, CASE WHEN LAG(STARTDATE) OVER (PARTITION BY HASTA_ID ORDER BY STARTDATE) >= DATEADD(DAY, -14, STARTDATE) THEN 0 ELSE 1 END AS CHG
    FROM @T
), CTE2 AS (
    SELECT *, SUM(CHG) OVER (PARTITION BY HASTA_ID ORDER BY STARTDATE) AS GRP
    FROM CTE1
)
SELECT *,
    MIN(STARTDATE) OVER (PARTITION BY HASTA_ID, GRP) AS EX_STARTDATE,
    MAX(ENDDATE) OVER (PARTITION BY HASTA_ID, GRP) AS EX_ENDDATE
FROM CTE2
ORDER BY HASTA_ID, STARTDATE

Demo on DB Fiddle

Group rows with that are less than 15 days apart and assign min/max date

1 Answers1

Linked