compare lastyear vs. thisyear SQL
-
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.
-
Try this:
SELECT ISNULL(a.itemno,b.itemno) AS itemno,
ISNULL(a.qty2003, 0) AS qty2003,
ISNULL(a.sales2003, 0) AS sales2003,
ISNULL(b.qty2002, 0) AS qty2002,
ISNULL(b.sales2002, 0) AS sales2002,
ISNULL(a.sales2003, 0)-ISNULL(b.sales2002, 0) AS sales$difference,
ISNULL(a.sales$var,0) AS sales$var
FROM (
SELECT itemno, SUM(qty) AS qty2003, SUM(sales) AS sales2003,VAR(sales) AS sales$var
FROM #osh
WHERE (shipdate > '20030101' AND shipdate < '20031231')
GROUP BY itemno
) a full OUTER JOIN (
SELECT itemno, SUM(qty) AS qty2002, SUM(sales) AS sales2002
FROM #osh
WHERE (shipdate > '20020101' AND shipdate < '20021231')
GROUP BY itemno
) b on b.itemno=a.itemno
order BY ISNULL(a.itemno,b.itemno)
Hope this would help ...
-
want to add more tuning into it!!
better if u replace
(shipdate > '20030101' AND shipdate < '20031231') to year(shipdate ) = 2003
and
(shipdate > '20020101' AND shipdate < '20021231') to year(shipdate ) = 2002
My Blog:
http://dineshasanka.spaces.live.com/ -
Try this.
Select itemno, Qty2002,Qty2003,Sales2002,Sales2003, () Do some math) AS sales$difference, (do some math) AS sale$%var
FROM (SELECT TOP 100 PERCENT itemno, SUM(CASE WHEN YEAR(SHIPDATE)=2002 THEN qty ELSE 0 END) AS Qty2002,
SUM(CASE WHEN YEAR(SHIPDATE)=2003 THEN qty ELSE 0 END) AS Qty2003,
SUM(CASE WHEN YEAR(SHIPDATE)=2002 THEN sales ELSE 0 END) AS Sales2002,
SUM(CASE WHEN YEAR(SHIPDATE)=2003 THEN sales ELSE 0 END) AS Sales2003,
FROM [order-sub-head]
WHERE (shipdate > '20030101' AND shipdate < '20031231')
GROUP BY itemno
ORDER BY itemno) SalesFigures
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply