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
|
1463 Discussions
|
Please Login in order to post a comment
with cte as( select min(start_date) as min_start_date from projects),
TaskGroups AS ( SELECT Task_ID, Start_Date, End_Date, ROW_NUMBER() OVER (ORDER BY Start_Date) - DATEDIFF(DAY, (select min_start_date from cte), Start_Date) AS GroupID FROM Projects ), cte2 as( SELECT MIN(Start_Date) AS Project_Start_Date, MAX(End_Date) AS Project_End_Date, COUNT(*) AS Project_Days FROM TaskGroups GROUP BY GroupID
)
select Project_Start_Date, Project_end_Date from cte2 order by project_days, project_start_date
SET @project_id = 0; SET @prev_end_date = NULL; SELECT MIN(Start_Date) AS Project_Start, MAX(End_Date) AS Project_End FROM ( SELECT Start_Date, End_Date, @project_id := IF(Start_Date = @prev_end_date, @project_id, @project_id + 1) AS Project_ID, @prev_end_date := End_Date FROM Projects, (SELECT @project_id := 0, @prev_end_date := NULL) AS vars ORDER BY Start_Date ) AS ProjectGroups GROUP BY Project_ID ORDER BY DATEDIFF(MAX(End_Date), MIN(Start_Date)), MIN(Start_Date);
--MS SQL solution for beginer WITH cte1 AS ( SELECT start_date, row_number() OVER(ORDER BY start_date) AS rowNumber 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 rowNumber FROM projects WHERE end_date NOT IN (SELECT start_date FROM projects) ) SELECT cte1.start_date, cte2.end_date FROM cte1 INNER JOIN cte2 ON cte1.rowNumber = cte2.rowNumber ORDER BY datediff(day, cte1.start_date, cte2.end_date) , cte1.start_date;