Pivot Help - 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 Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-12-11 : 07:09:22

Hey All

I know that i am very close to solving this problem, But i have hit a brick wall ?

Aim – Produce the months along the top, and put the sales of Fee_Sequence_Number = 18j in the relevant months

my query so far is

select DISTINCT FDMSAccountNo as FDMSAccountNo,
'2013-01-01' as [2013-01-01],
'2013-02-01' as [2013-02-01],
'2013-03-01' as [2013-03-01],
'2013-04-01' as [2013-04-01],
'2013-05-01' as [2013-05-01],
'2013-06-01' as [2013-06-01],
'2013-07-01' as [2013-07-01],
'2013-08-01' as [2013-08-01],
'2013-09-01' as [2013-09-01],
'2013-10-01' as [2013-10-01],
ISNULL([18j],0)
FROM
(SELECT FDMSAccountNo,
Fee_Sequence_Number,
sum([Retail_amount])as Auth_Sales
FROM [FDMS].[dbo].[Fact_Fee_History]
where FDMSAccountNo = 878714963880
and Month_end_date between '2013-01-01' and '2013-10-01'
group by FDMSAccountNo,Fee_Sequence_Number, Month_end_date
) as p
PIVOT (MAX([Auth_Sales]) FOR Fee_Sequence_Number in ( [18j])) as pvt

Where am i going wrong ?

Looking forward to your help

Daniel

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-11 : 11:27:36

try using this as a model - not sure if this is what you're looking for but it should help:


;with butt as
(
       select FDMSAccountNo = 878714963880, Fee_Sequence_Number = '18j', Retail_amount = 10, month_end_date = '2013-01-01' union all
       select FDMSAccountNo = 878714963880, Fee_Sequence_Number = '18j', Retail_amount = 11, month_end_date = '2013-02-01' union all
       select FDMSAccountNo = 878714963880, Fee_Sequence_Number = '18j', Retail_amount = 12, month_end_date = '2013-03-01'
)

select FDMSAccountNo,
       [2013-01-01],
       [2013-02-01],
       [2013-03-01],
       Fee_sequence_number
FROM   (
       SELECT FDMSAccountNo,
              Fee_Sequence_Number,
              Month_end_date,
              sum([Retail_amount])as Auth_Sales
       FROM butt
       where FDMSAccountNo = 878714963880
       and Month_end_date between '2013-01-01' and '2013-10-01'
       group by FDMSAccountNo,Fee_Sequence_Number, Month_end_date
       ) as p

PIVOT (MAX([Auth_Sales]) FOR Month_end_date in ( [2013-01-01],[2013-02-01],[2013-03-01])) as pvt


OUTPUT:

FDMSAccountNo                           2013-01-01  2013-02-01  2013-03-01  Fee_sequence_number
--------------------------------------- ----------- ----------- ----------- -------------------
878714963880                            10          11          12          18j

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-11 : 12:36:37

I think you might need a calendar table to use as the base to get all dates in a month as your columns. Otherwise you may see gaps based on whether data is missing for some of the days

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources