Hi All,
Hope you are doing well!..I am trying to map the worklist data based on different sets of conditions ...TABLE -Input 1 has the worklist input data with the specid and ctextid information ..Table Input-2 has the other information that need to be mapped(along with specid and ctextid).. Now table input 1 has the specid and ctextid data in three different ways
-
Both specid and ctextid available in table Input 1..In this case the corresponding rows of data from table input2 has to be mapped based on the join of specid and ctextid ; also the comment should be included in the output that -Wlist has both ctextid and specid
-
Only ctextid is present in table Input 1 ..In this case the corresponding rows of data from table input2 has to be mapped based on the join of ctextid alone ; also the comment should be included in the output that-Wlist has only ctextid
-
Only specid is present in table Input 1 ..In this case the corresponding rows of data from table input2 has to be mapped based on the join of specid alone ; also the comment should be included in the output that-Wlist has only specid
INPUT1
create table ##input1
(wid int,
wname varchar(100),
status int,
orgid varchar(100),
caretype varchar(100),
specid int,
ctextid int
)
insert into ##input1 values
('3','chela-21','2','acu','jinja','9','123'),
('6','tolo-2','3','ofg','jinja','','891'),
('9','kior3','4','acu','jinja','22','')
INPUT2
create table ##input2
(
ctextid int,
dname varchar(100),
specid int,
id int,
pservtype int
)
insert into ##input2 values
('123','ting1','9','67','3'),
('123','hjt2','9','89','4'),
('123','ting1','9','67','4'),
('123','yuit','8','77','2'),
('891','bhg5','8','44','3'),
('891','zxc34','5','55','1'),
('891','ghyt','8','67','5'),
('891','fge3','78','23','4'),
('1183','hj2','22','9','2'),
('1183','jk2','22','89','1'),
('2111','uio','22','64','2'),
('678','jku','22','22','3')
OUTPUT
create table ##output
(wid int,
wname varchar(100),
status int,
orgid varchar(100),
caretype varchar(100),
specid int,
ctextid int,
dname varchar(100),
id int,
pservtype int,
comment varchar(1000)
)
insert into ##output values
('3','chela-21','2','acu','jinja','9','123','ting1','67','3','Wlist has both ctextid and specid'),
('3','chela-21','2','acu','jinja','9','123','hjt2','89','4','Wlist has both ctextid and specid'),
('3','chela-21','2','acu','jinja','9','123','ting1','67','4','Wlist has both ctextid and specid'),
('6','tolo-2','3','ofg','jinja','8','891','bhg5','44','3','Wlist has only ctextid'),
('6','tolo-2','3','ofg','jinja','5','891','zxc34','55','1','Wlist has only ctextid'),
('6','tolo-2','3','ofg','jinja','8','891','ghyt','67','5','Wlist has only ctextid'),
('6','tolo-2','3','ofg','jinja','78','891','fge3','23','4','Wlist has only ctextid'),
('9','kior3','4','acu','jinja','22','1183','hj2','9','2','Wlist has only specid'),
('9','kior3','4','acu','jinja','22','1183','jk2','89','1','Wlist has only specid'),
('9','kior3','4','acu','jinja','22','2111','uio','64','2','Wlist has only specid'),
('9','kior3','4','acu','jinja','22','678','jku','22','3','Wlist has only specid')
Thanks,
Arun