Date Range Consolidation Query

Question

Date Range Consolidation Query

Paul McCaffrey

SSC Veteran

Points: 226
More actions
March 15, 2019 at 5:13 pm

#382020

Hi there,
I have a table that stores date periods against PersonID's and there should be no overlapping date periods for a given PersonID (a period starting on the same day as another ending is fine).
However, we have some historic records which break this rule and are in need of tidying up.
So, what I'm after is to keep the record with the earliest start date and update the end date to be the MAX end date of any records that overlap it
All overlapping records other than the row updated above should be deleted from the table.
Any records that don't have date overlaps should be left alone
So, if the table looked like this;
RefID PersonID    RefStart    RefEnd 1        1000        2010-01-01    2010-08-01 2        1000        2010-02-01    2010-06-01 3        1000        2011-02-01    2011-05-01 4        1000        2012-05-01    2014-05-01 5        2000        2011-01-01    2012-01-01 6        2000        2012-01-01    2014-01-01 7        3000        2014-06-01    2014-08-01 8        3000        2014-07-01    2015-05-01
Then, the final version of the table should look like this;
RefID    PersonID    RefStart    RefEnd 1        1000        2010-01-01    2011-05-01 4        1000        2012-05-01    2014-05-01 5        2000        2011-01-01    2012-01-01 6        2000        2012-01-01    2014-01-01 7        3000        2014-06-01    2015-05-01
Sample Code;
CREATE TABLE #Refs (     RefID INT,     PersonID INT,     RefStart DATETIME,     RefEnd DATETIME )
INSERT INTO #Refs (     RefID,     PersonID,     RefStart,     RefEnd ) VALUES (1, 1000, '20100101', '20100801'), (2, 1000, '20100201', '20100601'), (3, 1000, '20110201', '20110501'), (4, 1000, '20120501', '20140501'), (5, 2000, '20110101', '20110101'), (6, 2000, '20120101', '20140101'), (7, 3000, '20140601', '20140801'), (7, 3000, '20140701', '20150501')
It's been a long week, I'm tired and I just can't seem to come up with an elegant way of doing this 🙁
Any help/advice would be greatly appreciated.
Thanks in advance.
Paul

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

It's an older article but still one of the best ways to do such a thing as you ask. Please see the following...
https://blogs.solidq.com/en/sqlserver/packing-intervals/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul McCaffrey SSC Veteran Points: 226 More actions · Answer 2

Jeff Moden - Friday, March 15, 2019 6:52 PM
It's an older article but still one of the best ways to do such a thing as you ask. Please see the following...
https://blogs.solidq.com/en/sqlserver/packing-intervals/

Hi Jeff,

Thanks for the link - That is a shining example of how a SQL coding problem/example should be presented, I am very impressed.

I should have been a bit clearer in my initial post, I had actually come up with a way of getting the packed intervals into a result set (although nowhere near as efficiently as the code in the link you supplied), I was more struggling with how to then link the desired result set back to the source table to remove the now redundant periods/update the end dates of the periods that are being kept and deal with any other elements linked to the redundant periods.

Anyway with fresh eyes this morning, I've adapted the linked code to suit my needs (null end dates weren't catered for) and come up with a way of linking the desired result set back to the source table for further manipulation.

Cheers

Paul