SQLTeam.com | Weblogs | Forums

Mapping Worklist data based on different sets of conditions

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

  1. 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

  2. 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

  3. 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

Select *, 'Wlist has both ctextid and specid' as Comment
from #input1 i1
join #input2 i2
on i1.ctextid = i2.ctextid
and i1.specid = i2.specid
union all
Select *, 'Wlist has only ctextid' as Comment
from #input1 i1
join #input2 i2
on i1.ctextid = i2.ctextid
and i1.specid <> i2.specid
union all
Select *, 'Wlist has only specid' as Comment
from #input1 i1
join #input2 i2
on i1.ctextid <> i2.ctextid
and i1.specid = i2.specid

1 Like

Thanks @mike01 !..Really appreciate your help