Hi All,
Hope you are doing well!... i have a base routing table where I have the entries at a ctextid ,deptid and prid level... I need to map the data from the routing table to the deptprovi ,provider and dept tables based on a set of rules ...Below are the rules, Also I am providing the DDL for the tables below: Can you please help me here...
Note id column in dept table indicates deptid ; id column in provider table indicates prid
- when the routing table has only ctextid data with the deptid and prid data blank then
- I need to join the data to the deptprovi table based on the ctextid and then join the (ctextid,deptid) from deptprovi to the (ctextid,id) column in dept table to get the pservtype and then join the (ctextid,prid) in deptprovi table with the (ctextid,id) in provider table to get the specid
- In case there are no matching rows of ctextid in deptprovi table from the routing table then take all the matching rows of the ctextid from the provider table and also take all the matching rows of the ctextid from the dept table
- when the routing table has ctext data , deptid data then
- I need to join the data to the deptprovi table based on the ctextid and deptid in the routing table and then join the (ctextid,deptid) from deptprovi to the (ctextid,id) column in dept table to get the pservtype and then join the (ctextid,prid) in deptprovi table with the (ctextid,id) in provider table to get the specid
-In case there are no matching rows of ctextid and deptid in deptprovi table from the routing table then take all the matching rows of the ctextid from the provider table and also take all the matching rows of the (ctextid, deptid) from the dept table (ctextid,id)
- when the routing table has ctext data , prid data then
I need to join the data to the deptprovi table based on the ctextid and prid in the routing table and then join the (ctextid,deptid) from deptprovi to the (ctextid,id) column in dept table to get the pservtype and then join the (ctextid,prid) in deptprovi table with the (ctextid,id) in provider table to get the specid
-In case there are no matching rows of ctextid and prid in deptprovi table from the routing table then take all the matching rows of the (ctextid,prid) from the provider table (ctexid,id) and also take all the matching rows of the ctextid from dept table
- when the routing table has ctextid, deptid,prid then then take all the matching rows of the (ctextid,prid) from the provider table (ctexid,id) and also take all the matching rows of the (ctextid,deptid) from dept table (ctextid,id)
create table ##routing
(ctextid int,
deptid int,
prid int
)
insert into ##routing values
('2113','',''),
('3214','',''),
('1231','21',''),
('4512','17',''),
('2134','7','31'),
('7812','','22'),
('6612','','11')
create table ##deptprovi
(ctextid int,
deptid int,
prid int)
insert into ##deptprovi values
('2113','6','13'),
('2113','6','9'),
('2113','4','12'),
('1231','21','12'),
('1231','21','13'),
('7812','71','22')
create table ##dept
(ctextid int,
pservtype int,
id int)
insert into ##dept values
('3214','12','2'),
('3214','10','3'),
('4512','6','17'),
('2134','5','7'),
('7812','2','8'),
('7812','3','10'),
('2113','2','6'),
('2113','3','4'),
('1231','10','21'),
('6612','21','8'),
('6612','31','10')
create table ##provider
(ctextid int,
specid int,
id int)
insert into ##provider values
('2113','1','13'),
('2113','2','9'),
('2113','3','12'),
('3214','4','892'),
('3214','5','781'),
('4512','8','90'),
('4512','7','91'),
('2134','2','31'),
('7812','9','22'),
('1231','4','12'),
('1231','5','13'),
('6612','8','11')
create table ##output
(ctextid int,
deptid int,
prid int,
specid int,
pservtype int)
insert into ##output values
('2113','6','13','1','2'),
('2113','6','9','2','2'),
('2113','4','12','3','3'),
('3214','','892','4',''),
('3214','','781','5',''),
('3214','2','','','12'),
('3214','3','','','10'),
('1231','21','12','4','10'),
('1231','21','13','5','10'),
('4512','','90','8',''),
('4512','','91','7',''),
('4512','17','','','6'),
('2134','7','31','2','5'),
('7812','','22','9',''),
('7812','8','','','2'),
('7812','10','','','3'),
('6612','','11','8',''),
('6612','8','','','21'),
('6612','10','','','31')
Thanks,
Arun