need help find overlapping dates in a table

Question

Post reply

need help find overlapping dates in a table

Mountain Steve

Ten Centuries

Points: 1125
More actions
November 4, 2010 at 4:26 pm

#228606

we have a table we store rates in
we re-use the rate table names but use date ranges and primary keys to keep each row unique
we have problems (now resolved by the UI) where the dates overlapped
I need to go into sql and find overlapping rows and haven't been able to do so.
following is a very rudimentary example of the issue:
declare @overlap table
(
ID int identity,
infoname char(10),
effective smalldatetime,
termination smalldatetime
)
insert into @overlap
values ('ARATES','01/01/2000','12/31/2000')
insert into @overlap
values ('BRATES','01/01/2000','12/31/2000')
insert into @overlap
values ('CRATES','01/01/2000','12/31/2000')
insert into @overlap
values ('DRATES','01/01/2000','12/31/2000')
insert into @overlap
values ('ARATES','01/01/2000','12/31/2001')
insert into @overlap
values ('BRATES','12/31/2000','12/31/2001')
insert into @overlap
values ('CRATES','03/01/2000','12/01/2000')
insert into @overlap
values ('DRATES','01/01/2001','12/31/2002')
select * from @overlap
I need to write a query that would show any row where the infoname is a match but the primary key is different and the respective date ranges overlap. This is probably easy to do but I am vexed as to how to do it after a day of trying. I need help!
thanks y'all!
always get a backup before you try that.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Try the below SQL which results in:

ARATES 12000-01-01 2000-12-31 5 2000-01-012001-12-31

BRATES 22000-01-01 2000-12-31 6 2000-12-31 2001-12-31

s

select First.InfoName

,First.Id

,First.Effective

,First.termination

,Second.Id

,Second.Effective

,Second.termination

From @overlap as First

join @overlap as Second

on Second.Id > First.Id

and Second.InfoName = First.InfoName

and ( First.Effective between Second.Effective and Second.termination

or First.termination between Second.Effective and Second.termination

)

;

SQL = Scarcely Qualifies as a Language

LutzM SSC Guru Points: 107049 More actions · Answer 2

what is your expected output based on your sample data?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mountain Steve Ten Centuries Points: 1125 More actions · Answer 3

ARATES

BRATES

CRATES

always get a backup before you try that.

LutzM SSC Guru Points: 107049 More actions · Answer 4

Stephen Harris-233385 (11/4/2010)
ARATES
BRATES
CRATES

A simple SELECT DISTINCT would return your required result... 😀

We'd need a little more info what you define as "overlapping".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mountain Steve Ten Centuries Points: 1125 More actions · Answer 5

I should have said overlapping ranges. You can't have a date range of rates that overlap another date range of rates with the same infoname

make sense?

always get a backup before you try that.

LutzM SSC Guru Points: 107049 More actions · Answer 6

Stephen Harris-233385 (11/4/2010)
I should have said overlapping ranges. You can't have a date range of rates that overlap another date range of rates with the same infoname
make sense?

Not really:

CRATES don't overlap, rows are nested.

BRATES might overlap or not depending on the definition (effective col of one row =

termination col of another row -> overlap or not?)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mountain Steve Ten Centuries Points: 1125 More actions · Answer 7

how about no overlapping date ranges and no nested date ranges

so for example if I was looking for CRATES for 07/01/2001 and I wanted to select * from @overlap where infoname='CRATES' and '07/01/2001' between effective and termination I would get two rows when I only want one.

I am looking to find anything where that kind of select has the possibility to bring back more than one row.

I hope that makes sense.

always get a backup before you try that.

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 8

SELECTo.*

FROM@Overlap AS o

CROSS APPLY(

SELECT*

FROM@Overlap AS x

WHEREx.infoname = o.infoname

AND x.id <> o.id

AND x.effective <= o.termination

and x.termination >= o.effective

) AS f

N 56°04'39.16"
E 12°55'05.25"

Mountain Steve Ten Centuries Points: 1125 More actions · Answer 9

SwePeso knocked it out of the park. Good work. I learned something. I will certainly add cross apply to my tool kit and I am a huge fan of brief code. I had actually figured this out late last night but the SQL was klugey and used a cursor: blech!

always get a backup before you try that.

WayneS SSC Guru Points: 95409 More actions · Answer 10

If you need help in understanding the APPLY operator, check out that link in my signature. (That article links to another - both are excellent for understanding APPLY.)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2