September 25, 2008 at 9:44 am
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)
September 25, 2008 at 10:43 am
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'',
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply