Can you do divsion on a pivot ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can you do divsion on a pivot ?

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-22 : 08:09:44

Hey guys

I need some help

i am trying to work out a division within my sql query

the calculation would be the following below

Standard,
CP,
Contactless,
Secure_eCom,
TBA,
CHIP,
MOTO,
Non_Secure_eCom,

Divided by the grand total coloumn

for eg

Standard, Grand total Standard %
2600 19000 13%

My query is as follows

--- Account Build ---

SELECT
Dim_Outlet.FDMSAccountNo_First9,
Dim_Outlet.FDMSAccountNo,
Dim_Outlet.External_Account_No,
Dim_Outlet.Legal_Name,
Dim_Outlet.DBA_Name,
Dim_Outlet.Account_Status,
Dim_Outlet.MCC_Code,
dim_outlet.Open_Date,
dim_outlet.Cancel_Date
into #accounts
FROM Dim_Outlet
Where MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')

-- Quarter Build--

Declare @dateQ varchar(10)
set @dateQ = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)

SELECT
a.FDMSAccountNo_First9,
--A.FDMSAccountNo,
SUM(hst_sales_amt) as Sales_Quarter
into #Quarter
FROM #accounts A INNER JOIN
Fact_Financial_History ON LEFT(a.fdmsaccountno,9) = Fact_Financial_History.hst_merchnum
and (dbo.Fact_Financial_History.hst_date_processed >= @dateQ)
group by a.FDMSAccountNo_First9

---------Rolling 12 ----

Declare @Rolling12tempFROM varchar(10)
Declare @Rolling12tempTO varchar(10)
set @Rolling12tempFROM = (select dateadd(MM,-11,max(hst_date_processed))
from dbo.Fact_Financial_History)
set @Rolling12tempTO = (select (max(hst_date_processed))
from dbo.Fact_Financial_History)

SELECT
a.FDMSAccountNo_First9,
--A.FDMSAccountNo,
SUM(hst_sales_amt) as Sales_R12
into #R12
FROM #accounts A INNER JOIN
Fact_Financial_History ON LEFT(a.fdmsaccountno,9) = Fact_Financial_History.hst_merchnum
and ( hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
group by a.FDMSAccountNo_First9

---------Rolling 18 ----

Declare @Rolling18tempFROM varchar(10)
Declare @Rolling18tempTO varchar(10)
set @Rolling18tempFROM = (select dateadd(MM,-17,max(hst_date_processed))
from dbo.Fact_Financial_History)
set @Rolling18tempTO = (select (max(hst_date_processed))
from dbo.Fact_Financial_History)

SELECT
a.FDMSAccountNo_First9,
--A.FDMSAccountNo,
SUM(hst_sales_amt) as Sales_R18
into #R18
FROM #accounts A INNER JOIN
Fact_Financial_History ON LEFT(a.fdmsaccountno,9) = Fact_Financial_History.hst_merchnum
and ( hst_date_processed BETWEEN @Rolling18tempFROM and @Rolling18tempTO)
group by a.FDMSAccountNo_First9

----- Interchange breakdown ----

SELECT DISTINCT coalesce (hst_merchnum,0) as Fdmsaccountno,
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 'Grand Total'
into #Interchange
FROM
(select Fact_Financial_History.hst_merchnum,
Dim_Interchange_Tier_2.Qualification_2,
SUM(Fact_Financial_History.hst_sales_amt) AS [sales]
FROM
Dim_Outlet INNER JOIN Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History.Plan_Key = Dim_Interchange_Tier_2.Plan_Code
Where Dim_Outlet.MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
and Dim_Interchange_Tier_2.Scheme =('mastercard')
--and (hst_date_processed >= @dateQ)
group by
Fact_Financial_History.hst_merchnum,
Dim_Interchange_Tier_2.Qualification_2

)as p
pivot
( MAX([Sales]) FOR Qualification_2 in
([Standard],[CP],[Contactless],[Secure eCom],[TBA],[CHIP],[MOTO],[Non Secure eCom])) as pvt

---Fee sequence trans count per month ---

SELECT
FDMSAccountNo
,[January]
,[February]
,[March]
,[April]
,[May]
,[June]
,[July]
,[August]
,[September]
,[October]
,[November]
,[December]
into #FeeSequenceTrans
FROM (SELECT DISTINCT
Retail_tran_count,
Fact_Fee_History.FDMSAccountNo,
--Fact_Fee_History.Fee_Sequence_Number,
--Fact_Financial_History.hst_merchnum,
--sum (Fact_Fee_History.Retail_amount) over(partition by Fact_Fee_History.FDMSAccountNo, Fee_Sequence_Number) as chargeback_amount,
DATENAME( Month, Month_end_date) Months
FROM dbo.Fact_Fee_History
--FROM Dim_Outlet INNER JOIN Fact_Fee_History ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History.FDMSAccountNo
WHERE Fee_Sequence_Number = '236'
and ( Month_end_date BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
) AS p
PIVOT
(COUNT(Retail_tran_count) FOR Months IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December]))AS pvt

----fee squence ----

SELECT
Fact_Fee_History.FDMSAccountNo,
sum (Fact_Fee_History.Retail_amount) as chargeback_amount
into #feesequence
FROM dbo.Fact_Fee_History
where Fee_Sequence_Number = '236'
and ( Month_end_date BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
group by
Fact_Fee_History.FDMSAccountNo

----Results -----

select
--a.FDMSAccountNo,
a.FDMSAccountNo_First9,
a.External_Account_No,
a.Legal_Name,
a.DBA_Name,
a.Account_Status,
a.MCC_Code,
a.Open_Date,
a.Cancel_Date,
q.Sales_Quarter,
r12.Sales_R12,
r18.Sales_R18,
#Interchange.Standard,
#Interchange.CP,
#Interchange.Contactless,
#Interchange.Secure_eCom,
#Interchange.Non_Secure_eCom,
#Interchange.TBA,
#Interchange.CHIP,
#Interchange.MOTO,
#Interchange.[Grand Total],
#FeeSequenceTrans.January,
#FeeSequenceTrans.February,
#FeeSequenceTrans.March,
#FeeSequenceTrans.April,
#FeeSequenceTrans.May,
#FeeSequenceTrans.June,
#FeeSequenceTrans.July,
#FeeSequenceTrans.August,
#FeeSequenceTrans.September,
#FeeSequenceTrans.October,
#FeeSequenceTrans.November,
#FeeSequenceTrans.December,
#feesequence.chargeback_amount
from #accounts a
full outer join #Quarter q on a.FDMSAccountNo_First9 = q.FDMSAccountNo_First9
full outer join #R12 r12 on a.FDMSAccountNo_First9 = r12.FDMSAccountNo_First9
full outer join #R18 r18 on a.FDMSAccountNo_First9 = r18.FDMSAccountNo_First9
full outer join #Interchange on a.FDMSAccountNo_First9 = #Interchange.Fdmsaccountno
left join #feesequence on a.FDMSAccountNo_First9 = left(#feesequence.FDMSAccountNo,9)
left join #FeeSequenceTrans on a.FDMSAccountNo = #FeeSequenceTrans.FDMSAccountNo

---Table Drops ---

drop table #Interchange,#Quarter,#R12,#R18,#accounts,#feesequence,#FeeSequenceTrans

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-22 : 08:17:06

You can add another column to final SELECT statement as follows:

#Interchange.Standard / #Interchange.[Grand Total] as 'Standard %'

And also one suggestion is:

full outer join #Interchange ic on a.FDMSAccountNo_First9 = ic.Fdmsaccountno
left join #feesequence fs on a.FDMSAccountNo_First9 = left(fs.FDMSAccountNo,9)
left join #FeeSequenceTrans fst on a.FDMSAccountNo = fst.FDMSAccountNo

--
Chandu

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-22 : 08:38:58

HI Bandi
I have done the following below,

but i get the following error msg

Msg 102, Level 15, State 1, Line 198
Incorrect syntax near '#Interchange'.

select
--a.FDMSAccountNo,
a.FDMSAccountNo_First9,
a.External_Account_No,
a.Legal_Name,
a.DBA_Name,
a.Account_Status,
a.MCC_Code,
a.Open_Date,
a.Cancel_Date,
q.Sales_Quarter,
r12.Sales_R12,
r18.Sales_R18,
#Interchange.Standard,
#Interchange.CP,
#Interchange.Contactless,
#Interchange.Secure_eCom,
#Interchange.Non_Secure_eCom,
#Interchange.TBA,
#Interchange.CHIP,
#Interchange.MOTO,
#Interchange.[Grand Total],

#Interchange.Standard / #interchange.[Grand Total] as 'Standard %'
#Interchange.CP / #interchange.[Grand Total] as 'CP %'
#Interchange.Contactless / #interchange.[Grand Total] as 'Contactless %'
#Interchange.Secure_eCom / #interchange.[Grand Total] as 'Secure_eCom %'
#Interchange.Non_Secure_eCom / #interchange.[Grand Total] as 'Non_Secure_eCom %'
#Interchange.TBA / #interchange.[Grand Total] as 'TBA %'
#Interchange.CHIP / #interchange.[Grand Total] as 'CHIP %'
#Interchange.MOTO / #interchange.[Grand Total] as 'MOTO %'

#FeeSequenceTrans.January,
#FeeSequenceTrans.February,
#FeeSequenceTrans.March,
#FeeSequenceTrans.April,
#FeeSequenceTrans.May,
#FeeSequenceTrans.June,
#FeeSequenceTrans.July,
#FeeSequenceTrans.August,
#FeeSequenceTrans.September,
#FeeSequenceTrans.October,
#FeeSequenceTrans.November,
#FeeSequenceTrans.December,
#feesequence.chargeback_amount
from #accounts a
full outer join #Quarter q on a.FDMSAccountNo_First9 = q.FDMSAccountNo_First9
full outer join #R12 r12 on a.FDMSAccountNo_First9 = r12.FDMSAccountNo_First9
full outer join #R18 r18 on a.FDMSAccountNo_First9 = r18.FDMSAccountNo_First9
full outer join #Interchange on a.FDMSAccountNo_First9 = #Interchange.Fdmsaccountno
left join #feesequence on a.FDMSAccountNo_First9 = left(#feesequence.FDMSAccountNo,9)
left join #FeeSequenceTrans on a.FDMSAccountNo = #FeeSequenceTrans.FDMSAccountNo

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-22 : 08:56:56

See sample code related to your requirement

create table #tab (col1 int, [col2] int)
Insert into #tab values(32,4),(12,3),(345,32)
SELECT (#tab.col1*100) / #tab.col2
frOM #tab

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-22 : 09:11:57

Hi,

Its a typo................

#Interchange.Standard / #interchange.[Grand Total] as 'Standard %',
#Interchange.CP / #interchange.[Grand Total] as 'CP %',
#Interchange.Contactless / #interchange.[Grand Total] as 'Contactless %',
#Interchange.Secure_eCom / #interchange.[Grand Total] as 'Secure_eCom %',
#Interchange.Non_Secure_eCom / #interchange.[Grand Total] as 'Non_Secure_eCom %',
#Interchange.TBA / #interchange.[Grand Total] as 'TBA %',
#Interchange.CHIP / #interchange.[Grand Total] as 'CHIP %',
#Interchange.MOTO / #interchange.[Grand Total] as 'MOTO %',

You forgot to put comma

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources