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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 temp tables

Author  Topic 

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2013-03-22 : 16:05:42
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-22 : 16:22:35
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

58 Posts

Posted - 2013-03-22 : 16:48:39
Duh! I just caught that myself!! Thanks for the help.

SLReid
Forum Newbie
Renton, WA USA
Go to Top of Page
   

- Advertisement -