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
Advertisement
Answer
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
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