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.
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);
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
SQL Project Planning
You are viewing a single comment's thread. Return to all comments →
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);