compare lastyear vs. thisyear SQL

• finance_teacher

  SSC Eights!

  Points: 855

  More actions

  February 11, 2004 at 9:15 pm

  #359104

  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.

• NPeeters

  SSChampion

  Points: 12536

  More actions

  February 12, 2004 at 4:29 am

  #493769

  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
  

• Antares686

  SSC Guru

  Points: 125444

  More actions

  February 12, 2004 at 6:17 am

  #493782

  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