November 6, 2013 at 9:48 am
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
ProductMonthYearLocationStartDateEndDateMaxPriceMaxPriceDateMinPriceMinPriceDate
ACBD82013Lon01/11/201231/10/2013
ACBD92013Lon01/11/201231/10/2013
ACBD102013Lon01/11/201231/10/2013
EFGH82013Lon01/11/201231/10/2013
tblPrice
DateProductMonthYearPrice
01/08/2013ACBD92013180
31/07/2013ACBD92013178
30/07/2013ACBD92013167
29/07/2013ACBD92013179
26/07/2013ACBD92013180
25/07/2013ACBD92013167
24/07/2013ACBD92013175
23/07/2013ACBD92013175
tblDiff
DateProductMonthYearLocationPrice
01/08/2013ACBD92013Lon1
29/07/2013ACBD92013Lon2
28/07/2013ACBD92013Lon5
27/07/2013ACBD92013Lon5
24/07/2013ACBD92013Lon3
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).
MaxPriceMaxPriceDateMinPriceMinPriceDate
18429/07/201316930/07/2013
Where
PriceDatePrice
01/08/2013181180+1
31/07/2013180178+2
30/07/2013169167+2
29/07/2013184179+5
26/07/2013183180+3
25/07/2013170167+3
24/07/2013178175+3
23/07/2013N/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,
November 7, 2013 at 9:55 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy