How do I create temporary tables, add data from other tables and total

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

SQL

DECLARE @Totaltbl TABLE (
PeriodTTL decimal (11,2), YTDTTL decimal (11,2), JTDTTL decimal (11,2))

INSERT INTO @Totaltbl (PeriodTTL, YTDTTL, JTDTTL) SELECT subTTLPer=( Table2.col1 + Table2.col2 + Table2.col3), subTTLYTD=(Table2.col4 + Table2.col5 + Table2.col6), subTTLJTD=(Table2.col7 + Table2.col8 + Table2.col9)

FROM dbo.Table2

UPDATE @Totaltbl SET PeriodTTL = subTTLPer, YTDTTL = subTTLYTD, JTDTTL = subTTLJTD

Go

SELECT Table2.col_10, Table2.col_11, Table2.col_12

FROM dbo.Table2

UPDATE @Totaltbl SET PeriodTTL = col_10, YTDTTL = col_11, JTDTTL = col_12

Go

SELECT sumCurPerAdj = SUM(Table1.Col1), sumFYDamt = SUM(Table1.Col2), sumITDamt = SUM(Table1.Col3)

FROM dbo.Table1

UPDATE @RevTotalstbl SET PeriodTTL = sumCurPerAdj, YTDTTL = sumFYDamt, JTDTTL = sumITDamt

[edit]Code Block fixed - OriginalGriff[/edit]

Posted 23-Mar-11 5:27am

technette

Updated 28-Mar-11 21:03pm

OriginalGriff

v3

Add a Solution

Comments

Om Prakash Pant 29-Mar-11 3:37am

you are already provided the code to insert data in temp tables and do calculations. what is the issue that you are facing?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Costica U · Answer 1 · 2011-03-29T00:42:00

1. You declare @Totaltbl as table variable (table variable != temp table ) and any variable can be accessed only in the batch that was created.

2. You update table @RevTotalstbl that is never declared

3. The code should look like

SQL

DECLARE @Totaltbl TABLE (
PeriodTTL decimal (11,2), YTDTTL decimal (11,2), JTDTTL decimal (11,2))
INSERT INTO @Totaltbl (PeriodTTL, YTDTTL, JTDTTL) SELECT subTTLPer=( Table2.col1 + Table2.col2 + Table2.col3), subTTLYTD=(Table2.col4 + Table2.col5 + Table2.col6), subTTLJTD=(Table2.col7 + Table2.col8 + Table2.col9)
FROM dbo.Table2
UPDATE @Totaltbl SET PeriodTTL = subTTLPer, YTDTTL = subTTLYTD, JTDTTL = subTTLJTD
--Go
SELECT Table2.col_10, Table2.col_11, Table2.col_12
FROM dbo.Table2
UPDATE @Totaltbl SET PeriodTTL = col_10, YTDTTL = col_11, JTDTTL = col_12
--Go
DECLARE @RevTotalstbl TABLE( COLUMN DEFINITIONS GO HERE)
SELECT sumCurPerAdj = SUM(Table1.Col1), sumFYDamt = SUM(Table1.Col2), sumITDamt = SUM(Table1.Col3)
FROM dbo.Table1
UPDATE @RevTotalstbl SET PeriodTTL = sumCurPerAdj, YTDTTL = sumFYDamt, JTDTTL = sumITDamt