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.
WITH seq AS (
SELECT
Start_Date,
End_Date,
DATEADD(day, -ROW_NUMBER() OVER (ORDER BY End_Date), End_Date) AS grp,
-ROW_NUMBER() OVER (ORDER BY End_Date) AS dd
FROM Projects
),
projects AS (
SELECT
MIN(Start_Date) AS start_date,
MAX(End_Date) AS end_date,
DATEDIFF(day, MIN(Start_Date), MAX(End_Date)) AS duration_days
FROM seq
GROUP BY grp
)
SELECT start_date, end_date
FROM projects
ORDER BY duration_days ASC, start_date ASC;
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 →
MS-SQL
WITH seq AS ( SELECT Start_Date, End_Date, DATEADD(day, -ROW_NUMBER() OVER (ORDER BY End_Date), End_Date) AS grp, -ROW_NUMBER() OVER (ORDER BY End_Date) AS dd FROM Projects ), projects AS ( SELECT MIN(Start_Date) AS start_date, MAX(End_Date) AS end_date, DATEDIFF(day, MIN(Start_Date), MAX(End_Date)) AS duration_days FROM seq GROUP BY grp ) SELECT start_date, end_date FROM projects ORDER BY duration_days ASC, start_date ASC;