query Problem - 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

query Problem

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-11-21 : 06:22:09

Hey guys

I have a problem

can you tell me what the difference between these two queries are ? i only ask, as both queries producing different set of results

query 1

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.FDMSAccountNo
where year(period) > 2010
and s.FDMSAccountNo in (select FDMSAccountNo from #Accounts)
group by
s.FDMSAccountNo,
o.ParentID

query 2

SELECT m.[FDMSAccountNo],
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 MSCtest2011
into #MSC
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] m
where m.FDMSAccountNo in (select FDMSAccountNo from #Accounts)
and year(period) = '2011'
GROUP BY m.FDMSAccountNo

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-21 : 06:40:10

They seem like very different queries, the most obvious one being that in case one is grouped by o.ParentID and the other is not. Also, year = 2011 applies only to the first clause in the case expression in one case whereas it would apply to the entire query in the other case because of the year=2011 filter being in the where clause.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-11-21 : 06:49:05

Hi sunitabeck

I just want to work out the MSC For 2011
and group by the fdmsaccountno, what query do you think is best suited ?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-21 : 07:08:52

quote:
Originally posted by masond

I just want to work out the MSC For 2011 and group by the fdmsaccountno

Second case is more suitable for your requirement

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources