SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 calculation query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/20/2012 :  04:30:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/20/2012 :  04:48:36  Show Profile  Reply with Quote
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
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/20/2012 :  04:57:32  Show Profile  Reply with Quote
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'.
"
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/20/2012 :  05:00:10  Show Profile  Reply with Quote
remove that red marked paranthesis

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

--
Chandu
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/20/2012 :  05:00:40  Show Profile  Reply with Quote
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
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 11/20/2012 :  05:01:44  Show Profile  Reply with Quote
You guys are a *

i wish i could employ you to work for my organisation :)
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/20/2012 :  05:02:06  Show Profile  Reply with Quote
quote:
Originally posted by masond

You guys are a *

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




:)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000