We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
- Prepare
- SQL
- Advanced Join
- SQL Project Planning
- Discussions
SQL Project Planning
SQL Project Planning
Sort by
recency
|
1313 Discussions
|
Please Login in order to post a comment
The easiest way to think about this is to identify a pattern in the original output table (projects). If you look at the table, you can identify that the start_date of a particular project doesn't appear in the end_date column, and the end_date of a particular project doesn't appear in the start_date column. Once you figure this out, you can rank them, join and you are done!
` with in_start_not_in_end as ( select distinct start_date, row_number() over(order by start_date) as rnk from projects where start_date not in (select distinct end_date from projects)
),
in_end_not_in_start as ( select distinct end_date, row_number() over(order by end_date) as rnk from projects where end_date not in (select distinct start_date from projects) ),
w_date_delta as ( select start_date, end_date, datediff(day, start_date, end_date) as delta from in_start_not_in_end s left join in_end_not_in_start e on s.rnk = e.rnk )
select start_date, end_date
from w_date_delta order by delta, start_date
oracle nested query : select start_date, end_date from (select project_id,min(start_date) as start_date, max(end_date) as end_date, max(end_date) - min(start_date) completion_date from ( select task_id, start_date,end_date , sum(group_flag) over (order by start_date) as project_id from (select task_id, start_date,end_date,datediff, case when datediff <= 1 then 0 else 1 end as group_flag from (select task_id, start_date, end_date, lag(end_date,1) over (order by end_date) as lag_date, end_date - lag(end_date,1, end_date) over (order by end_date) as datediff from projects) ) ) group by project_id )order by completion_date,start_date;
with t1 as (Select start_date, row_number() over(order by (SELECT 0)) r1 From Projects Where start_date not in (select end_date from projects)),
t2 as (Select end_date, row_number() over(order by (SELECT 0)) r2 From Projects Where end_date not in (select start_date from projects))
select start_date, end_date from t1, t2 where t1.r1 = t2.r2 order by DATEDIFF(DAY,start_date, end_date), start_date