June 21, 2012 at 11:54 am
Hello,
I have the following tables below:
CREATE TABLE #Definition
(
id INT IDENTITY(1,1) PRIMARY KEY,
OldCustomer VARCHAR(150),
NewCustomer VARCHAR(150)
);
INSERT INTO #Definition (oldcustomer,newcustomer)
VALUES ('oldfirstname','newfirstname');
CREATE TABLE #Match
(
Matchid INT IDENTITY(1,1) PRIMARY KEY,
Definition_ID int
);
CREATE TABLE #CustomerOld
(
id INT IDENTITY(1,1) PRIMARY KEY,
oldfirstname VARCHAR(150),
oldLastName varchar(150
);
INSERT INTO #customerold (oldfirstname,oldlastname)
VALUES ('Bob','Sam');
INSERT INTO #customerold (oldfirstname,oldlastname)
VALUES ('Kevin','Ortiz');
INSERT INTO #customerold (oldfirstname,oldlastname)
VALUES ('brian','andy');
INSERT INTO #customerold (oldfirstname,oldlastname)
VALUES ('dave','right');
INSERT INTO #customerold (oldfirstname,oldlastname)
VALUES ('Kevin','Ortiz');
CREATE TABLE #Customernew
(
id INT IDENTITY(1,1) PRIMARY KEY,
newfirstname VARCHAR(150),
newLastName varchar(150
);
INSERT INTO #customerold (oldfirstname,oldlastname)
VALUES ('steve','ax');
INSERT INTO #customerold (oldfirstname,oldlastname)
VALUES ('alex','amana');
INSERT INTO #customerold (oldfirstname,oldlastname)
VALUES ('Kevin','Ortiz');
INSERT INTO #customerold (oldfirstname,oldlastname)
VALUES ('james','loong');
INSERT INTO #customerold (oldfirstname,oldlastname)
VALUES ('dave','wrong');
What i would like to happen is with a stored procedure is first look at the definition table, to see what we need to compare the 2 tables (compare old customer data with the new customer data to see if there is a match), as you can see on the insert, we only want to compare firstname of old customer with new customer first name, so then it will look at both tables, and display both the old customer first name and new customer first name. This will be dynamic in the definition because maybe we want to compare first name of old and new customer, but also last name as well, to see if old first name and old last name match plus have both id's of old and new customer with their first and lastname.
I am stuck i know i should use maybe intersect but going from definition is hard right now.
June 21, 2012 at 2:05 pm
Hello,
I am getting close, but my query will not show the First Names from new customers table, what it will show is "FirstName" all the rows, which i want to show the actual customer name fromt the new customer list, here is the query below:
declare @Columns varchar (150)
WITH GETCOLUMNS(definition_ID,OldCustomers, NewCustomers)
as
(
select a.Definition_ID, oldcustomers, newcustomers
from #definition a
Join #Match b
on a.Definition_ID = b.Definition_ID
)
select @Columns = NewCustomers from GETCOLUMNS
select @Columns
from #Customernew
June 21, 2012 at 5:27 pm
Solved problem, what i did was instead of CTE used a temp table then I queried.
Thank you anyways 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply