Let's say I have tables that contains sales records:
Sales - Table
SalesID (int)
SalesDate (datetime)
TerminalID (int)
... other data ...
SalesDetails - Table
SalesDetailID (int)
SalesID (int)
ProductCodeID (int)
Qty (int)
... other data ...
Now, I would like to have a report that does something like this:
Terminal (based on Sales.TerminalID)
2007 Sales (based on SalesDetails.Qty, regardless of Product)
2008 Sales (based on SalesDetails.Qty, regardless of Product)
I've seen some do a stored proc that does something like this:
DECLARE @tempCompare TABLE (
TerminalID,
2007Data,
2008Data
)
INSERT INTO @tempCompare ('2007Data')
SELECT sum(SalesDetails.Qty) FROM SalesDetails JOIN Sales ON Sales.SalesID = SalesDetails.SalesID
WHERE Sales.SalesDate BETWEEN '1/1/2007' AND '1/1/2008'
UPDATE @tempCompare SET '2008Data' = data FROM @tempCompare
INNER JOIN (
SELECT sum(SalesDetails.Qty) FROM SalesDetails JOIN Sales ON Sales.SalesID = SalesDetails.SalesID
WHERE Sales.SalesDate BETWEEN '1/1/2008' AND '1/1/2009'
) 2008Sales ON @tempCompare.TerminalID = 2008Sales.TerminalID
Is this the best way to do this or is there a better way?
In actuall use, I'll be comparing not just quantity, but $$ amounts sold per year, so there will be several comparison columns.
