Tricky Join Question
-
I need to calculate Cost of Goods sold for a particular item. Basically, I have 2 tables. One table stores the item sold by transaction date and the other stores a history of cost adjustments for an item. Keep in mind this a very simple example. We actually process several thousand transactions with any number of items sold per day.
Below is an example of the tables
-- Build CostHist table
Select
Item = 1, Cost = 1.00, CostDate = '1/1/2007'
Into
#CostHist
Union
All
Select
1, 1.50, '2/1/2007'
Union
All
Select
1, 1.00, '3/1/2007'
-- Build Sales table
Select
SaleID = 1, Item = 1, SaleDate = '1/2/2007'
Into
#Sales
Union
All
Select
2, 1, '1/4/2007'
Union
All
Select
3, 1, '2/10/2007'
Union
All
Select
4, 1, '3/15/2007'
Question: How much did item 1 cost for sale 3?
Desired answer: Item 1 cost 1.00 for sale 1.50
-
Please try the below given script
WITH CHDetails(Item, Cost, CostDateFrom, CostDateTo) AS
(
SELECT Item,
Cost,
CostDate,
ISNULL(
(SELECT MIN(CostDate)
FROM #CostHist AS ch
WHERE (CostDate > #CostHist.CostDate)
AND (Item = #CostHist.Item)),
CONVERT(varchar, GETDATE(), 103)) AS CostDateTo
FROM #CostHist
)
SELECT #Sales.SaleID,
#Sales.Item,
#Sales.SaleDate,
CostTable.Cost
FROM #Sales
INNER JOIN CHDetails AS CostTable
ON #Sales.Item = CostTable.Item
WHERE #Sales.SaleID = 3
AND #Sales.SaleDate
BETWEEN CostTable.CostDateFrom AND CostTable.CostDateTo
-
Thanks for the suggestion. It worked pretty well. I have a question about the use of CTEs. Does it only get the data requested through the join or does it run against the entire dataset?
-
CTE is a temporary view defined within the scope of an executing statement; CTEs are almost same as the derived tables
To know more about CTE, please read this article
http://qa.sqlservercentral.com/columnists/sSampath/commontableexpressionsinsqlserver2005.asp
-
Are you using SQL Server 2005? Your post is in the SQL Server 7,2000 T-SQL section....
Here's the cost w/o the CTE:
SELECT Cost
FROM #CostHist ch
INNER JOIN (
SELECT s.SaleID, s.Item, MAX(CostDate) AS MaxCostDate
FROM #CostHist ch
INNER JOIN #Sales s
ON ch.Item = s.Item AND ch.CostDate < s.SaleDate
WHERE s.SaleID = 3
GROUP BY s.SaleID, s.Item
) t
ON ch.Item = t.Item AND ch.CostDate = t.MaxCostDate
-
Sorry
, I didn’t notice that the post is in the SQL Server 7, 2000 T-SQL section.We can create the same script using a derived table
-
DECLARE @SaleId int
SET @SaleId=3
SELECT TOP 1 'Final answers is: '+ convert(varchar,C1.Cost)
FROM #CostHist C1,#CostHist C2
WHERE (C1.CostDate<>C2.CostDate)
AND
(
(SELECT SaleDate FROM #sales WHERE SaleId= @SaleId) BETWEEN C1.CostDate AND C2.CostDate
)
ORDER BY C1.CostDate desc,C2.CostDate desc
-- Run the above code and get the result
-
I appreciate everyone's input. I tried the CTE solution and it worked, but with 1 major flaw... It blew our TEMPDB to 50gb under a normal load (a few million records). I'm still searching for viable solution. I guess I should have explained in my example that we would be doing this in a batch process. Again, thanks for all your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply