calculation query - SQL Server Forums

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

calculation query

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-11-20 : 04:30:21

hey guys

i am getting the following error message on my query

"Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '<'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'group'."

My query is as follows, any ideas why ?

select *
into #Accounts from FDMS.dbo.Dim_Outlet
where RM_Account = 'y'
create index idxacc on #accounts (fdmsaccountno)

select s.[FDMSAccountNo],
SUM (case when year(period) = '2011' then 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]s inner join fdms.dbo.Dim_Outlet o on o.FDMSAccountNo = s.FDMSAccountNo
where year(period) > 2010 and s.FDMSAccountNo in (select FDMSAccountNo from #Accounts)
group by s.FDMSAccountNo

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-20 : 04:48:36

[code]Select 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 MSC
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] s
inner join fdms.dbo.Dim_Outlet o on o.FDMSAccountNo = s.FDMSAccountNo
where year(period) > 2010 and s.FDMSAccountNo in (select FDMSAccountNo from #Accounts)
Group by s.FDMSAccountNo[/code]

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-11-20 : 04:57:32

hi sodeep

i get the following error message when using your logic

"Msg 174, Level 15, State 1, Line 3
The isnull function requires 2 argument(s).
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Group'.
"

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-20 : 05:00:10

remove that red marked paranthesis

WHEN merch_purch_fees > = 0 THEN ISNULL([Merch_Purch_Fees],0) + ISNULL([Per_Tran_Fees]),0)

--
Chandu

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-20 : 05:00:40

[code]Select *
into #Accounts from FDMS.dbo.Dim_Outlet
where RM_Account = 'y'

Create Index idxacc on #accounts (fdmsaccountno)

Select 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 MSC
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] s
inner join fdms.dbo.Dim_Outlet o on o.FDMSAccountNo = s.FDMSAccountNo
where year(period) > 2010 and s.FDMSAccountNo in (select FDMSAccountNo from #Accounts)
Group by s.FDMSAccountNo[/code][/code]

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-11-20 : 05:01:44

You guys are a *

i wish i could employ you to work for my organisation :)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-20 : 05:02:06

quote:
Originally posted by masond

You guys are a *

i wish i could employ you to work for my organisation :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources