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
|
1515 Discussions
|
Please Login in order to post a comment
This is MySQL version how this can be done
This is explanation
with NumberedTasks as ( Select *, row_number() over (order by Task_ID) as rn from Projects ),
grouptask as ( select *, date_sub(Start_Date, interval rn day) as grp from ) SELECT MIN(Start_Date) AS Start_Date, MAX(End_Date) AS End_Date, DATEDIFF(MAX(End_Date), MIN(Start_Date)) + 1 AS Project_Duration FROM GroupedTasks GROUP BY grp ORDER BY Project_Duration, Start_Date;
Used Oracle:
/* Enter your query here. */ with cte as (select start_date, end_date, - row_number() over (order by end_date) as date_lag, end_date - row_number() over (order by end_date) as date_lag_2 from projects order by start_date asc)
, cte2 as ( select min(start_date) min_date, max(end_date) max_date, count(*) total_days from cte group by date_lag_2 )
select min_date, max_date from cte2 order by total_days asc, min_date asc
`with all_date as ( select Start_Date as date_ from Projects union all select End_Date as date_ from Projects ) , table_data_point as ( select date_, count(date_) as number_dup from all_date group by 1 having count(date_) = 1 ) , table_add_key_point as ( select date_, case when date_ in (select Start_Date as date_ from Projects) then 'Start' else 'End' end as Segment from table_data_point ) , start_table as ( select date_, row_number() over(order by date_) as rk from table_add_key_point where 1=1 and Segment = 'Start' ) , end_table as ( select date_, row_number() over(order by date_) as rk from table_add_key_point where 1=1 and Segment = 'End' ) select start_table.date_ as start_date, end_table.date_ as end_date from start_table join end_table using(rk) order by datediff(end_table.date_, start_table.date_) asc, 1 asc