| Author |
 Topic  |
|
|
masond
Posting Yak Master
241 Posts |
 Posted - 10/31/2012 : 07:46:17
|
Hey guys
I have a little snag and i am hoping you will be able to help me
my query is producing duplicate hst_merchnum and i dont know why
any solutions
Declare @date varchar(10)
set @Date = (select dateadd(MM,-11,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)
SELECT DISTINCT (hst_merchnum) as MerchantName,
[Standard],
[CP],
[Contactless],
[Secure eCom],
[TBA],
[CHIP],
[MOTO],
[Non Secure eCom],
ISNULL([Standard],0)
+ISNULL([CP],0)
+ISNULL([Secure eCom],0)
+ISNULL([TBA],0)
+ISNULL([CHIP],0)
+ISNULL([MOTO],0)
+ISNULL([Non Secure eCom],0) as 'Grand Total'
FROM
(SELECT distinct (hst_merchnum),
Fact_Financial_History.hst_date_processed,
Dim_Interchange_Tier_2.Qualification_2,
SUM(Fact_Financial_History.Net_Sales) AS [Sum Of Net Sales]
FROM Fact_Financial_History INNER JOIN Dim_Interchange_Tier_2 ON
Fact_Financial_History.hst_prod_code + '-' + Fact_Financial_History.hst_plan_code = Dim_Interchange_Tier_2.Plan_Code
WHERE
(Fact_Financial_History.hst_date_processed >= @date)
--and Dim_Interchange_Tier_2.Qualification_2 in ('Secure eCom','Non Secure eCom')
GROUP BY
Fact_Financial_History.hst_date_processed,
Dim_Interchange_Tier_2.Qualification_2,
hst_merchnum
) as p
PIVOT
( MAX( [Sum Of Net Sales]) FOR Qualification_2 IN ([Standard],
[CP],
[Contactless],
[Secure eCom],
[TBA],
[CHIP],
[MOTO],
[Non Secure eCom]) ) as pvt
|
Edited by - masond on 10/31/2012 09:01:01
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 10/31/2012 : 09:13:54
|
In the subquery you are grouping result set by 3 columns(Fact_Financial_History.hst_date_processed,
Dim_Interchange_Tier_2.Qualification_2,
hst_merchnum)
So you Obviously get combination of those 3 columns.
That is the reason you are getting duplicate values for hst_merchnum
--
Chandu |
Edited by - bandi on 10/31/2012 09:14:33 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 10/31/2012 : 09:24:49
|
if you want a solution you need to post us some sample data and then show output you're looking at
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
masond
Posting Yak Master
241 Posts |
Posted - 10/31/2012 : 09:38:27
|
HI guys
required results
I want a one liner, which gives me 12months of data for every hst_merchnum
For eg
MerchantName,[Standard],[CP],[Contactless],[Secure eCom],[TBA],[CHIP],[MOTO],[Non Secure eCom], Grand Total
test1 1000 2000 3000 4000 5000 6000 7000 8000 36000
test2 8000 7000 6000 5000 4000 3000 2000 1000 36000
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 10/31/2012 : 09:46:20
|
Try this once.......
FROM
(SELECT distinct (hst_merchnum)
,Fact_Financial_History.hst_date_processed,
,Dim_Interchange_Tier_2.Qualification_2,
SUM(Fact_Financial_History.Net_Sales) AS [Sum Of Net Sales]
FROM Fact_Financial_History INNER JOIN Dim_Interchange_Tier_2 ON
Fact_Financial_History.hst_prod_code + '-' + Fact_Financial_History.hst_plan_code = Dim_Interchange_Tier_2.Plan_Code
WHERE
(Fact_Financial_History.hst_date_processed >= @date)
--and Dim_Interchange_Tier_2.Qualification_2 in ('Secure eCom','Non Secure eCom')
GROUP BY
Fact_Financial_History.hst_date_processed,
Dim_Interchange_Tier_2.Qualification_2,
hst_merchnum
--
Chandu |
|
|
|
masond
Posting Yak Master
241 Posts |
Posted - 10/31/2012 : 10:34:03
|
Hi Bandi
Although i have put isnul into my query, the results are still producing a null, any ideas ? why that would be ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 10/31/2012 : 12:41:57
|
quote:
Originally posted by masond
Hi Bandi
Although i have put isnul into my query, the results are still producing a null, any ideas ? why that would be ?
you mean NULL values in PIVOT result? thats because it doesnt have a row with value specified in PIVOT. for that you've to add ISNULL check in final select list
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
removing duplicates
