How to identify OVERLAPPING records
-
I have a table whose dates are represented by a bigint rather than a datetime. e.g. 12/23/2015 is represented by 20151223. How would I identify which item numbers had overlapping dates?
Create table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OverlapTest](
[ItemNo] [varchar](15) NOT NULL,
[Start_Dt] [bigint] NULL,
[End_Dt] [bigint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
The data
Insert into OverlapTest
(ItemNo, Start_Dt, End_Dt)
Select 'A',20150101,20151231
Union all
Select 'A',20140101,20151231
Union all
Select 'B',20150101,20151231
union all
Select 'B',20150101,20151231
union all
Select 'A',20150701,20161231
Only item A has an overlapping record. How would I write a query to identify item A?
Thanks,
pat
-
mpdillon (12/23/2015)
I have a table whose dates are represented by a bigint rather than a datetime. e.g. 12/23/2015 is represented by 20151223. How would I identify which item numbers had overlapping dates?Create table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OverlapTest](
[ItemNo] [varchar](15) NOT NULL,
[Start_Dt] [bigint] NULL,
[End_Dt] [bigint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
The data
Insert into OverlapTest
(ItemNo, Start_Dt, End_Dt)
Select 'A',20150101,20151231
Union all
Select 'A',20140101,20151231
Union all
Select 'B',20150101,20151231
union all
Select 'B',20150101,20151231
union all
Select 'A',20150701,20161231
Only item A has an overlapping record. How would I write a query to identify item A?
Thanks,
pat
Define overlapping. Looking at the sample data both rows for item B overlap. When you say identify A, what do you mean? What result set are you looking for from your query?
-
Here's one way of doing it. It may or may not be the most efficient. Create a table of dates with one row for each date between the earliest possible and the latest possible. You can then join to that table to get every single date in the range specified in each row of your source table. Then, if you have the same combination of ItemNo and date more than once, you know you've got an overlap.
John
-
Lynn,
Ooops. Sorry. You are correct. Item B should have two date ranges that do NOT overlap each other.
Insert into OverlapTest
(ItemNo, Start_Dt, End_Dt)
Select 'A',20150101,20151231
Union all
Select 'A',20140101,20151231
Union all
Select 'B',20150101,20151231
union all
Select 'B',20140101,20141231
union all
Select 'A',20150701,20161231
With the new data, Item B does not have any dates that overlap. Only item A has dates that overlap between 07/01/2015 and 12/31/2015. In the code above that would be records 1 and 5.
I am looking for a result containing the item number that has the overlapping records.
-
John,
Yes. I agree that would work. I was hoping to find a query solution. I was thinking that if the table were Ordered by item number and Start date, that the addition of a Row number would allow me to compare dates to the next record in the same item number group using a join as you suggested. But I am out of my depth here.
-
I suppose you could create the table on the fly if you don't want a permanent one. This is a common technique. It would be a bit fiddly, though, considering the nature of your date values.
John
-
First let me offer an apology to everyone who has tried to use the data I supplied. it was bad, even the second set. Sorry.
Insert into OverlapTest
(ItemNo, Start_Dt, End_Dt)
Select 'A',20150101,20151231
Union all
Select 'A',20140101,20141231
Union all
Select 'B',20150101,20151231
union all
Select 'B',20140101,20141231
union all
Select 'A',20150701,20161231
I found the answer in the Row_Number function.
Start by assigning Row Numbers
Select ROW_NUMBER() OVER(PARTITION BY ItemNo ORDER BY Start_Dt DESC) AS RowNo,ItemNo, Start_Dt, End_Dt From OverlapTest
Next construct a CTE which compares the next row in the CTE to the current row. Look for next row Start dates that are less than the END date of the current record.
with mstrTable (RowNo, ItemNo, Start_Dt,End_Dt)
as (
Select ROW_NUMBER() OVER(PARTITION BY ItemNo ORDER BY Start_Dt DESC) AS RowNo,ItemNo, Start_Dt, End_Dt From OverlapTest
)
Select M.ItemNo, M.Start_Dt, M.End_Dt, N.Start_Dt from mstrTable M
Left outer Join mstrTable N
On M.Itemno = N.Itemno and M.RowNo = N.RowNo+1
where N.Start_Dt < = M.End_Dt
Order by M.itemno, M.Start_Dt
-
The query should be much simpler:
SELECT * FROM OverlapTest T1INNER JOIN OverlapTest T2 ON T2.ItemNo = T1.ItemNo
WHERE T1.Start_Dt < T2.Start_Dt
AND T2.Start_Dt < T1.End_Dt
ORDER BY T1.Start_Dt, T2.Start_Dt
And it identifies overlapping in all records for the same Item, not only in 2 consecutive ones.
_____________
Code for TallyGenerator -
SS,
Thank you for your efforts. So that other who read this will have a better understanding without having to recreate the tables and queries, I have added a Identity field to the table and a few more records.
New table spec
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OverlapTest](
[IDNo] [int] IDENTITY(1,1) NOT NULL,
[ItemNo] [varchar](15) NOT NULL,
[Start_Dt] [bigint] NULL,
[End_Dt] [bigint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
A few more conflicting records (Please delete the original records)
Insert into OverlapTest
(ItemNo, Start_Dt, End_Dt)
Select 'A',20150101,20151231
Union all
Select 'A',20140101,20141231
Union all
Select 'B',20150101,20151231
union all
Select 'B',20140101,20141231
union all
Select 'A',20150701,20161231
UNION ALL
Select 'A',20150801,20161231
UNION ALL
Select 'A',20150701,20151231
Finally our two queries.
with mstrTable (RowNo, ItemNo, Start_Dt,End_Dt, IDNo)
as (
Select ROW_NUMBER() OVER(PARTITION BY ItemNo ORDER BY Start_Dt DESC) AS RowNo,ItemNo, Start_Dt, End_Dt, IDNo From OverlapTest
)
Select M.ItemNo, M.Start_Dt, M.End_Dt, N.Start_Dt, N.End_Dt, M.IDNo, N.IDNo from mstrTable M
Left outer Join mstrTable N
On M.Itemno = N.Itemno and M.RowNo = N.RowNo+1
where N.Start_Dt < = M.End_Dt
Order by M.itemno, M.Start_Dt
SELECT T1.ITemNo,T1.IDNo as T1IDNo, T1.Start_Dt as T1StartDt, T1.End_Dt as T1EndDt, T2.IDNo, T2.Start_Dt as T2StartDt, T2.End_Dt as T2EndDt FROM OverlapTest T1
INNER JOIN OverlapTest T2 ON T2.ItemNo = T1.ItemNo
WHERE T1.Start_Dt < T2.Start_Dt
AND T2.Start_Dt < T1.End_Dt
ORDER BY T1.Start_Dt, T2.Start_Dt
The results can be seen in the image. Not sure if the image will appear. It did not appear with preview.
-
mpdillon (12/23/2015)
John,Yes. I agree that would work. I was hoping to find a query solution. I was thinking that if the table were Ordered by item number and Start date, that the addition of a Row number would allow me to compare dates to the next record in the same item number group using a join as you suggested. But I am out of my depth here.
How many rows do you need to check for overlaps?
--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)
Intro to Tally Tables and Functions -
mpdillon (12/23/2015)
SS,Thank you for your efforts. So that other who read this will have a better understanding without having to recreate the tables and queries, I have added a Identity field to the table and a few more records.
New table spec
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OverlapTest](
[IDNo] [int] IDENTITY(1,1) NOT NULL,
[ItemNo] [varchar](15) NOT NULL,
[Start_Dt] [bigint] NULL,
[End_Dt] [bigint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
A few more conflicting records (Please delete the original records)
Insert into OverlapTest
(ItemNo, Start_Dt, End_Dt)
Select 'A',20150101,20151231
Union all
Select 'A',20140101,20141231
Union all
Select 'B',20150101,20151231
union all
Select 'B',20140101,20141231
union all
Select 'A',20150701,20161231
UNION ALL
Select 'A',20150801,20161231
UNION ALL
Select 'A',20150701,20151231
Finally our two queries.
with mstrTable (RowNo, ItemNo, Start_Dt,End_Dt, IDNo)
as (
Select ROW_NUMBER() OVER(PARTITION BY ItemNo ORDER BY Start_Dt DESC) AS RowNo,ItemNo, Start_Dt, End_Dt, IDNo From OverlapTest
)
Select M.ItemNo, M.Start_Dt, M.End_Dt, N.Start_Dt, N.End_Dt, M.IDNo, N.IDNo from mstrTable M
Left outer Join mstrTable N
On M.Itemno = N.Itemno and M.RowNo = N.RowNo+1
where N.Start_Dt < = M.End_Dt
Order by M.itemno, M.Start_Dt
SELECT T1.ITemNo,T1.IDNo as T1IDNo, T1.Start_Dt as T1StartDt, T1.End_Dt as T1EndDt, T2.IDNo, T2.Start_Dt as T2StartDt, T2.End_Dt as T2EndDt FROM OverlapTest T1
INNER JOIN OverlapTest T2 ON T2.ItemNo = T1.ItemNo
WHERE T1.Start_Dt < T2.Start_Dt
AND T2.Start_Dt < T1.End_Dt
ORDER BY T1.Start_Dt, T2.Start_Dt
The results can be seen in the image. Not sure if the image will appear. It did not appear with preview.
Just a quick note: depending on your desired results, the comparisons in Sergiy's example might need to be changed to <=, instead of just <.
For example, if you want the row for item A from 20150701-20161231 to show as overlapping with item A's 20150801-20161231, you would need to make that change.
Also, with that definition, since a period of time overlaps with itself, you'd want to also change the join criteria to make sure that the IDNo is different between T1 and T2.
Cheers!
-
Jeff Moden (12/23/2015)
mpdillon (12/23/2015)
John,Yes. I agree that would work. I was hoping to find a query solution. I was thinking that if the table were Ordered by item number and Start date, that the addition of a Row number would allow me to compare dates to the next record in the same item number group using a join as you suggested. But I am out of my depth here.
How many rows do you need to check for overlaps?
Seriously... how many rows are we talking about? I'm trying to setup a volume of test data to test some of the solutions we've seen on this thread for performance and, perhaps, provide one of my own. I need to test for what you expect.
--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)
Intro to Tally Tables and Functions
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply