-1

I have a table with 4 columns.I need to merge the rows into one record if the difference of the end_date and start_date of the next row is less than 120 days difference. This merge should be for each name.

enter image description here

Please see the expected results below

enter image description here

  • Does the ranges can overlap? does the adjacent records (where end_date(1) = start_date(2) - 120 days) must be linked? – Akina May 16 at 9:09
0

Test:

WITH cte AS (
SELECT id, name, start_date, end_date
FROM test
UNION ALL
SELECT test.id, test.name, test.start_date, cte.end_date
FROM test, cte
WHERE cte.start_date BETWEEN test.start_date AND DATEADD(day, 120, test.end_date)
  AND test.name = cte.name
  AND test.id < cte.id
),
cte2 AS (
SELECT id, 
       name, 
       start_date, 
       end_date,
       MAX(start_date) OVER (PARTITION BY name, end_date) xstart_date, 
       MAX(end_date)  OVER (PARTITION BY name, start_date) xend_date
FROM cte
)
SELECT DISTINCT
       MAX(id) OVER (PARTITION BY name, xend_date) id,
       name,
       MIN(start_date) OVER (PARTITION BY name, xend_date) start_date,
       MAX(end_date) OVER (PARTITION BY name, xend_date) end_date
FROM cte2
ORDER BY id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.