Unable to get the desired output for Project Planning SQL Problem?

Question

The project planning problem is as below:

Input:

projects (task_id, start_date,end_date)
                 1, 1-1-2023,  2-1-2023
                 2, 2-1-2023,  3-1-2023
                 3, 3-1-2023,  4-1-2023
                 4, 7-1-2023,  8-1-2023
                 5, 10-1-2023, 11-1-2023
                 6, 11-1-2023, 12-1-2023

Desired Output:

                 1-1-2023,  4-1-2023
                 7-1-2023,  8-1-2023
                 10-1-2023, 12-1-2023

Basically, the idea is to have date range for project completion as long as the dates are continuous theyll fall in same range else new_range etc..

I tried below sql but unable to get the desired results (missing some condition to get different minimum start_dates even tried with group by end_Date )

select
(SELECT 
min(case when p.start_date<p1.start_date  then p.start_date END) from dual),
p1.end_date 
from projects p
cross join 
(Select distinct start_date,end_date from projects  where 
 end_date not in (Select start_date from projects))
 p1
 order by 1,2;

See my answer for similar question https://stackoverflow.com/questions/76780313/postgres-detect-active-and-inactive-periods/76781193#76781193 — ValNik, Jul 28 '23 at 14:05
**Hint** : [gaps and islands in Oracle](https://stackoverflow.com/questions/tagged/gaps-and-islands%2boracle?tab=Frequent) — Barbaros Özhan, Jul 28 '23 at 14:50
Thanks Barbaros, helpful hint but i did it the other way as i think this was simpler case of gaps and islands wherein the representation i suppose was desired still ill work on gaps and islands as that approach is much better — Himanshu, Jul 30 '23 at 10:28

score 1 · Accepted Answer · answered Jul 28 '23 at 20:52

Finally, was able to do by below:

select
start_date,end_date from 
(select start_date,rank() over (order by start_date) rn from projects 
where start_date not in (Select end_Date from projects )
) a
join
(select end_date, rank() over (order by end_date) rn  from projects 
where end_date not in (SELECT start_date from projects)
) b on
 a.rn=b.rn
 order by abs(start_date-end_date),start_date
 ;

Unable to get the desired output for Project Planning SQL Problem?

1 Answers1