Blog Post

How to merge (combine) date intervals in T-SQL

,

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:

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating