SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Checking if Date Ranges overlap
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rogerclerkwell
Yak Posting Veteran

United Kingdom
51 Posts

Posted - 11/06/2013 :  05:54:00  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 11/06/2013 :  06:37:44  Show Profile  Reply with Quote
(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

United Kingdom
51 Posts

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

rogerclerkwell
Yak Posting Veteran

United Kingdom
51 Posts

Posted - 11/06/2013 :  09:58:24  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 11/06/2013 :  10:25:51  Show Profile  Reply with Quote
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

United Kingdom
51 Posts

Posted - 11/06/2013 :  10:32:14  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000