How to Combine two tables results into one table

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 2005 Forums

Transact-SQL (2005)

How to Combine two tables results into one table

Author

Topic

vision99
Starting Member

14 Posts

Posted - 2009-06-18 : 10:40:41

Dear All,

I have 2 tables, and am having problem to get final resultset...

1. CREATE TABLE #tempFinal
(

[Group] VARCHAR(50),
LastCalled DATETIME,
TotalAssign INT,
sales INT,
refusals INT,
unworkables INT,
finalized INT
)

INSERT INTO #tempFinal
SELECT
[Group],
date,
SUM(assgn_num),
CASE WHEN Y.rowTitle='Sales' THEN SUM(x.finalized)
ELSE 0
END AS 'Sales',
CASE WHEN Y.rowTitle='Refusals' THEN SUM(x.finalized)
ELSE 0
END AS 'Resfusals',
CASE WHEN Y.rowTitle='UnWorkables' THEN SUM(x.finalized)
ELSE 0
END AS 'UnWorkables',
SUM(x.finalized)
FROM
prmNAYReport X
LEFT JOIN
ctlNAYStatuses Y
ON
x.status=y.status
GROUP BY
[Group],
[Date],
rowTitle

2.CREATE TABLE #tempLeads
(
date DATETIME,
grossLeads INT,
netLeads INT,
)

INSERT INTO #tempLeads
SELECT
date,
CASE WHEN Y.rowTitle='Gross Leads' THEN SUM(x.Leads)
ELSE 0
END AS 'Gross Leads',
CASE WHEN Y.rowTitle='Net Leads' THEN SUM(x.Leads)
ELSE 0
END AS 'Net Leads'
FROM
prmNAYReport X
JOIN
ctlNAYStatuses Y
ON
x.status=y.status
GROUP BY
[Group],
[Date],
rowTitle

Now i have to perform some calculations to get the final resultset, so after performing some calculations i want to insert into one final temporary table like

3.CREATE TABLE #tempSummary
(
[Group] VARCHAR(50),
LastCalled DATETIME,
TotalLeads INT,
TotalTries REAL,
OpenTries INT,
TotalAssign INT,
TotalYes INT,
TotalConts INT,
CONV REAL,
TotalBad INT,
Badpercent REAL,
Badnum REAL,
UnsoldAssign INT,
Pen_per_Assign REAL,
OpenAssign REAL,
RecordsBlocked INT,
RecordsCallable INT,
ListSize INT,
DayoneHours REAL,
DaytwoHours REAL
)

INSERT INTO #tempSummary
SELECT
[Group],
LastCalled,
grossLeads,
totalAttempts/grossLeads AS TotalTries,
totalAttempts/(grossLeads - LeadsRemaining) AS OpenTries,
totalAssign,
status,
sales + refusals,
sales / refusals,
unworkables,
unworkables/grossLeads,
unworkables/(unworkables + sales + refusals),
totalAssign - sales,
sales / totalAssign,
NULL,
NULL,
netLeads - finalized,
netLeads - finalized,
NULL,
NULL

Now my question is how to combine table 1(#tempFinal) and table 2(#tempLeads) and insert the result into table3(#tempSummary) after performing some calculations..

Thanks in advance..

-Vision

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-18 : 12:01:30

how are (#tempFinal) and (#tempLeads) related? Based on your table definitions, I dont think they are related at all...or am I missing something?

vision99
Starting Member

14 Posts

Posted - 2009-06-18 : 12:30:35

quote:
Originally posted by vijayisonly

how are (#tempFinal) and (#tempLeads) related? Based on your table definitions, I dont think they are related at all...or am I missing something?

Both tables #tempFinal & #tempLeads dont have any common fields, both are using different base tables, but is it possible to insert into another table....

-Vision

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-18 : 14:35:37

seems like what you need is to use UNION or UNION ALL to combine the two resultsets and insert to third table.but seeing the third table, it seems like it contains both set of columns from first two tables. in that case, how will you determine which row of first table should be merged with which row of second table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources