Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Min/Max values using order by Expand / Collapse
Posted Wednesday, November 6, 2013 9:48 AM


Group: General Forum Members
Last Login: Wednesday, April 22, 2015 5:06 AM
Points: 156, Visits: 876
Hi folks,

I have a product table that needs 4 columns updating using joins to 2 other tables.
Columns to be updated:
MaxPrice, MaxPriceDate, MinPrice, MinPriceDate

The joins are:
With tblPrice on product, [Month] and [Year]
With tblDiff on Product, [Month] and [Year] and Location

1. The tblPrice and tblDiff ‘Date’ columns must be between the tblProduct.startDate and tblProduct.EndDate range.
2. tblDiff.Date <= tblPrice.Date
3. the tblDiff.Date cannot be <= another tblPrice.Date in the tblProduct date range
4. If 2 values are the same, then the latest date should be used (DESC)

Something like:
tblProducts p1 JOIN tblPrice p2
ON p2.product = p1.Product
AND p2.[Month] = p1.[Month]
AND p2.[Year] = p1.[Year]
JOIN tblDiff d
ON d.Product = p1.Product
AND d.[Month] = p1.[Month]
AND d.[Year] = p1.[Year]
AND d.Location = p1.Location
AND d.Date <= p2.Date
p2.Date BETWEEN p1.StartDate AND p1.EndDate
AND d.Date BETWEEN p1.StartDate AND p1.EndDate

The tables are:

Product Month Year Location StartDate EndDate MaxPrice MaxPriceDate MinPrice MinPriceDate
ACBD 8 2013 Lon 01/11/2012 31/10/2013
ACBD 9 2013 Lon 01/11/2012 31/10/2013
ACBD 10 2013 Lon 01/11/2012 31/10/2013
EFGH 8 2013 Lon 01/11/2012 31/10/2013

Date Product Month Year Price
01/08/2013 ACBD 9 2013 180
31/07/2013 ACBD 9 2013 178
30/07/2013 ACBD 9 2013 167
29/07/2013 ACBD 9 2013 179
26/07/2013 ACBD 9 2013 180
25/07/2013 ACBD 9 2013 167
24/07/2013 ACBD 9 2013 175
23/07/2013 ACBD 9 2013 175

Date Product Month Year Location Price
01/08/2013 ACBD 9 2013 Lon 1
29/07/2013 ACBD 9 2013 Lon 2
28/07/2013 ACBD 9 2013 Lon 5
27/07/2013 ACBD 9 2013 Lon 5
24/07/2013 ACBD 9 2013 Lon 3

In my example, these are the results I am expecting where product = ABCD and month = 9 in tblProduct ( PriceDate should be taken as the Price.Date column value).

MaxPrice	MaxPriceDate	MinPrice	MinPriceDate
184 29/07/2013 169 30/07/2013

PriceDate	Price		
01/08/2013 181 180+1
31/07/2013 180 178+2
30/07/2013 169 167+2
29/07/2013 184 179+5
26/07/2013 183 180+3
25/07/2013 170 167+3
24/07/2013 178 175+3
23/07/2013 N/A

Any ideas how I can achieve this?
I am using a cursor and @temp table in my query but it doesn't provide the correct results.

Thanks in advance,
Post #1511931
Posted Thursday, November 7, 2013 9:55 PM

Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 3,976, Visits: 6,415
With 146 points it would seem you've been around long enough to know we need some help from you to help you, namely in the form of DDL and sample data.

CREATE TABLE #tblProducts
Product VARCHAR(10)
,[Month] INT
,[Year] INT
,Location VARCHAR(5)
,StartDate DATE
,EndDate DATE
,MaxPrice MONEY
,MaxPriceDate DATE
,MinPrice MONEY
,MinPriceDate DATE

INSERT INTO #tblProducts
(Product, [Month], [Year], Location, StartDate, EndDate)
SELECT 'ACBD',8,2013,'Lon','11/01/2012','10/31/2013'
UNION ALL SELECT 'ACBD',9,2013,'Lon','11/01/2012','10/31/2013'
UNION ALL SELECT 'ACBD',10,2013,'Lon','11/01/2012','10/31/2013'
UNION ALL SELECT 'EFGH',8,2013,'Lon','11/01/2012','10/31/2013';

I must be feeling charitable today, so perhaps this will get you close:

WITH tblPrice ([Date],Product,[Month],[Year],Price) AS
SELECT CAST('08/01/2013' AS DATE),'ACBD',9,2013,180
UNION ALL SELECT '07/31/2013','ACBD',9,2013,178
UNION ALL SELECT '07/30/2013','ACBD',9,2013,167
UNION ALL SELECT '07/29/2013','ACBD',9,2013,179
UNION ALL SELECT '07/26/2013','ACBD',9,2013,180
UNION ALL SELECT '07/25/2013','ACBD',9,2013,167
UNION ALL SELECT '07/24/2013','ACBD',9,2013,175
UNION ALL SELECT '07/23/2013','ACBD',9,2013,175
,tblDiff ([Date],Product,[Month],[Year],Location,Price) AS
SELECT CAST('08/01/2013' AS DATE),'ACBD',9,2013,'Lon',1
UNION ALL SELECT '07/29/2013','ACBD',9,2013,'Lon',2
UNION ALL SELECT '07/28/2013','ACBD',9,2013,'Lon',5
UNION ALL SELECT '07/27/2013','ACBD',9,2013,'Lon',5
UNION ALL SELECT '07/24/2013','ACBD',9,2013,'Lon',3
,Calendar AS
SELECT [Date]=DATEADD(day, n, (SELECT MIN([Date]) FROM tblPrice))
SELECT TOP (SELECT DATEDIFF(day, MIN([Date]), MAX([Date])) FROM tblPrice)
FROM sys.all_columns a
) a(n)
Prices AS
SELECT c.Product,c.[Month],c.[Year],c.Location
,Price=b.Price + c.Price
,rn1=ROW_NUMBER() OVER (PARTITION BY c.Product,c.[Month],c.[Year],c.Location ORDER BY b.Price + c.Price)
,rn2=ROW_NUMBER() OVER (PARTITION BY c.Product,c.[Month],c.[Year],c.Location ORDER BY b.Price + c.Price DESC)
FROM Calendar a
SELECT [Date],Product,[Month],[Year],Price
SELECT [Date],Product,[Month],[Year],Price
,rn=ROW_NUMBER() OVER (PARTITION BY Product,[Month],[Year] ORDER BY b.[Date] DESC)
FROM tblPrice b
) b
WHERE rn = 1 AND a.[Date] <= b.[Date]
) b
SELECT TOP 1 [Date],Product,[Month],[Year],Location,Price
FROM tblDiff c
WHERE c.[Date] <= a.[Date] AND
c.Product = b.Product AND c.[Month] = b.[Month] AND c.[Year] = b.[Year]
) c
SET MinPrice = b.MinPrice
,MinPriceDate = b.MinPriceDate
,MaxPrice = b.MaxPrice
,MaxPriceDate = b.MaxPriceDate
FROM #tblProducts a
SELECT Product,[Month],[Year],Location
,MinPrice=MAX(CASE WHEN rn1=1 THEN Price END)
,MinPriceDate=MAX(CASE WHEN rn1=1 THEN PriceDate END)
,MaxPrice=MAX(CASE WHEN rn2=1 THEN Price END)
,MaxPriceDate=MAX(CASE WHEN rn2=1 THEN PriceDate END)
FROM Prices
GROUP BY Product,[Month],[Year],Location
) b
ON a.Product = b.Product AND a.[Month] = b.[Month] AND a.[Year] = b.[Year] AND
a.Location = b.Location;

FROM #tblProducts;

DROP TABLE #tblProducts;

Note that I get something slightly different than your expected results, but that may be due to an inconsistency that seems to be in your expected results. Or possibly a minor error in my suggested query or data setup. Either way, it would be incumbent upon you to analyze my method (I believe it is sound) and see if you can make it work for you.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1512526
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse