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
|
1519 Discussions
|
Please Login in order to post a comment
Horrible problem. What if there are two different tasks with the same start and end date?
WITH ordered_tasks AS ( SELECT Task_ID, Start_Date, End_Date, ROW_NUMBER() OVER (ORDER BY Start_Date) AS rn FROM Projects ), grouped_tasks AS ( SELECT *, DATE_SUB(Start_Date, INTERVAL rn DAY) AS grp FROM ordered_tasks ), project_ranges AS ( SELECT MIN(Start_Date) AS project_start, MAX(End_Date) AS project_end, DATEDIFF(MAX(End_Date), MIN(Start_Date)) AS duration FROM grouped_tasks GROUP BY grp ) SELECT project_start, project_end FROM project_ranges ORDER BY duration, project_start;
with t1 as( select start_date, end_date, row_number() over (order by end_date) as rn from projects)
select sd, ed from ( select min(start_date) as sd, max(end_date) as ed, count(*) as cnt from ( select start_date, end_date, date_sub(end_date, interval rn day) as flag_date from t1) a group by flag_date ) b order by cnt, sd
with CTE1 as ( select start_date, ROW_NUMBER() over (order by start_date) as str_rank from projects where start_date not in (select end_date from projects) ) , CTE2 as ( select end_date, ROW_NUMBER() over (order by end_date) as end_rank from projects where end_date not in (select start_date from projects) )
select start_date, end_date from CTE1, CTE2 where str_rank = end_rank order by Datediff(day,start_date,end_date), start_date
This is MySQL version how this can be done
This is explanation