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.
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 overlapas I can't have two prices on one day.For example if in a table there was:Product TROUSER Colour BLUEStart Date 01-NOV-13 End Date 20-NOV-13 Price £20.00Start Date 10-NOV-13 End Date 12-NOV-13 Price £18.00Start Date 21-NOV-13 End Date 25-NOV-13 Price £15.00The 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] aINNER JOIN [Product Prices] bON 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
rogerclerkwell
Yak Posting Veteran
85 Posts |
Posted - 2013-11-06 : 06:55:09
|
Thanks visakh16, I'll give it a go. |
|
|
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 BLUEStart Date 01-NOV-13 End Date 20-NOV-13 Price £20.00Start Date 10-NOV-13 End Date 12-NOV-13 Price £18.00Start Date 21-NOV-13 End Date 25-NOV-13 Price £15.00And a Stock Table with Dates inProduct TROUSER Colour BLUEDate 01-NOV-13 Qty 10Date 11-NOV-13 Qty 50If I doSELECT a.Product, a.Colour, a.StockQty * b.PriceFROM StockTable aLEFT OUTER JOIN PriceTable bON a.Product = b.ProductAND a.Colour = b.ColourAND a.Date BETWEEN b.StartDate AND b.EndDateI guess the 11-NOV will pull two lines because there are two lines found in price table so is best way around this todo a DENSE RANK and select highest rank or something or just MIN(Price).Shall I create a new post for this question? |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
|
|
|
|
|