Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
masond
Constraint Violating Yak Guru
447 Posts |
 Posted - 2012-11-20 : 08:58:43
|
| Hello once again I am having problems with my sql queryI have worked out a calculation which is correct, however from the table i am referencing from, all the figures have a - in front of them Consequently when i am trying to a subtraction on this, my figures are incorrect ( as it sees that two - - = a positive )Is there any way i can change this – within my case statement below ? My query is select o.ParentID,s.[FDMSAccountNo],SUM(Case WHEN year(period) = '2011' and merch_purch_fees < 0 THEN [Merch_Purch_Fees] WHEN [Merch_Purch_Fees] > = 0 THEN ISNULL([Merch_Purch_Fees],0) + ISNULL([Per_Tran_Fees],0) END) AS [MSC2011],FROM [FDMS].[dbo].[Fact_Omnipay_Profitability]s inner join fdms.dbo.Dim_Outlet o on o.FDMSAccountNo = s.FDMSAccountNowhere year(period) > 2010 and s.FDMSAccountNo in (select FDMSAccountNo from #Accounts)group by s.FDMSAccountNo,o.ParentID |
|
|
hoffman
Starting Member
9 Posts |
Posted - 2012-11-20 : 09:10:02
|
| Hi masond,You can turn negative values into positive ones by multiplying them by -1.Case WHEN year(period) = '2011' and merch_purch_fees < 0 THEN [Merch_Purch_Fees]*(-1) WHEN [Merch_Purch_Fees] > = 0 THEN ISNULL([Merch_Purch_Fees],0) + ISNULL([Per_Tran_Fees],0) END |
 |
|
|
|
|
|
Subtraction issues