| Author |
 Topic  |
|
|
masond
Constraint Violating Yak Guru
256 Posts |
 Posted - 11/21/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/21/2012 : 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
256 Posts |
Posted - 11/21/2012 : 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
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 11/21/2012 : 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 |
Edited by - bandi on 11/21/2012 07:10:01 |
|
|
| |
Topic |
|
query Problem
