CASE to ....

• b_boy

  Newbie

  Points: 0

  More actions

  September 25, 2008 at 9:44 am

  #193584

  Hello

  I am using the following query, of which I want to also get the sum total of order totals for each year as a column can anyone help?:

  select

  datepart(mm, t.orderdate) [Month], datepart(yy, t.orderdate) [Year],

  sum(t.ordertotal) Total,

  sum(case datepart(yy,t.orderdate) when 2006 then 1 else 0 end) as 'Orders 2006',

  sum(case datepart(yy,t.orderdate) when 2007 then 1 else 0 end) as 'Orders 2007',

  sum(case datepart(yy,t.orderdate) when 2008 then 1 else 0 end) as 'Orders 2008',

  ISNULL(sum(case datepart(yy,t.orderdate) when 2008 then 1. else 0. end) * 1.0 /

  NULLIF (sum(case datepart(yy,t.orderdate) when 2007 then 1. else 0. end)* 1.0, 0), 0) as 'Percent Change 07-08'

  from t_orderheader t

  where t.orderdate between getdate()-2920 and getdate()

  group by datepart(yy, t.orderdate),datename(mm, t.orderdate),datepart(mm, t.orderdate)

  order by datepart(yy, t.orderdate), datepart(mm, t.orderdate)

• b_boy

  Newbie

  Points: 0

  More actions

  September 25, 2008 at 10:43 am

  #877172

  Thanks Guys, but i figured it out with the following query:

  'SUM(CASE datepart(yy,t.orderdate) WHEN 2008 THEN t.ordertotal ELSE 0 END) AS '£ Orders in 2008'',