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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 temp tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sherrireid
Yak Posting Veteran

USA
58 Posts

Posted - 03/22/2013 :  16:05:42  Show Profile  Reply with Quote
I am trying to get the dollar amount for all budget records and the dollar amount for all actual financial transactions and then subtract the actual from the budget

I created both of the temp tables, but when I try to run the query that substracts the actualy from the budget, it says my tables don't exist. But they do, because the queries to create the 2 temp tables work.

What is wrong? Here is my code

--- Create the temp tables ------
CREATE TABLE #Budget (
JNL_CODE char(8),
TRAN_DATE datetime,
JNL_AMT money,
ACCT_CODE varchar(60),
ACCT_DESC varchar(60))

CREATE TABLE #Actual (
JNL_AMT money,
ACCT_CODE varchar(60))

---- Populate the temp tables-----
INSERT INTO #Budget (JNL_CODE, TRAN_DATE, JNL_AMT, ACCT_CODE, ACCT_DESC)
select
JHHD.JNL_CODE,
JHHD.TRAN_DATE,
sum(JHDT.JNL_AMT),
ACCT.ACCT_CODE,
ACCT.ACCT_DESC


from JHDT
join JHHD
on JHDT.BATCH_NO = JHHD.BATCH_NO
join ACCT
on JHDT.ACCT_INT_CODE = ACCT.ACCT_INT_CODE

where
JNL_CODE = 'BUDGET' and
TRAN_DATE >= '02/01/2013' and
TRAN_DATE <= '02/28/2013'

group by
ACCT.ACCT_CODE,
ACCT.ACCT_DESC,
JHHD.TRAN_DATE,
JHHD.JNL_CODE

order by TRAN_DATE
---------------------------------

Insert Into #Actual (JNL_AMT, ACCT_CODE)
select

sum(JHDT.JNL_AMT),
ACCT.ACCT_CODE

from JHDT
join JHHD
on JHDT.BATCH_NO = JHHD.BATCH_NO
join ACCT
on JHDT.ACCT_INT_CODE = ACCT.ACCT_INT_CODE

where
JNL_CODE not in ('BUDGET') and
TRAN_DATE >= '02/01/2013' and
TRAN_DATE <= '02/28/2013'

group by
ACCT.ACCT_CODE


----Run query against temp tables -----
select
(#Budget.JNL_AMT - #Actual.JNL_AMT) as DIFF,
#Budget.ACCT_CODE,
#Budget.ACCT_DESC

--- drop temp tables---
drop table #Actual, #Budget



SLReid
Forum Newbie
Renton, WA USA

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 03/22/2013 :  16:22:35  Show Profile  Reply with Quote
The problem is you final select statement. There is not FROM clause. I can't tell how rows in #budget correlated with rows in #actual. But perhaps something like this:

select b.JNL_AMT - a.JNL_AMT as DIFF,
       b.ACCT_CODE,
       b.ACCT_DESC
from   #budget b
join   #actual a on a.acct_code = b.acct_code


Be One with the Optimizer
TG
Go to Top of Page

sherrireid
Yak Posting Veteran

USA
58 Posts

Posted - 03/22/2013 :  16:48:39  Show Profile  Reply with Quote
Duh! I just caught that myself!! Thanks for the help.

SLReid
Forum Newbie
Renton, WA USA
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.05 seconds. Powered By: Snitz Forums 2000