将相距少于15天的行分组，并指定最小/最大日期 [英] Group rows with that are less than 15 days apart and assign min/max date

问题描述

如果不同协议的protocol_opening_date相隔15天之内，我需要在另一列中将它们作为一种协议显示在名为预计开始日期"的列中.

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.

因此，假设一个协议的开始日期为24.01.2018，结束日期为30.01.2018，另一协议的开始日期为25.01.2018，结束日期为10.02.2018

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

我需要将它们显示为不同的协议，起始日期为24.01，结束日期为10.02，因为它们之间的间隔为15天.

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.

我添加了表格的一部分，相同的颜色是被组合成一个协议的协议.Beklenen_baslangıc==预期开始日期

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

推荐答案

如注释中所述，您可以为此使用 LAG 函数.基本思想是为每行分配一个0/1值:如果它在上一行的15天之内，则为0，否则为1.然后使用 SUM()OVER()转换1和0到可以用于分组的数字.

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.

请注意，这可以将更长的日期范围分组在一起，例如 01-01 ， 01-11 ， 01-21 ， 02-01 和 02-11将被分组在一起，尽管开始日期和结束日期相隔15天以上.

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

DB Fiddle上的演示

这篇关于将相距少于15天的行分组，并指定最小/最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！