Mapping rows in the routing table with other tables on certain conditions

Question

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

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

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

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

4) 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

Answer 1

Hi,@Hellothere8028
Please check this:

;WITH CTE1 AS  
(  
 SELECT R.ctextid,CASE WHEN R.deptid='' THEN DP.deptid ELSE R.deptid END AS deptid,  
                  CASE WHEN R.prid='' THEN DP.prid ELSE R.prid END AS prid  
 FROM ##routing R LEFT JOIN ##deptprovi DP ON R.ctextid=DP.ctextid  
),CTE2 AS  
(  
 SELECT C1.ctextid,D.id deptid,P.id prid,P.specid,D.pservtype   
 FROM CTE1 C1 LEFT JOIN ##dept D ON C1.ctextid=D.ctextid AND C1.deptid=D.id  
              LEFT JOIN ##provider P ON C1.ctextid=P.ctextid AND C1.prid=P.id  
),CTE3 AS  
(  
 SELECT C2.ctextid  
 FROM CTE2 C2   
 WHERE C2.deptid IS NULL OR C2.prid IS  NULL  
)  
 SELECT * FROM CTE2   
 WHERE deptid IS NOT NULL AND prid IS NOT NULL  
 UNION  
 SELECT C3.ctextid ,D.id deptid,NULL AS prid,NULL AS specid,D.pservtype  
 FROM CTE3 C3 JOIN ##dept D ON C3.ctextid =D.ctextid  
 UNION   
 SELECT C3.ctextid ,NULL,P.id,P.specid,NULL  
 FROM CTE3 C3 JOIN ##provider P ON C3.ctextid =P.ctextid  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.