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-10-31 : 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 solutionsDeclare @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_CodeWHERE (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 pPIVOT( MAX( [Sum Of Net Sales]) FOR Qualification_2 IN ([Standard],[CP],[Contactless],[Secure eCom],[TBA],[CHIP],[MOTO],[Non Secure eCom]) ) as pvt |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-10-31 : 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-31 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-10-31 : 09:38:27
|
HI guys required results I want a one liner, which gives me 12months of data for every hst_merchnumFor eg MerchantName,[Standard],[CP],[Contactless],[Secure eCom],[TBA],[CHIP],[MOTO],[Non Secure eCom], Grand Totaltest1 1000 2000 3000 4000 5000 6000 7000 8000 36000test2 8000 7000 6000 5000 4000 3000 2000 1000 36000 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-10-31 : 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_CodeWHERE (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
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-10-31 : 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
52326 Posts |
Posted - 2012-10-31 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|