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,datep art(yy, date), datepart(mm, month), count(orders), sum(case when ordertype = 4 then subtotal*-1 else subtotal end) From ordertable Group By ordertype,datep art(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.
Select ordertype, case when ordertype = 1 then 'order' ...etc(for each order type) as orderdesc,datep art(yy, date), datepart(mm, month), count(orders), sum(case when ordertype = 4 then subtotal*-1 else subtotal end) From ordertable Group By ordertype,datep art(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.