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.
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 budgetI 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_DESCfrom JHDTjoin JHHDon JHDT.BATCH_NO = JHHD.BATCH_NOjoin ACCTon JHDT.ACCT_INT_CODE = ACCT.ACCT_INT_CODEwhereJNL_CODE = 'BUDGET' and TRAN_DATE >= '02/01/2013' and TRAN_DATE <= '02/28/2013'group byACCT.ACCT_CODE,ACCT.ACCT_DESC,JHHD.TRAN_DATE,JHHD.JNL_CODEorder by TRAN_DATE---------------------------------Insert Into #Actual (JNL_AMT, ACCT_CODE)selectsum(JHDT.JNL_AMT),ACCT.ACCT_CODE from JHDTjoin JHHDon JHDT.BATCH_NO = JHHD.BATCH_NOjoin ACCTon JHDT.ACCT_INT_CODE = ACCT.ACCT_INT_CODEwhereJNL_CODE not in ('BUDGET') and TRAN_DATE >= '02/01/2013' and TRAN_DATE <= '02/28/2013'group byACCT.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, #BudgetSLReidForum NewbieRenton, 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_DESCfrom #budget bjoin #actual a on a.acct_code = b.acct_code Be One with the OptimizerTG |
|
|
sherrireid
Yak Posting Veteran
58 Posts |
Posted - 2013-03-22 : 16:48:39
|
Duh! I just caught that myself!! Thanks for the help.SLReidForum NewbieRenton, WA USA |
|
|
|
|
|
|
|