How to remove overlapping periods from a table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to remove overlapping periods from a table?

Author

Topic

agarwaldvk
Starting Member

3 Posts

Posted - 2014-08-02 : 10:12:55

Hi Everybody

I have a table with 4 fields called 'NMI', 'startdate' and 'enddate'. For a particular value for 'NMI', there can a number of records with different start and end dates. I need to eliminate all records with any overlapping periods. The record with the oldest 'startdate' needs to be retained in all cases. In the situation where there are multiple records with the same oldest 'startdate', the one with the largest 'enddate' needs to be retained.

RowID is unique (primary key)

As an example, here is a data set (sorted by 'startdate' - asc.

RowID NMI startdate enddate
1 NCCC002321 01/10/2013 30/09/2015
2 NCCC002321 01/03/2014 31/10/2014
3 NCCC002321 01/04/2014 31/03/2015
4 NCCC002321 01/05/2014 31/12/2014
5 NCCC002321 01/05/2014 30/04/2015
6 NCCC002321 20/05/2014 19/05/2015
7 NCCC002321 01/06/2014 31/05/2015
8 NCCC002321 02/06/2014 31/12/2014
9 NCCC002321 01/07/2014 30/09/2014
10 NCCC002321 01/07/2014 30/09/2014
11 NCCC002321 01/07/2014 31/12/2014
12 NCCC002321 01/07/2014 30/06/2015
13 NCCC002321 01/07/2014 30/06/2015
14 NCCC002321 01/07/2014 30/06/2015
15 NCCC002321 01/07/2014 30/06/2015
16 NCCC002321 01/07/2014 30/06/2015
17 NCCC002321 01/07/2014 30/06/2015
18 NCCC002321 01/07/2014 30/06/2015
19 NCCC002321 01/07/2014 30/06/2015
20 NCCC002321 01/07/2014 30/06/2015
21 NCCC002321 01/07/2014 30/06/2015
22 NCCC002321 01/07/2014 30/09/2015
23 NCCC002321 01/08/2014 31/07/2015
24 NCCC002321 14/10/2014 30/09/2017
25 NCCC002321 17/10/2014 30/09/2015
26 NCCC002321 01/11/2014 31/10/2015
27 NCCC002321 23/11/2014 31/10/2017
28 NCCC002321 01/12/2014 30/11/2015
29 NCCC002321 01/01/2015 31/12/2017
30 NCCC002321 01/05/2015 30/04/2016
31 NCCC002321 20/05/2015 31/12/2015
32 NCCC002321 01/07/2015 30/06/2016
33 NCCC002321 01/07/2015 31/12/2017
34 NCCC002321 01/10/2015 31/12/2017
35 NCCC002321 01/05/2016 30/04/2017
36 NCCC002321 01/07/2016 30/06/2017

I only need to keep record with RowId 1 and 34 since all other records are overlapping with either of these records.

Any assistance on this is highly valued. I need this as a very urgent need to be able to complete my month end processing starting on Monday, Melbourne time!

Best regards

Deepak

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-02 : 18:17:50

I can back into your output via 1 of my several attempts, but I am not sure it is what you are looking for - I think you need to define what you see as overlapping.

For example: You want rowid 34 on your output, yet OCT 1 2015 as the date overlaps with other ranges: for instance RowID 32, July 1 2015 to June 30 2016 as well as others. October 1 2015 is a date covered in that range.

So what is your definition of an overlapping period in detail with an example. I think I have it covered with one of the my attempts, but would like to make sure I am following you

agarwaldvk
Starting Member

3 Posts

Posted - 2014-08-02 : 19:14:39

Hi MichaelJSQL

Thanks for your response.

Both row 32 and row 33 overlap with Row1 as the startdate for these records (01 Jul 2015) fall in the period in Row1. Hence Row32 overlaps Row1 and needs to be excluded.

All rows between 2 and 33 inclusive are overlapping with Row1 and hence can be excluded. Rows 35 and 36 are overlapping with Row 34. But Row1 and 34 are not overlapping with any other row at all.

Hence these are the only rows that I need to retain in my output set.

Hopefully this clarifies the situation a little and you might now be able to provide some pointers to me on that !

Thanks and best regards

Deepak

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2014-08-03 : 01:20:35

You can use cursor to get the result. Normally people hate cursor but with this requirement, I think cursor may be better solution over other.

You also can use recursive to get the result. If you want to use recursive for this and have problem, let me know.

Note: you should have table structure script AND insert command to generate testing data AND expected result. This is a great help for people who try to help you. I am sure lot people here can help you for this problem.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-03 : 12:56:46

[code]DECLARE @Sample TABLE
(
RowID INT NOT NULL,
NMI CHAR(10) NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL
);

INSERT @Sample
(
RowID,
NMI,
StartDate,
EndDate
)
VALUES (1, 'NCCC002321', '20131001', '20150930'),
(2, 'NCCC002321', '20140301', '20141031'),
(3, 'NCCC002321', '20140401', '20150331'),
(4, 'NCCC002321', '20140501', '20141231'),
(5, 'NCCC002321', '20140501', '20150430'),
(6, 'NCCC002321', '20140520', '20150519'),
(7, 'NCCC002321', '20140601', '20150531'),
(8, 'NCCC002321', '20140602', '20141231'),
(9, 'NCCC002321', '20140701', '20140930'),
(10, 'NCCC002321', '20140701', '20140930'),
(11, 'NCCC002321', '20140701', '20141231'),
(12, 'NCCC002321', '20140701', '20150630'),
(13, 'NCCC002321', '20140701', '20150630'),
(14, 'NCCC002321', '20140701', '20150630'),
(15, 'NCCC002321', '20140701', '20150630'),
(16, 'NCCC002321', '20140701', '20150630'),
(17, 'NCCC002321', '20140701', '20150630'),
(18, 'NCCC002321', '20140701', '20150630'),
(19, 'NCCC002321', '20140701', '20150630'),
(20, 'NCCC002321', '20140701', '20150630'),
(21, 'NCCC002321', '20140701', '20150630'),
(22, 'NCCC002321', '20140701', '20150930'),
(23, 'NCCC002321', '20140801', '20150731'),
(24, 'NCCC002321', '20141014', '20170930'),
(25, 'NCCC002321', '20141017', '20150930'),
(26, 'NCCC002321', '20141101', '20151031'),
(27, 'NCCC002321', '20141123', '20171031'),
(28, 'NCCC002321', '20141201', '20151130'),
(29, 'NCCC002321', '20150101', '20171231'),
(30, 'NCCC002321', '20150501', '20160430'),
(31, 'NCCC002321', '20150520', '20151231'),
(32, 'NCCC002321', '20150701', '20160630'),
(33, 'NCCC002321', '20150701', '20171231'),
(34, 'NCCC002321', '20151001', '20171231'),
(35, 'NCCC002321', '20160501', '20170430'),
(36, 'NCCC002321', '20160701', '20170630');

-- SwePeso
WITH cteSource(RowID, NMI, StartDate, EndDate)
AS (
SELECT RowID,
NMI,
StartDate,
EndDate
FROM (
SELECT RowID,
NMI,
StartDate,
EndDate,
ROW_NUMBER() OVER (PARTITION BY NMI ORDER BY RowID) AS rn
FROM @Sample
) AS d
WHERE rn = 1

UNION ALL

SELECT f.RowID,
f.NMI,
f.StartDate,
f.EndDate
FROM cteSource AS c
OUTER APPLY (
SELECT ROW_NUMBER() OVER (ORDER BY s.RowID) AS rn,
s.RowID,
s.NMI,
s.StartDate,
s.EndDate
FROM @Sample AS s
WHERE s.NMI = c.NMI
AND s.RowID > c.RowID
AND (s.StartDate > c.EndDate OR s.EndDate < c.StartDate)
) AS f
WHERE f.rn = 1
)
SELECT RowID,
NMI,
StartDate,
EndDate
FROM cteSource;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources