query Problem, - 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

query Problem,

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-19 : 09:36:21

Hey guys

sorry for posting so many times in the last few days, but the report i am building has really stumped me

I have a built a test pivot table which is below

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

select
[Fee_Sequence_Number] as fee_sequence_number,
[FDMSAccountNo] as fdmsaccountno,
[Retail_tran_count] as [Retail_tran_count],
[Month_end_date]
into #fee
from Fact_Fee_History
where [Month_end_date] >= @date
and [Fee_Sequence_Number] = '236'

drop table #fee,

SELECT
[FDMSAccountNo],
[1] AS Jan,
[2] AS Feb,
[3] AS Mar,
[4] AS Apr,
[5] AS May,
[6] AS June,
[7] AS July,

AS August,
[9] AS September,
[10] AS October,
[11] AS November,
[12] AS December
into #feesquence
FROM (Select
[FDMSAccountNo],
[Retail_tran_count],
MONTH([Month_end_date]) as TMonth from #fee ) source PIVOT (
count([Retail_tran_count]) FOR TMonth
IN ( [1], [2], [3], [4], [5], [6], [7],

, [9], [10], [11], [12] ) ) AS pvtMonth

Which works fine,however when i try and adapt the pivot to my query below, it just doesnt like it.

i know that for the query above to work, i need to reference from #accounts
and i need to reference where the table is comming from. For eg from Fact_Fee_History . However i dont know where to put that in the table and need your assistance

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')

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-19 : 09:50:26

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

SELECT *,
[1] AS Jan,
[2] AS Feb,
[3] AS Mar,
[4] AS Apr,
[5] AS May,
[6] AS June,
[7] AS July,

AS August,
[9] AS September,
[10] AS October,
[11] AS November,
[12] AS December
FROM (select ffh.[Fee_Sequence_Number] as fee_sequence_number,
ffh.[FDMSAccountNo] as fdmsaccountno,
ffh.[Retail_tran_count] as [Retail_tran_count],
ffh.[Month_end_date],
MONTH(ffh.[Month_end_date]) as TMonth
do.FDMSAccountNo_First9,
do.External_Account_No,
do.Legal_Name,
do.DBA_Name,
do.Account_Status,
do.MCC_Code,
do.Open_Date,
do.Cancel_Date
from Fact_Fee_History ffh
JOIN Dim_Outlet do ON do.FDMSAccountNo = ffh.[FDMSAccountNo]
Where MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
AND [Month_end_date] >= @date
and [Fee_Sequence_Number] = '236' ) as P
PIVOT
(
count([Retail_tran_count]) FOR TMonth
IN ( [1], [2], [3], [4], [5], [6], [7],

, [9], [10], [11], [12] )
) AS pvtMonth
[/code]

--
Chandu

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-19 : 09:56:15

hi bandi

the following query below is an essential table to all my other queries, so ideally i need to reference of that table

--- 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')

for eg the pivot will need to go alogn the lines off

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')

SELECT
[FDMSAccountNo],
[1] AS Jan,
[2] AS Feb,
[3] AS Mar,
[4] AS Apr,
[5] AS May,
[6] AS June,
[7] AS July,

AS August,
[9] AS September,
[10] AS October,
[11] AS November,
[12] AS December
into #feesquence
FROM
(Select
[FDMSAccountNo],
[Retail_tran_count]
from dbo.Fact_Fee_History
group by MONTH([Month_end_date]) as TMonth from #accounts ) source PIVOT (
and count([Retail_tran_count]) FOR TMonth
IN ( [1], [2], [3], [4], [5], [6], [7],

, [9], [10], [11], [12] ) ) AS pvtMonth

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-22 : 02:08:15

SELECT
[FDMSAccountNo],
[1] AS Jan,
[2] AS Feb,
[3] AS Mar,
[4] AS Apr,
[5] AS May,
[6] AS June,
[7] AS July,
AS August,
[9] AS September,
[10] AS October,
[11] AS November,
[12] AS December
into #feesquence
FROM (Select [FDMSAccountNo], [Retail_tran_count],MONTH([Month_end_date]) as TMonth
from #fee f JOIN #accounts a ON f.[FDMSAccountNo] = a.FDMSAccountNo) source PIVOT (
count([Retail_tran_count]) FOR TMonth
IN ( [1], [2], [3], [4], [5], [6], [7], , [9], [10], [11], [12] ) ) AS pvtMonth

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources