Tricky Join Question
-
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://www.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 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply