April 4, 2014 at 5:08 pm
Hello Could you please help me with this issue,
in this selection when there is partciluar segment found in the selection then need to add 2 records per segment otherwise just selected results
--Drop table #list
CREATE TABLE #LIST ( email varchar(20),Segment varchar(20), Addr1 varchar(20), City varchar(20),ST varchar(20), Code int)
--Drop table #subject
CREATE TABLE #Subject (Segment varchar(20), Fname varchar(20), LName varchar(20))
INSERT INTO #LIST VALUES ('mmm@m.com','VEC_BAL','20 MORC ST','KIMS','TX',1234)
INSERT INTO #LIST VALUES ('vin@tta.com','VEC_BAL','345 KIM RD','ROBOR','TX',2345)
INSERT INTO #LIST VALUES ('imjh@ui.com','VEC_BAL','44 SILVER PL','SINGOOR','TX',323)
INSERT INTO #LIST VALUES ('lrk@m.com','PPC_BAL','DRIVE ST','BOSTON','TX',123)
INSERT INTO #LIST VALUES ('tom@tre.com','PPC_BAL','STREET RD','NYC','NY',1234)
INSERT INTO #LIST VALUES ('jim@rty.com','LEDU_BAL','PARK PLACE','NORWALK','CT',324)
INSERT INTO #LIST VALUES ('kim@a.com','VUNDADU','WATH CT','OSCAR','OH',455)
INSERT INTO #LIST VALUES ('tina@k.com','LIGHT','SNADAR RD','NYC','NY',655)
INSERT INTO #LIST VALUES ('sona@al.com','VIRTUAL_BAL','MILL PK','HARTFORD','CT',456)
INSERT INTO #Subject VALUES ('VEC_BAL','Mark','Toolsi')
INSERT INTO #Subject VALUES ('PPC_BAL','Sony','Nimura')
INSERT INTO #Subject VALUES ('LEDU_BAL','Jonathan','Peox')
INSERT INTO #Subject VALUES ('VUNDADU','Dan','Arnold')
INSERT INTO #Subject VALUES ('LIGHT','Mike','Steven')
INSERT INTO #Subject VALUES ('VIRTUAL_BAL','Phiolip','Dora')
INSERT INTO #Subject VALUES ('Monarch_BAL','RAS','KILEN')
/*
select Segment , Addr1 , City ,ST from #LIST
select Segment , Fname , LName from #Subject
*/
---Selection results
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expectedresult FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment
--Now here what i am trying to get, union the below two record to the above "selection Results" for only segment ='VEC_BAL' or segment = 'PPC_BAL'
--2 records
SELECT l.segment , l.email , Addr1, City, ST, 999 as Code,'' fname, ''lname from #LIST l where email ='mmm@m.com'
SELECT l.segment , l.email , Addr1, City, ST, 888 as Code, '' fname, ''lname from #LIST l where email ='lrk@m.com'
--- expected results please execute below block ( per each segment 2 records will insert if there is segment ='VEC_BAL' or 'PPC_BAL' ). If there is no Vec_Bal or PPC BAL then no additional inserts, if there is only VEC_BAL no PPC_BAL then only 2 records insert
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expctresults1 FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment
union
SELECT l.segment , l.email , Addr1, City, ST, 999 as Code,'VEC_BAL' fname, ''lname from #LIST l where email ='mmm@m.com'
union
SELECT l.segment , l.email , Addr1, City, ST, 888 as Code, 'VEC_BAL' fname, ''lname from #LIST l where email ='lrk@m.com'
union
SELECT l.segment , l.email , Addr1, City, ST, 999 as Code,'PPC_BAL' fname, ''lname from #LIST l where email ='mmm@m.com'
union
SELECT l.segment , l.email , Addr1, City, ST, 888 as Code, 'PPC_BAL' fname, ''lname from #LIST l where email ='lrk@m.com'
select * from #expctresults1
-----
Please help me
Thank you in advance
Asita
April 4, 2014 at 8:07 pm
Sorry for the Confusion,
to make it simple,
--#list table has a composite primary key on (AcctID, email ,Segment)
--Drop table #list
CREATE TABLE #LIST (AcctID numeric, email varchar(20),Segment varchar(20), Addr1 varchar(20), City varchar(20),ST varchar(20), Code int)
INSERT INTO #LIST VALUES (100,'mmm@m.com','VEC_BAL','20 MORC ST','KIMS','TX',1234)
INSERT INTO #LIST VALUES (103,'lrk@m.com','VEC_BAL','DRIVE ST','BOSTON','TX',123)
INSERT INTO #LIST VALUES (101,'vin@tta.com','VEC_BAL','345 KIM RD','ROBOR','TX',2345)
INSERT INTO #LIST VALUES (102,'imjh@ui.com','VEC_BAL','44 SILVER PL','SINGOOR','TX',323)
INSERT INTO #LIST VALUES (103,'lrk@m.com','PPC_BAL','DRIVE ST','BOSTON','TX',123)
INSERT INTO #LIST VALUES (100,'mmm@m.com','PPC_BAL','20 MORC ST','KIMS','TX',1234)
INSERT INTO #LIST VALUES (104,'tom@tre.com','PPC_BAL','STREET RD','NYC','NY',1234)
INSERT INTO #LIST VALUES (105,'jim@rty.com','LEDU_BAL','PARK PLACE','NORWALK','CT',324)
INSERT INTO #LIST VALUES (106,'kim@a.com','VUNDADU','WATH CT','OSCAR','OH',455)
INSERT INTO #LIST VALUES (107,'tina@k.com','LIGHT','SNADAR RD','NYC','NY',655)
INSERT INTO #LIST VALUES (108,'sona@al.com','VIRTUAL_BAL','MILL PK','HARTFORD','CT',456)
INSERT INTO #LIST VALUES (104,'t.tom@tre.com','PPC_BAL','STREET RD','NYC','NY',1234)
INSERT INTO #LIST VALUES (105,'jh.jim@rty.com','LEDU_BAL','PARK PLACE','NORWALK','CT',324)
INSERT INTO #LIST VALUES (106,'p.kim@a.com','VUNDADU','WATH CT','OSCAR','OH',455)
select * from #LIST -- lists all members
now here i am requesting your help, for to add 2 more records to the selection output, with below rules,
if results contains Segment is in (VEC_BAL or PPC_BAL) then add 2 records(mmm@m.com, lrk@m.com) per each segment(if exist) with same row but the Code (last column ) change to 999
so expected results with the above sample data is
select * into #expectResults from #LIST --drop table #one
union
select 100,'mmm@m.com','VEC_BAL','20 MORC ST','KIMS','TX',999
union
select 103,'lrk@m.com','PPC_BAL','DRIVE ST','BOSTON','TX',999
union
select 100,'mmm@m.com','PPC_BAL','20 MORC ST','KIMS','TX',999
union
select 103,'lrk@m.com','VEC_BAL','DRIVE ST','BOSTON','TX',999
select * from #expectResults order by case when Code=999 then 1 else 2 end
hope this clears what i am requesting your help
Thanks a ton in advance
April 4, 2014 at 11:37 pm
Use a CTE or a table with the additional email addresses to be added, cross join to a filtered set with code replacement and union the result to the original table;
😎
CREATE TABLE #LIST (AcctID numeric, email varchar(20),Segment varchar(20), Addr1 varchar(20), City varchar(20),ST varchar(20), Code int);
INSERT INTO #LIST VALUES (100,'mmm@m.com','VEC_BAL','20 MORC ST','KIMS','TX',1234)
INSERT INTO #LIST VALUES (103,'lrk@m.com','VEC_BAL','DRIVE ST','BOSTON','TX',123)
INSERT INTO #LIST VALUES (101,'vin@tta.com','VEC_BAL','345 KIM RD','ROBOR','TX',2345)
INSERT INTO #LIST VALUES (102,'imjh@ui.com','VEC_BAL','44 SILVER PL','SINGOOR','TX',323)
INSERT INTO #LIST VALUES (103,'lrk@m.com','PPC_BAL','DRIVE ST','BOSTON','TX',123)
INSERT INTO #LIST VALUES (100,'mmm@m.com','PPC_BAL','20 MORC ST','KIMS','TX',1234)
INSERT INTO #LIST VALUES (104,'tom@tre.com','PPC_BAL','STREET RD','NYC','NY',1234)
INSERT INTO #LIST VALUES (105,'jim@rty.com','LEDU_BAL','PARK PLACE','NORWALK','CT',324)
INSERT INTO #LIST VALUES (106,'kim@a.com','VUNDADU','WATH CT','OSCAR','OH',455)
INSERT INTO #LIST VALUES (107,'tina@k.com','LIGHT','SNADAR RD','NYC','NY',655)
INSERT INTO #LIST VALUES (108,'sona@al.com','VIRTUAL_BAL','MILL PK','HARTFORD','CT',456)
INSERT INTO #LIST VALUES (104,'t.tom@tre.com','PPC_BAL','STREET RD','NYC','NY',1234)
INSERT INTO #LIST VALUES (105,'jh.jim@rty.com','LEDU_BAL','PARK PLACE','NORWALK','CT',324)
INSERT INTO #LIST VALUES (106,'p.kim@a.com','VUNDADU','WATH CT','OSCAR','OH',455);
/* Emails for additional records(mmm@m.com, lrk@m.com)*/
;WITH ALTERN_EMAIL AS
(
SELECT A_EMAIL = 'mmm@m.com'
UNION ALL
SELECT A_EMAIL = 'lrk@m.com'
)
/* Create the records */
,ADD_LIST AS
(
SELECT
AcctID
,AE.A_EMAIL AS email
,Segment
,Addr1
,City
,ST
,Code = 999
FROM #LIST LS
CROSS JOIN ALTERN_EMAIL AE
WHERE LS.Segment IN ('VEC_BAL','PPC_BAL')
)
SELECT * FROM ADD_LIST
UNION ALL
SELECT * FROM #LIST;
DROP TABLE #LIST;
And the results;
AcctID email Segment Addr1 City ST Code
------- --------------- ------------ ------------- --------- --- -----
100 mmm@m.com VEC_BAL 20 MORC ST KIMS TX 999
100 lrk@m.com VEC_BAL 20 MORC ST KIMS TX 999
103 mmm@m.com VEC_BAL DRIVE ST BOSTON TX 999
103 lrk@m.com VEC_BAL DRIVE ST BOSTON TX 999
101 mmm@m.com VEC_BAL 345 KIM RD ROBOR TX 999
101 lrk@m.com VEC_BAL 345 KIM RD ROBOR TX 999
102 mmm@m.com VEC_BAL 44 SILVER PL SINGOOR TX 999
102 lrk@m.com VEC_BAL 44 SILVER PL SINGOOR TX 999
103 mmm@m.com PPC_BAL DRIVE ST BOSTON TX 999
103 lrk@m.com PPC_BAL DRIVE ST BOSTON TX 999
100 mmm@m.com PPC_BAL 20 MORC ST KIMS TX 999
100 lrk@m.com PPC_BAL 20 MORC ST KIMS TX 999
104 mmm@m.com PPC_BAL STREET RD NYC NY 999
104 lrk@m.com PPC_BAL STREET RD NYC NY 999
104 mmm@m.com PPC_BAL STREET RD NYC NY 999
104 lrk@m.com PPC_BAL STREET RD NYC NY 999
100 mmm@m.com VEC_BAL 20 MORC ST KIMS TX 1234
103 lrk@m.com VEC_BAL DRIVE ST BOSTON TX 123
101 vin@tta.com VEC_BAL 345 KIM RD ROBOR TX 2345
102 imjh@ui.com VEC_BAL 44 SILVER PL SINGOOR TX 323
103 lrk@m.com PPC_BAL DRIVE ST BOSTON TX 123
100 mmm@m.com PPC_BAL 20 MORC ST KIMS TX 1234
104 tom@tre.com PPC_BAL STREET RD NYC NY 1234
105 jim@rty.com LEDU_BAL PARK PLACE NORWALK CT 324
106 kim@a.com VUNDADU WATH CT OSCAR OH 455
107 tina@k.com LIGHT SNADAR RD NYC NY 655
108 sona@al.com VIRTUAL_BAL MILL PK HARTFORD CT 456
104 t.tom@tre.com PPC_BAL STREET RD NYC NY 1234
105 jh.jim@rty.com LEDU_BAL PARK PLACE NORWALK CT 324
106 p.kim@a.com VUNDADU WATH CT OSCAR OH 455
April 5, 2014 at 1:49 pm
Thank you very much Eiriksson,
Thank you for your response Eiriksson
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply