Append Group with data from another table based on its existence in first table

user_954093 0
Viewed 23

I have a table like this

Project       Project Name  Stage
----------------------------------------
P123           Project1      L1
P123           Project1      L2
P123           Project1      L1
P123           Project1      Phase1

P452           Project2      L1
P452           Project2      L2
P452           Project2      START
P452           Project2      L3

P432           Project3      L1
P432           Project3      L2
P432           Project3      Phase1
P432           Project3      Phase2

I have another table with these data

PhaseCode       PhaseName
------------------------------
Phase1          Select
Phase2          Develop
Phase3          Deploy
Phase4          Close

Based on the second table I need to find which phases are missing for each Project codes

So I need to get a result like this

Project       Project Name  Stage     Phase Name      Status
----------------------------------------
P123           Project1      L1
P123           Project1      L2
P123           Project1      L1
P123           Project1      Phase1      Select       Available
P123           Project1      Phase2      Develop        Missing
P123           Project1      Phase3      Deploy        Missing
P123           Project1      Phase4      Close         Missing

P452           Project2      L1   
P452           Project2      L2
P452           Project2      START
P452           Project2      L3
P452           Project2      Phase1      Select       Missing
P452           Project2      Phase2      Develop        Missing
P452           Project2      Phase3      Deploy        Missing
P452           Project2      Phase4      Close         Missing

P432           Project3      L1
P432           Project3      L2
P432           Project3      Phase1     Select       Available
P432           Project3      Phase2     Develop       Available
P452           Project3      Phase3      Deploy        Missing
P452           Project3      Phase4      Close         Missing

I tried cross join it doesn work since its creating 4 entries for each one item in the first table. So how can I achieve the above result check whether Phase is existing for each group?

So what I am expecting is to append each group of PCodes with that from Phase table but append only those not in first group

union sql-server sql
1 Answers

Try this:

SELECT COALESCE(t1.Project, t2.Project) AS Project
    , COALESCE(t1.[Project Name], t2.[Project Name]) AS [Project Name]
    , COALESCE(t2.Stage, t1.PhaseCode) AS Stage
    , t1.PhaseName
    , CASE 
         WHEN PhaseName IS NULL THEN ''
         ELSE CASE 
                 WHEN t2.Project IS NULL THEN 'Missing' 
                 ELSE 'Available' 
              END 
      END AS Status
FROM (
    SELECT DISTINCT Project, [Project Name], PhaseCode, PhaseName
    FROM Project 
    CROSS JOIN Phase
) t1
FULL OUTER JOIN (
    SELECT Project, Stage, [Project Name]
    FROM Project
) t2 ON t1.Project = t2.Project AND t1.PhaseCode = t2.Stage
ORDER BY COALESCE(t1.Project, t2.Project), Stage

Demo here

To better understand how the query works you can have a look at the rows of derived table t1:

Project |   Project Name  | PhaseCode | PhaseName
--------|-----------------|-----------|------------
P123    |  Project1       |  Phase1   | Select
P123    |  Project1       |  Phase2   | Develop
P123    |  Project1       |  Phase3   | Deploy
P123    |  Project1       |  Phase4   | Close

P432    |  Project3       |  Phase1   | Select
P432    |  Project3       |  Phase2   | Develop
P432    |  Project3       |  Phase3   | Deploy
P432    |  Project3       |  Phase4   | Close

P452    |  Project2       |  Phase1   | Select
P452    |  Project2       |  Phase2   | Develop
P452    |  Project2       |  Phase3   | Deploy
P452    |  Project2       |  Phase4   | Close
user_21497180
Related