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
 General SQL Server Forums
 New to SQL Server Programming
 Checking if Date Ranges overlap

Author  Topic 

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-11-06 : 05:54:00
Hi,

Just want to check if my query is the standard way to check if date ranges overlap in a price table as I need to check any that overlap
as I can't have two prices on one day.

For example if in a table there was:

Product TROUSER Colour BLUE
Start Date 01-NOV-13 End Date 20-NOV-13 Price £20.00
Start Date 10-NOV-13 End Date 12-NOV-13 Price £18.00
Start Date 21-NOV-13 End Date 25-NOV-13 Price £15.00

The top two overlap.

I'm doing this which is giving me nothing returned which I'm hoping means I have no overlapping date ranges:

SELECT a.[PriceList]
,a.[ProductID]
,a.[Colour]
,a.[Start Date]
,a.[End Date]
,a.[Product Price]

FROM [Product Prices] a

INNER JOIN [Product Prices] b

ON a.[PriceList] = b.[PriceList]
AND a.[ProductID] = b.[ProductID]
AND a.[Colour] = b.[Colour]

AND b.[Start Date] > a.[Start Date]
AND b.[End Date] < a.[End Date]

Is this the standard sql way to do this?

Thanks,

Roger

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-06 : 06:37:44
(b.[Start Date] BETWEEN a.[Start Date] AND a.[End Date])
OR (b.[End Date] BETWEEN a.[Start Date] AND a.[End Date])

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-11-06 : 06:55:09
Thanks visakh16, I'll give it a go.
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-11-06 : 09:58:24
Thanks Visakh16 thanks codes works perfectly.

Just been thinking I probably need to post this as a new post but instead of checking for dates overlapping and removing them from my price list, maybe I can just filter them in my query.

Like above, if I have a price table with:

Product TROUSER Colour BLUE
Start Date 01-NOV-13 End Date 20-NOV-13 Price £20.00
Start Date 10-NOV-13 End Date 12-NOV-13 Price £18.00
Start Date 21-NOV-13 End Date 25-NOV-13 Price £15.00

And a Stock Table with Dates in

Product TROUSER Colour BLUE
Date 01-NOV-13 Qty 10
Date 11-NOV-13 Qty 50

If I do

SELECT a.Product, a.Colour, a.StockQty * b.Price
FROM StockTable a

LEFT OUTER JOIN PriceTable b
ON a.Product = b.Product
AND a.Colour = b.Colour
AND a.Date BETWEEN b.StartDate AND b.EndDate

I guess the 11-NOV will pull two lines because there are two lines found in price table so is best way around this to
do a DENSE RANK and select highest rank or something or just MIN(Price).

Shall I create a new post for this question?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-06 : 10:25:51
It really depends on your requirement. You can take min price if what you're looking at is lowest price.
But in any case price table will not have duplicate prices for same overlapping dates.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-11-06 : 10:32:14
Yes I see what you mean, I guess I could use MIN Price although feels like I'm doing a bit of a bodge job and I'm not 100% if the second price row will always be the MIN price...

To be honest I haven't worked it out in my head fully yet but I guess I'm thinking of some how ranking with a new column on price table to show where two rows overlap then make first row number 1,
next 2, next 3 etc so I could then code to say always take highest rank number so I get always get the last row in the table and don't double figures... but this could be out of my basic sql knowledge at the moment, just looking at RANKING on net at the moment.
Go to Top of Page
   

- Advertisement -