February 11, 2004 at 9:15 pm
How can I modify the following so it will display the columns below ?
itemno, 2003qty, 2003sales, 2002qty, 2002sales, sales$difference, sale$%var
SELECT itemno, SUM(qty), SUM(sales)
FROM [order-sub-head]
WHERE (shipdate > '20030101' AND shipdate < '20031231')
GROUP BY itemno
ORDER BY itemno
Thanks . . . . .
Your help is greatly appreciated.
February 12, 2004 at 4:29 am
You have two possibilities :
1. Use the CASE construct in the SUM statements. Using it, you can say to only add the quantity or sales if the date is in range. A bit like the following
2. Use derived tables and joining them, like the following
SELECT M.ItemNo, D02.SumQty, D02.SumSales, D03.SumQty, D03.SumSales,
(D03.SumSales - D02.SumSales) as SalesDiff,
(D03.SumSales - D02.SumSales)/D03.SumSales as SalesDiffPct
FROM [order-sub-head] M
LEFT OUTER JOIN (SELECT ItemNo, Sum(Qty) as SumQty, Sum(Sales) as SumSales
FROM [order-sub-head]
WHERE <shipdate in range 2002>
GROUP BY ItemNo) D02
ON M.ItemNo = D02.ItemNo
LEFT OUTER JOIN (SELECT ItemNo, Sum(Qty) as SumQty, Sum(Sales) as SumSales
FROM [order-sub-head]
WHERE <shipdate in range 2003>
GROUP BY ItemNo) D03
ON M.ItemNo = D03.ItemNo
February 12, 2004 at 6:17 am
Another option
SELECT
itemno,
SUM((CASE WHEN YEAR(shipdate) = 2003 THEN qty ELSE 0 END)) [2003qty],
SUM((CASE WHEN YEAR(shipdate) = 2003 THEN sales ELSE 0 END)) [2003sales],
SUM((CASE WHEN YEAR(shipdate) = 2002 THEN qty ELSE 0 END)) [2002qty],
SUM((CASE WHEN YEAR(shipdate) = 2002 THEN sales ELSE 0 END)) [2002sales],
(SUM((CASE WHEN YEAR(shipdate) = 2003 THEN sales ELSE 0 END)) - SUM((CASE WHEN YEAR(shipdate) = 2002 THEN sales ELSE 0 END))) SalesDiff
--,VAR(sales)*100 SalesVariance -- Wasn't sure what you mean for your case about variance.
FROM
dbo.[order_sub_head]
WHERE
(shipdate BETWEEN '20030101' AND '20031231') OR
(shipdate BETWEEN '20020101' AND '20021231') -- Yes this could have been part of the above line but I was going for being able to compare non-contig years.
GROUP BY
itemno
ORDER BY
itemno
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply