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
Author
Message
Posted Wednesday, November 6, 2013 9:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 23, 2014 2:53 PM
Points: 152, Visits: 854
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]
and
With tblDiff on Product, [Month] and [Year] and Location

Criteria:
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
WHERE
p2.Date BETWEEN p1.StartDate AND p1.EndDate
AND d.Date BETWEEN p1.StartDate AND p1.EndDate
ORDER BY d.Date DESC

The tables are:

tblProducts									
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



tblPrice
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



tblDiff
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

Where
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: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
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))
FROM
(
SELECT 0 UNION ALL
SELECT TOP (SELECT DATEDIFF(day, MIN([Date]), MAX([Date])) FROM tblPrice)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a
) a(n)
),
Prices AS
(
SELECT c.Product,c.[Month],c.[Year],c.Location
,PriceDate=a.[Date]
,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
OUTER APPLY
(
SELECT [Date],Product,[Month],[Year],Price
FROM
(
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
OUTER APPLY
(
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]
ORDER BY c.[Date] DESC
) c
)
UPDATE a
SET MinPrice = b.MinPrice
,MinPriceDate = b.MinPriceDate
,MaxPrice = b.MaxPrice
,MaxPriceDate = b.MaxPriceDate
FROM #tblProducts a
JOIN
(
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;

SELECT *
FROM #tblProducts;

GO
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!
Post #1512526
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse