|
masond
Posting Yak Master
241 Posts |
 Posted - 02/14/2013 : 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_merchnum
INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History_2.Plan_Key = Dim_Interchange_Tier_2.Plan_Code
inner join stg_FDMS_Merchant_Control_Data on o.FDMSAccountNo = stg_FDMS_Merchant_Control_Data.FDMSAccountNo
group 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 p
pivot
( MAX([Sales]) FOR Qualification_2 in
([Standard],[CP],[Contactless],[Secure eCom],[TBA],[CHIP],[MOTO],[Non Secure eCom])) as pvt |
|
Pivot Help
