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-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 MSCinto #DIFROM [FDMS].[dbo].[Fact_Omnipay_Profitability] dwhere (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 #DIFROM [FDMS].[dbo].[Fact_Omnipay_Profitability] dwhere (convert(datetime,Period,103) BETWEEN @Rolling12tempFROM and @Rolling12tempTO) and d.FDMSAccountNo in (select FDMSAccountNo from #Accounts)GROUP BY d.FDMSAccountNo |
|
|
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 DIfrom(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] dwhere (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] |
|
|
|
|
|
|
|