On my current project, I am dealing with date intervals in T-SQL very heavily. I’ve hit interesting issue recently – how to combine/merge date intervals into more intervals.
I am talking about this:
I was googling and googling but found only not suitable solutions or solutions which I don’t like. That’s why I am now sharing solution which I find quite ellegant. Feel free to comment it or propose anything better. Thanks
-- create UDTT which serves as input parameter CREATE TYPE Interval AS TABLE (datefrom DATETIME, dateto DATETIME) -- create UDF which combines date intervals CREATE FUNCTION fn_MergeIntervals (@intervals Interval READONLY) RETURNS @output TABLE (datefrom DATETIME, dateto DATETIME) AS BEGIN -- create unique milestones consisting of datefrom and dateto of each interval WITH milestones (milestoneOrder, milestone, milestoneType) AS ( SELECT ROW_NUMBER() OVER (ORDER BY milestone) milestoneOrder, -- create order for each milestone milestone, milestoneType FROM (SELECT datefrom as milestone, 1 AS milestoneType FROM @intervals UNION SELECT dateto, 2 FROM @intervals) AS ms ) -- join milestone to previous milestone based on ROW_NUMBER orderring INSERT INTO @output SELECT CASE WHEN m.milestoneType = 2 THEN DATEADD(dd, 1, m.milestone) -- distinguish between starting and ending date ELSE m.milestone END datefrom , CASE WHEN s.milestoneType = 1 THEN DATEADD(dd, -1, s.milestone) -- distinguish between starting and ending date ELSE s.milestone END dateto FROM milestones m JOIN milestones s ON s.milestoneOrder = m.milestoneOrder + 1 RETURN END -- fill intervals from sample DECLARE @intervals AS Interval INSERT INTO @intervals VALUES ('20110101','20110430'); INSERT INTO @intervals VALUES ('20110420','20111005'); INSERT INTO @intervals VALUES ('20110415','20111130'); -- merge intervals SELECT * FROM fn_MergeIntervals (@intervals)
Result is following: