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

FData
Starting Member

24 Posts

Posted - 2012-09-03 : 06:44:01

Hi guys

Can someone please explain to me, why i am receiving the following error message below

(64685 row(s) affected)

(85273 row(s) affected)

(5644507 row(s) affected)

(71378 row(s) affected)

(71378 row(s) affected)
Msg 207, Level 16, State 1, Line 91
Invalid column name 'hst_merchnum'.
Msg 207, Level 16, State 1, Line 91
Invalid column name 'Rolling12_MSC'.
Msg 207, Level 16, State 1, Line 91
Invalid column name 'Rolling12_PCI'.
Msg 207, Level 16, State 1, Line 91
Invalid column name 'RefundTransactionCharge_2012'.
Msg 207, Level 16, State 1, Line 91
Invalid column name 'Other_repeatable2012'.
Msg 207, Level 16, State 1, Line 91
Invalid column name 'Other_Non_repeatable2012'.

this is my query

-------------------------------- Sales ------------------------------

Declare @Rolling12 varchar(10)
set @Rolling12 = (select dateadd(MM,-12,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)
SELECT FDMSAccountNo,
sum([Net_Sales]) as [Net_Sales_Rolling12],
SUM ([Net_Intg]) as [Net_Intg_Rolling12],
SUM ([Scheme_Fees]) as [Scheme_Fees_Rolling12],
SUM ([Funding_Amt]) as [Funding_Amt_Rolling12]
Into #Fin
FROM [FDMS].[dbo].[Fact_Financial_History]f full outer join Dim_Outlet o
on f.hst_merchnum = o.FDMSAccountNo_First9
Where hst_date_processed > @Rolling12
group by o.FDMSAccountNo

--select* from #Fin
--drop table #Fin

--------------------------------ProcessingCost -----------------------

Declare @Rolling13 varchar(10)
set @Rolling13 = (select dateadd(MM,-12,max(Period))
from [FDMS].[dbo].[Fact_ProcessingCost])
SELECT FDMSAccountNo,
sum([Value]) as [ProcessingCost_Rolling12]
Into #Processing
FROM [FDMS].[dbo].[Fact_ProcessingCost]p
Where Period > @Rolling13
group by FDMSAccountNo

------------------------------------MSC ----------------------------

Declare @Rolling14 varchar(10)
set @Rolling14 = (select dateadd(MM,-12,max(Month_end_date))
FROM FDMS.dbo.Fact_Fee_History )
SELECT
FDMSAccountNo,
[Month_end_date] as hst_date_processed,
Case when Fee_Code IN ('42B','42C','42D','42E') Then 'PCI'
When Fee_Code = '00Y' Then 'Refund Transaction Charge'
When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'Y' then 'Other repeatable'
When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'N' then 'Other Non repeatable'
Else salesMI_Group1 end as description,
d.repeat_msc,
Sum([Retail_amount]) As Amount
Into #MscCount
FROM FDMS.dbo.Fact_Fee_History f
INNER JOIN
FDMS.dbo.Dim_Fee_Codes D ON f.Fee_Sequence_Number = D.Fee_Code
where Month_end_date> @Rolling14
Group by FDMSAccountNo, Month_end_date, d.Fee_Code, d.SalesMI_Group1, d.Repeat_MSC

--select * from #MscCount
--drop table #MscCount,

SELECT [FDMSAccountNo],
--hst_date_processed,
SUM ( case when description ='MSC' then ([Amount])else 0 end ) as [Rolling12_MSC],
SUM ( case when description ='PCI' then ([Amount])else 0 end ) as [Rolling12_PCI],
SUM ( case when description ='Refund Transaction Charge' then ([Amount])else 0 end ) as [RefundTransactionCharge_2012],
SUM ( case when description ='Other repeatable' then ([Amount])else 0 end ) as [Other_repeatable2012],
SUM ( case when description ='Other Non repeatable' then ([Amount])else 0 end ) as [Other_Non_repeatable2012]
Into #Msc
FROM #MscCount
group by [FDMSAccountNo]
--hst_date_processed

select FDMSAccountNo,
SUM ([Rolling12_MSC]) as [Rolling 12 MSC],
sum ([Rolling12_PCI]) as [Rolling 12 PCI],
sum([RefundTransactionCharge_2012]) as [Rolling 12 RTCharge],
sum ([Other_repeatable2012]) as [Rolling 12 OtherRep],
sum([Other_Non_repeatable2012]) as [Rolling 12 NonRep]
Into #msc1
from #Msc
group by [FDMSAccountNo]

--Drop table #msc1, #msc
--select * from #msc

------------------------------------Results Layout---------------------------------
Select
p.FDMSAccountNo,
[Net_Sales_Rolling12] as [Rolling 12 Net Sales],
[Net_Intg_Rolling12] as [Rolling 12 Net Interchange],
[Scheme_Fees_Rolling12] as[Rolling 12 Scheme Fees],
[Funding_Amt_Rolling12] as [Rolling 12 Funding],
[Rolling12_MSC] as [Rolling 12 MSC],
[Rolling12_PCI] as [Rolling 12 PCI],
[RefundTransactionCharge_2012] as [Rolling 12 RTCharge],
[Other_repeatable2012] as [Rolling 12 OtherRep],
[Other_Non_repeatable2012] as [Rolling 12 NonRep]

Into #results
from #Fin f
full outer join #Processing p on f.hst_merchnum = left(p.FDMSAccountNo,9)
full outer join #msc1 m on p.FDMSAccountNo = m.FDMSAccountNo

------------------------------ Calculations ------------------------------
Select *,
[Rolling12_MSC]-[Net_Intg_Rolling12] as [Rolling 12 DI],
[Rolling12_MSC]-[Net_Intg_Rolling12] -[Scheme_Fees_Rolling12] as [Rolling 12 DIA],
SUM ([Rolling 12 DIA]+[Other_repeatable2012]+[Rolling 12 Funding])- SUM([ProcessingCost_Rolling12]) as [rolling 12 Repcon]

----------------------------------- F Results -----------------

Into #FinalResults
From #results
group by [FDMSAccountNo],
[Rolling 12 Net Sales],
[Rolling 12 Net Interchange],
[Rolling 12 Scheme Fees],
[Rolling 12 Funding],
[Rolling 12 MSC],
[Rolling 12 PCI],
[Rolling 12 RTCharge],
[Rolling 12 OtherRep],
[Rolling 12 NonRep]

select o.ParentID,F.* from #FinalResults f
inner join fdms.dbo.Dim_Outlet o on o.FDMSAccountNo = f.FDMSAccountNo

--Drop table #Fin,#Processing, #MscCount,#Msc,#msc1,#FinalResults ,#results

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-03 : 07:02:26

For example because you are NOT inserting hst_merchnum into #Fin.

Too old to Rock'n'Roll too young to die.

FData
Starting Member

24 Posts

Posted - 2012-09-03 : 07:27:27

webfred !

You are spot on its fdmsaccountno :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources