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.

  • 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

    SELECT ItemNo, SUM(CASE WHEN <shipdate in range> THEN qty ELSE 0), ...

    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

  • 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