August 30, 2012 at 12:52 am
I need to merge/combine the following two tables (tabl1 & table2). Results are in #Mergedtables . Value data on both tables are already aggregated
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#testtable1') IS NOT NULL DROP TABLE #testtable1
--===== Create the test table with
CREATE TABLE #testtable1
(
paytoname varchar (255),
pname varchar (255),
contract1 varchar (255),
Memcount1 int,-- aleady summed up
Paid1 int,-- aleady summed up
plans1 varchar (150),
)
INSERT INTO #testtable1
(paytoname, pname, contract1,Memcount1,Paid1,Plans1)
SELECT 'American DME','Smith, Frank' , '100%Medi' , '1500', '2400', 'Medplan1'
UNION ALL
SELECT 'ERP DME','Boyd, Carl' , '105%Medi' , '2500', '400', 'Medplan2'
UNION ALL
SELECT 'MOP DME','Falling, Peter' , '100%Medi' , '200', '1400', 'Medplan1'
IF OBJECT_ID('TempDB..#testtable2') IS NOT NULL DROP TABLE #testtable2
--Table 2
CREATE TABLE #testtable2
(
paytoname varchar (255),
pname varchar (255),
contract2 varchar (255),
Memcount2 int,-- aleady summed up
Pcount2 int,-- aleady summed up
Paid2 int, -- aleady summed up
plans2 varchar (150),
)
INSERT INTO #testtable2
(paytoname, pname, contract2,Memcount2,Pcount2,Paid2,Plans2)
SELECT 'American DME','Smith, Frank' , '101%Med' , '700', '150' , '1200', 'Medplan4'
UNION ALL
SELECT 'ERP DME','Boyd, Carl' , '105%Med' , '900', '600', '175', 'Medplan4'
UNION ALL
SELECT 'MOP DME','Falling, Peter' , '100%Med' , '1200', '3500', '240' , 'Medplan4'
UNION ALL
SELECT 'ASH DME','Ferrari, Shelly' , '90%Med' , '250', '1100', '197','Medplan4'
IF OBJECT_ID('TempDB..#Mergedtables ') IS NOT NULL DROP TABLE #Mergedtables
--Merged Tables Results
CREATE TABLE #Mergedtables
(
paytoname varchar (255),
pname varchar (255),
contract1 varchar (255),
contract2 varchar (255),
Memcount1 int,
Memcount2 int,
Pcount int,
Paid1 int,
Paid1 int,
plans1 varchar (150),
plans2 varchar (150)
)
INSERT INTO #Mergedtables
(paytoname, pname, contract1,contract2,Memcount1,Memcount2,Pcount1,Pcount2,Paid1,Paid2,Plans1,plans2)
SELECT 'American DME','Smith, Frank' , '100%Medi' , '101%Med' , '1500', '700', '2400' , '150' , '1200', 'Medplan1','Medplan4'
UNION ALL
SELECT 'ERP DME','Boyd, Carl' , '105%Medi','105%Med' , '2500', '900', '400', '600', '175', 'MedPlan2','Medplan4'
UNION ALL
SELECT 'MOP DME','Falling, Peter' , '100%Medi' , '100%Med' , '200', '1200', '1400', '3500', '240' , 'Medplan1' , 'Medplan4'
UNION ALL
SELECT 'ASH DME','Ferrari, Shelly' , '90%Med' , '250', '1100', '197','Medplan4'
Thank you for your help
Helal
August 30, 2012 at 1:36 am
Thanks for the DDL but it is not syntactically correct.
There are misssing columns from table1 (PCount), you are not specifiying enough columns in the union for the results table.
But from looking at the requirements it would seem you need an outer join and join the tables together.
August 30, 2012 at 12:19 pm
Thank you for catching that. Here is the corrected and tested DDL. I did try left join but can't get them lined up the way presented in results (MergedTab).
IF OBJECT_ID('TempDB..#table1') IS NOT NULL DROP TABLE #table1
--===== Create the test table with
CREATE TABLE #table1
(
paytoname varchar (255),
pname varchar (255),
contract1 varchar (255),
Memcount1 int,-- aleady summed up
Paid1 int,-- aleady summed up
plans1 varchar (150),
)
INSERT INTO #table1
(paytoname, pname, contract1,Memcount1,Paid1,Plans1)
SELECT 'American DME','Smith, Frank' , '100%Medi' , '1500', '2400', 'Medplan1'
UNION ALL
SELECT 'ERP DME','Boyd, Carl' , '105%Medi' , '2500', '400', 'Medplan2'
UNION ALL
SELECT 'MOP DME','Falling, Peter' , '100%Medi' , '200', '1400', 'Medplan1'
--Table 2
IF OBJECT_ID('TempDB..#table2') IS NOT NULL DROP TABLE #table2
CREATE TABLE #table2
(
paytoname varchar (255),
pname varchar (255),
contract2 varchar (255),
Memcount2 int,-- aleady summed up
Pcount int,-- aleady summed up
Paid2 int, -- aleady summed up
plans2 varchar (150),
)
INSERT INTO #table2
(paytoname, pname, contract2,Memcount2,Pcount,Paid2,Plans2)
SELECT 'American DME','Smith, Frank' , '101%Med' , '700', '150' , '1200', 'Medplan4'
UNION ALL
SELECT 'ERP DME','Boyd, Carl' , '105%Med' , '900', '600', '175', 'Medplan4'
UNION ALL
SELECT 'MOP DME','Falling, Peter' , '100%Med' , '1200', '3500', '240' , 'Medplan4'
UNION ALL
SELECT 'ASH DME','Ferrari, Shelly' , '90%Med' , '250', '1100', '197','Medplan4'
--Merged Tables Results
IF OBJECT_ID('TempDB..#MergedTab ') IS NOT NULL DROP TABLE #MergedTab
CREATE TABLE #MergedTab
(
paytoname varchar (255),
pname varchar (255),
contract1 varchar (255),
contract2 varchar (255),
Memcount1 int,
Memcount2 int,
Pcount int,
Paid1 int,
Paid2 int,
plans1 varchar (150),
plans2 varchar (150)
)
INSERT INTO #MergedTab
(paytoname, pname, contract1,contract2,Memcount1,Memcount2,Pcount,Paid1,Paid2,Plans1,plans2)
SELECT 'American DME', 'Smith, Frank', '100%Medi', '101%Med', '1500', '700', '150', '2400', '1200', 'Medplan1', 'Medplan4'
UNION ALL
SELECT 'ERP DME', 'Boyd, Carl', '105%Medi', '105%Med', '2500', '900', '600', '400', '175', 'Medplan2', 'Medplan4'
UNION ALL
SELECT 'MOP DME', 'Falling, Peter', '100%Medi', '100%Med', '200', '1200','3500', '1400', '240', 'Medplan1', 'Medplan4'
UNION ALL
SELECT 'ASH DME', 'Ferrari, Shelly', '', '90%Med', '', '250', '1100', '', '197', '', 'Medplan4'
Thank You for reviewin gand giving me directions.
Helal
August 30, 2012 at 12:34 pm
SELECT ISNULL(T1.paytoname, T2.paytoname) paytoname,
ISNULL(T1.pname, T2.pname) pname,
ISNULL(contract1,'') contract1,
contract2,
ISNULL(Memcount1,0) Memcount1,
Memcount2,
Pcount,
ISNULL(Paid1,0) Paid1,
Paid2,
ISNULL(plans1,'') plans1,
plans2
FROM #table2 T2
LEFT JOIN #table1 T1 ON T2.paytoname = T1.paytoname
August 30, 2012 at 2:02 pm
Awesome....worked liek a charm.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply