Merge date ranges eliminating duplicates
-
Hi,
Does anyone know how to create a set of date ranges from existing ranges to create a unique set of ranges?
Eg.
1 2018-08-21 2018-09-03
2 2018-09-01 2018-10-15
3 2019-02-07 2019-05-02
4 2019-04-21 2018-05-01Desired Result.
1 2018-08-21 2018-10-15
2 2019-02-07 2019-05-02Any suggestions must appricated
- carl.williamson - Monday, September 10, 2018 9:31 AM
You could start off posting your question with something that would create some DDL or return a table that people can work with, for this type of problem it's useful to have a row id:
WITH CTE AS
(
SELECT *
FROM (VALUES (1,CONVERT(date,'2018-08-21'), CONVERT(date,'2018-09-03')),
(2,'2018-09-01', '2018-10-15'),
(3,'2019-02-07', '2019-05-02'),
(4,'2019-04-21', '2019-05-01')) D(Id,StartDate,EndDate)
),
Overlapping1 AS
(
SELECT DISTINCT
IIF(A.StartDate<= B.StartDate, A.StartDate,B.StartDate) StartDate,
IIF(A.EndDate<= B.EndDate, B.EndDate,A.EndDate) EndDate
FROM CTE A
INNER JOIN CTE B
ON A.EndDate >= B.StartDate
AND A.StartDate <= B.EndDate
AND A.Id <> B.Id)
SELECT A.StartDate,
A.EndDate
FROM Overlapping1 A
UNION ALL
SELECT A.StartDate,
A.EndDate
FROM CTE A
WHERE NOT EXISTS(SELECT *
FROM Overlapping1 B
WHERE B.StartDate <= A.EndDate
AND B.EndDate >= A.StartDate)
PS: I think there is an error in your Line 4 "4 2019-04-21 2018-05-01"
(the end date is less than the start date)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply