SQL Server concatenate date periods

I have a table as follows which contains periods of contracts:
| id | year | startdate | enddate | drop_out | covered_days |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | 2015 | 2015-03-01 | 2020-12-31 | null | null |
| 1 | 2016 | 2015-03-01 | 2020-12-31 | null | null |
| 1 | 2017 | 2015-03-01 | 2020-12-31 | null | null |
| 1 | 2018 | 2015-03-01 | 2020-12-31 | null | null |
| 1 | 2019 | 2015-03-01 | 2020-12-31 | null | null |
| 1 | 2020 | 2015-03-01 | 2020-12-31 | null | null |
| 1 | 2021 | 2021-01-01 | 2021-04-20 | 2021-04-20 | 2242 |
| 3 | 2014 | 2014-03-01 | 2019-08-09 | null | null |
| 3 | 2015 | 2014-03-01 | 2019-08-09 | null | null |
| 3 | 2016 | 2014-03-01 | 2019-08-09 | null | null |
| 3 | 2017 | 2014-03-01 | 2019-08-09 | null | null |
| 3 | 2018 | 2014-03-01 | 2019-08-09 | null | null |
| 3 | 2019 | 2014-03-01 | 2019-08-09 | 2019-08-09 | 1987 |
| 3 | 2019 | 2019-08-12 | 2020-01-31 | null | null |
| 3 | 2020 | 2019-08-12 | 2020-01-31 | 2020-01-31 | 172 |
| 5 | 2015 | 2015-03-01 | 2015-03-31 | null | null |
| 5 | 2015 | 2015-04-01 | 2015-04-09 | 2015-04-09 | 39 |
| 5 | 2015 | 2015-04-11 | 2015-04-16 | 2015-04-16 | 5 |
| 5 | 2015 | 2015-04-18 | 2015-04-23 | 2015-04-23 | 5 |
| 5 | 2016 | 2016-06-01 | 2016-07-30 | null | null |
| 5 | 2016 | 2016-07-31 | 2017-02-03 | null | null |
| 5 | 2017 | 2016-07-31 | 2017-02-03 | null | null |
| 5 | 2017 | 2017-02-04 | 2017-09-13 | 2017-09-13 | 469 |
| 5 | 2017 | 2017-09-15 | 2017-09-17 | 2017-09-17 | 2 |
| 5 | 2017 | 2017-09-19 | 2019-04-08 | null | null |
| 5 | 2018 | 2017-09-19 | 2019-04-08 | null | null |
| 5 | 2019 | 2017-09-19 | 2019-04-08 | 2019-04-08 | 566 |
| 5 | 2019 | 2019-04-10 | 2019-04-26 | 2019-04-26 | 16 |
| 5 | 2019 | 2019-04-28 | 2020-12-31 | null | null |
| 5 | 2020 | 2019-04-28 | 2020-12-31 | 2020-12-31 | 613 |

If you take a look at ID 1 the first period starts on 2015-03-01 up to 2020-12-31 but a second period follows without a gap starting on 2021-01-01 up to 2021-04-20. I need to concatenate these periods.

ID 3 and 5 have gaps in between their periods. Thus, I need to list every period separately.

I would like to create a table that takes into account the respective periods like that:
| id | year | startdate | enddate | drop_out | covered_days |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | 2015 | 2015-03-01 | 2021-04-20 | 2021-04-20 | 2242 |
| 1 | 2016 | 2015-03-01 | 2021-04-20 | 2021-04-20 | 2242 |
| 1 | 2017 | 2015-03-01 | 2021-04-20 | 2021-04-20 | 2242 |
| 1 | 2018 | 2015-03-01 | 2021-04-20 | 2021-04-20 | 2242 |
| 1 | 2019 | 2015-03-01 | 2021-04-20 | 2021-04-20 | 2242 |
| 1 | 2020 | 2015-03-01 | 2021-04-20 | 2021-04-20 | 2242 |
| 3 | 2014 | 2014-03-01 | 2019-08-09 | 2019-08-09 | 1987 |
| 3 | 2015 | 2014-03-01 | 2019-08-09 | 2019-08-09 | 1987 |
| 3 | 2016 | 2014-03-01 | 2019-08-09 | 2019-08-09 | 1987 |
| 3 | 2017 | 2014-03-01 | 2019-08-09 | 2019-08-09 | 1987 |
| 3 | 2018 | 2014-03-01 | 2019-08-09 | 2019-08-09 | 1987 |
| 3 | 2019 | 2014-03-01 | 2019-08-09 | 2019-08-09 | 1987 |
| 3 | 2019 | 2019-08-12 | 2020-01-31 | 2020-01-31 | 172 |
| 3 | 2020 | 2019-08-12 | 2020-01-31 | 2020-01-31 | 172 |

I have tried the following but it does only work for ID 1 which has no gap in between the periods.

db<>fiddle

WITH CombinedPeriods AS (
  SELECT
    id,
    startdate AS combined_startdate,
    enddate AS combined_enddate,
    covered_days AS combined_coverage_days
  FROM YourTableName 
  UNION 
  SELECT
    id,
    CASE WHEN LAG(enddate) OVER (PARTITION BY id ORDER BY startdate) >= startdate THEN LAG(enddate) OVER (PARTITION BY id ORDER BY startdate) ELSE startdate END AS combined_startdate,
    enddate AS combined_enddate,
    covered_days AS combined_coverage_days
  FROM YourTableName
)
SELECT
  id,
  MIN(combined_startdate) AS combined_startdate,
  MAX(combined_enddate) AS combined_enddate,
  SUM(combined_coverage_days) AS combined_coverage_days
FROM CombinedPeriods
GROUP BY id;

WITH RecursivePeriods AS (
  SELECT
    id,
    year,
    startdate,
    enddate,
    drop_out,
    covered_days,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY startdate) AS rn
  FROM YourTableName
),
CombinedPeriods AS (
  SELECT
    rp1.id,
    rp1.year,
    rp1.startdate AS combined_startdate,
    rp1.enddate AS combined_enddate,
    rp1.drop_out AS combined_drop_out,
    rp1.covered_days AS combined_coverage_days,
    rp1.rn
  FROM RecursivePeriods rp1
  LEFT JOIN RecursivePeriods rp2 ON rp1.id = rp2.id AND rp1.rn = rp2.rn - 1
  WHERE rp2.id IS NULL
  UNION ALL
  SELECT
    cp.id,
    rp.year,
    cp.combined_startdate,
    CASE WHEN rp.startdate > cp.combined_enddate THEN rp.enddate ELSE cp.combined_enddate END,
    CASE WHEN rp.startdate > cp.combined_enddate THEN rp.drop_out ELSE cp.combined_drop_out END,
    CASE WHEN rp.startdate > cp.combined_enddate THEN rp.covered_days ELSE cp.combined_coverage_days END,
    rp.rn
  FROM CombinedPeriods cp
  JOIN RecursivePeriods rp ON cp.id = rp.id AND cp.rn = rp.rn - 1
)
SELECT
  id,
  year,
  combined_startdate,
  MAX(combined_enddate) AS combined_enddate,
  MAX(combined_drop_out) AS combined_drop_out,
  SUM(combined_coverage_days) AS combined_coverage_days
FROM CombinedPeriods
GROUP BY id, year, combined_startdate
ORDER BY id, year;

SQL Server concatenate date periods

1条答案

相关问题

热门标签

最新问答