Got an issue with the sum function and an expected return value. Using SQL Server 2005.
Select ordertype, case when ordertype = 1 then 'order' ...etc(for each order type) as orderdesc,datepart(yy, date), datepart(mm, month), count(orders), sum(case when ordertype = 4 then subtotal*-1 else subtotal end) From ordertable Group By ordertype,datepart(yy, date), datepart(mm, month)
All the values in the subtotal column are positive, I use the case statement to treat a return order type as negative.
The issue I'm having is that I'm exactly off by the sum of the returns negatively. What I expect to return is the sum of the open orders minus the sum of the open returns. I'm also looking at this by month and year using datepart funtions.