Search code examples
sqlleft-joinself-join

SQL left join with the same column

I am currently trying to query a table such as this where the phases of a project and the dates of those phases are all in the same columns. The table is as follows:

Project ID Phase Date
PR001 Create 1/1/2022
PR001 Approve 1/2/2022
PR001 Implement 1/3/2022
PR001 Close 1/4/2022
PR002 Create 1/2/2022
PR003 Create 1/5/2022
PR003 Approve 1/7/2022
PR003 Implement 1/8/2022
PR004 Create 1/3/2022
PR004 Approve 1/6/2022
PR004 Implement 1/7/2022
PR004 Close 1/8/2022
PR005 Create 1/9/2022
PR005 Approve 1/9/2022
PR005 Implement 1/10/2022
PR005 Close 1/11/2022

I am hoping to query the table to get a list of each project with it's date approved and closed. If the project has not reached the approve phase yet, then it will not show in the table. If it has reached approve or implement but not close yet, it will show in the table the approve date and Null for the close date such as this:

Project ID Approve Close
PR001 1/2/2022 1/4/2022
PR003 1/7/2022 Null
PR004 1/6/2022 1/8/2022
PR005 1/9/2022 1/11/2022

I am trying to left join the table to itself, but when I do this I get duplicate rows of the project ID where the approve date is there and the closed date is null, then the approve date is null but the close date is there. Any help would be greatly appreciated!

Solution

  • Here's a solution that provides all the info while eliminating cases where Approve is null

    select   [Project ID]
        ,max(case Phase when 'Approve' then [Date] end) as  Approve
        ,max(case Phase when 'Close'   then [Date] end) as  [Close]
from     t
group by [Project ID]
having   max(case Phase when 'Approve' then [Date] end) is not null
    Project ID Approve Close
    PR001 2022-01-02 00:00:00.000 2022-01-04 00:00:00.000
    PR003 2022-01-07 00:00:00.000 null
    PR004 2022-01-06 00:00:00.000 2022-01-08 00:00:00.000
    PR005 2022-01-09 00:00:00.000 2022-01-11 00:00:00.000

    Fiddle