SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 pivot by months		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Posting Yak Master

241 Posts
Posted - 10/18/2012 :  11:48:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1430 Posts
Posted - 10/19/2012 :  02:05:17  Show Profile  Reply with Quote
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
Edited by - bandi on 10/19/2012 02:06:22
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000