pivot by months - SQL Server Forums

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

pivot by months

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-18 : 11:48:15

Hey Guys

i need some pivot help

what i am trying to achieve is

currently the query produces 4 columns

Fdmsaccountno
Fee sequence number
chargeback amount
chargeback count

I would like to pivot the data so that the charge backcount is broken up via months through the month end date

for eg
fdmsaccountno, Chargeback amount , Sept, July , august

my query is as follows

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

SELECT
Fact_Fee_History.FDMSAccountNo,
--Fact_Financial_History.hst_merchnum,
Fact_Fee_History.Fee_Sequence_Number,
sum (Fact_Fee_History.Retail_amount) as chargeback_amount,
count (Fact_Fee_History.Fee_Sequence_Number)as chargeback_count
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 >= @date)
and Dim_Outlet.MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
group by
Fact_Fee_History.FDMSAccountNo,
--hst_merchnum,
Fee_Sequence_Number

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-19 : 02:05:17

Try this once


SELECT FDMSAccountNo
	,Fee_Sequence_Number
	,chargeback_amount
	,[January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December]
FROM (SELECT DISTINCT Fact_Fee_History.FDMSAccountNo,
			--Fact_Financial_History.hst_merchnum, 
			Fact_Fee_History.Fee_Sequence_Number,
			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 Dim_Outlet INNER JOIN
			Fact_Fee_History ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History.FDMSAccountNo
	   WHERE Fee_Sequence_Number = '236'
			and (Month_end_date >= @date)
			and Dim_Outlet.MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
	  ) AS p
         PIVOT 
	(COUNT(Fee_Sequence_Number) FOR Months IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December]))AS pvt

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources