|
masond
Constraint Violating Yak Guru
447 Posts |
 Posted - 2013-02-14 : 09:44:14
|
| HI Guys I need some help, i have created the following pivot table however i am missing a critical piece of information I need an additional column which states “Transactions” . To work out the transactions there is a column called “[hst_sales_tran_R12]” in the[FDMS].[dbo].[Fact_Financial_History_2] table. pivot table code is SELECT DISTINCT (fdmsaccountno) as Fdmsaccountno,[ParentID],[Agent_Chain_No],[Corp_Chain_No],[Chain_Chain_No],external_Account_No,DBA_Name, Legal_Name,PCA,Open_Date,[Last_Post_Date],[First_Post_Date],MCC_Code,[Security Code],coalesce ([Standard],0)as Standard,coalesce ([CP],0)as CP,coalesce ( [Contactless],0)as Contactless,coalesce ([Secure eCom],0)as Secure_eCom,coalesce ([TBA],0)as TBA,coalesce ([CHIP],0)as CHIP,coalesce ([MOTO],0)as MOTO,coalesce ( [Non Secure eCom],0) as Non_Secure_eCom,+ISNULL([Standard],0)+ISNULL([CP],0)+ISNULL([Contactless],0) +ISNULL([Secure eCom],0)+ISNULL([TBA],0)+ISNULL([CHIP],0) +ISNULL([MOTO],0)+ISNULL([Non Secure eCom],0) as 'RollingSales'FROM(select [Fact_Financial_History_2].hst_merchnum,o.FDMSAccountNo,o.[ParentID],o.[Agent_Chain_No],o.[Corp_Chain_No],o.[Chain_Chain_No],o.external_Account_No,o.DBA_Name, o.Legal_Name,o.PCA,o.Open_Date,o.[Last_Post_Date],o.[First_Post_Date],stg_FDMS_Merchant_Control_Data.[Security Code],o.MCC_Code,Dim_Interchange_Tier_2.Qualification_2,SUM([Fact_Financial_History_2].hst_sales_amt_R12) AS [Sales]FROM Dim_Outlet o INNER JOIN Fact_Financial_History_2 ON o.FDMSAccountNo_First9 = Fact_Financial_History_2.hst_merchnumINNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History_2.Plan_Key = Dim_Interchange_Tier_2.Plan_Codeinner join stg_FDMS_Merchant_Control_Data on o.FDMSAccountNo = stg_FDMS_Merchant_Control_Data.FDMSAccountNogroup by Fact_Financial_History_2.hst_merchnum,o.FDMSAccountNo,Dim_Interchange_Tier_2.Qualification_2,o.[ParentID],o.[Agent_Chain_No],o.[Corp_Chain_No],o.[Chain_Chain_No],o.external_Account_No,o.DBA_Name, o.Legal_Name,o.PCA,o.Open_Date,o.[Last_Post_Date],o.[First_Post_Date],stg_FDMS_Merchant_Control_Data.[Security Code],o.MCC_Code )as ppivot ( MAX([Sales]) FOR Qualification_2 in ([Standard],[CP],[Contactless],[Secure eCom],[TBA],[CHIP],[MOTO],[Non Secure eCom])) as pvt |
|
Pivot Help