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

Hellothere8028 821 Reputation points
2022-01-20T17:16:18.35+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. LiHong-MSFT 10,046 Reputation points
    2022-01-21T06:00:17.203+00:00

    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:
    167087-image.png

    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful