November 4, 2010 at 4:26 pm
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.
November 4, 2010 at 5:10 pm
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
November 4, 2010 at 5:11 pm
what is your expected output based on your sample data?
November 4, 2010 at 6:06 pm
ARATES
BRATES
CRATES
always get a backup before you try that.
November 4, 2010 at 6:21 pm
Stephen Harris-233385 (11/4/2010)
ARATESBRATES
CRATES
A simple SELECT DISTINCT would return your required result...
We'd need a little more info what you define as "overlapping".
November 4, 2010 at 6:29 pm
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.
November 4, 2010 at 6:39 pm
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 infonamemake 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?)
November 4, 2010 at 7:09 pm
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.
November 4, 2010 at 11:43 pm
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"
November 5, 2010 at 8:36 am
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.
November 5, 2010 at 11:13 am
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
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy