|
masond
Posting Yak Master
241 Posts |
 Posted - 02/25/2013 : 04:42:28
|
HI Guys
i need some help
I would like to add the case statement (below) into my pivot table. Would appreciate any help available
SELECT
sum(case when Dim_Interchange_Tier_2.Scheme = 'visa' then 1 else 0 end) as visa,
sum(case when Dim_Interchange_Tier_2.Scheme = 'mastercard' then 1 else 0 end) as mastercard
FROM Fact_Financial_History_2 INNER JOIN
Dim_Interchange_Tier_2 ON Fact_Financial_History_2.Plan_Key = Dim_Interchange_Tier_2.Plan_Code
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 |
|
including case statements on a Pivot
