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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Case Statement subtract query

Author  Topic 

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-11-19 : 10:28:50
Hi guys ~


This is a really basic/stupid question to ask, but i am really struggling with it

I want to a produce a coloumn called DI. To work out the DI is is MSC - [Intchg_Assess]

would appreciate any help available

My query is

SELECT
d.[FDMSAccountNo],
d.[Intchg_Assess],
SUM(CASE WHEN merch_purch_fees < 0 THEN [Merch_Purch_Fees]
WHEN merch_purch_fees > = 0 THEN [Merch_Purch_Fees] + [Per_Tran_Fees] END) AS MSC
into #DI
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] d
where (convert(datetime,Period,103) BETWEEN @Rolling12tempFROM and @Rolling12tempTO) and d.FDMSAccountNo in (select FDMSAccountNo from #Accounts)
GROUP BY d.FDMSAccountNo

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-11-19 : 10:31:18
is it as simple as

SELECT
d.[FDMSAccountNo],
d.[Intchg_Assess],
SUM(CASE WHEN merch_purch_fees < 0 THEN [Merch_Purch_Fees] WHEN merch_purch_fees > = 0 THEN [Merch_Purch_Fees] + [Per_Tran_Fees] END) AS 'MSC',
Sum([MSC])-sum(-[Intchg_Assess]) as [DI]
into #DI
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] d
where (convert(datetime,Period,103) BETWEEN @Rolling12tempFROM and @Rolling12tempTO) and d.FDMSAccountNo in (select FDMSAccountNo from #Accounts)
GROUP BY d.FDMSAccountNo
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-19 : 13:00:08
[code]
SELECT [FDMSAccountNo],[Intchg_Assess],
ISNULL(MSC,0) - ISNULL([Intchg_Assess],0) as DI
from
(SELECT
d.[FDMSAccountNo],
d.[Intchg_Assess],
SUM(CASE WHEN merch_purch_fees < 0 THEN [Merch_Purch_Fees] WHEN merch_purch_fees > = 0 THEN [Merch_Purch_Fees] + [Per_Tran_Fees] END) AS 'MSC'
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] d
where (convert(datetime,Period,103) BETWEEN @Rolling12tempFROM and @Rolling12tempTO) and d.FDMSAccountNo in (select FDMSAccountNo from #Accounts)
GROUP BY d.FDMSAccountNo,d.[Intchg_Assess]
)G
[/code]
Go to Top of Page
   

- Advertisement -