Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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:

Comments

Posted by Anonymous on 10 July 2011

Pingback from  Dew Drop – July 10, 2011 | Alvin Ashcraft's Morning Dew

Posted by Joe Celko on 11 July 2011

The original version of this was "The Anesthesia Puzzle" by Leonard Medal, which is #3 in my SQL PUZZLES & ANSWERS book. The anesthesiologists move from operating room to operating room starting several procedures one after the other and finally returning to finish them. The puzzle was to find who had (n) procedures open in each hour.

There were five solutions.

Posted by Anonymous on 11 July 2011

Pingback from  Weekly Link Post 201 « Rhonda Tipton's WebLog

Posted by Jakub Dvorak on 11 July 2011

Hi Mr.Celko, thank for the reference and will certainly read this, look interesting! But I am bit sensitive to plagiarism notes. I am sorry but this is MY ORIGINAL version which I've spent my time on without any books or samples. It has own background from banking world. If you look to my posts, whenever I use some other author's resource, I am linking it. I now went through solution mentioned in "The Anesthesia Puzzle" and none of the solution looks like one posted here. There's one similar with starting and ending milestones but I have clear consciousness that I've invented posted one for myself.

Posted by George H. on 19 February 2013

Jakub,

This was VERY helpful! I had a problem very close to this and your example was extremely helpful.

Leave a Comment

Please register or log in to leave a comment.