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-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.FDMSAccountNowhere year(period) > 2010 and s.FDMSAccountNo in (select FDMSAccountNo from #Accounts)group by s.FDMSAccountNo,o.ParentIDquery 2SELECT 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 MSCtest2011into #MSCFROM [FDMS].[dbo].[Fact_Omnipay_Profitability] mwhere 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 2011and 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 |
|
|
|
|
|