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.
-- Get rows that mark the beginning of a new project
WITH Project_Starts AS (
SELECT Start_Date,
ROW_NUMBER() OVER (ORDER BY Start_Date) AS Num
FROM Projects
WHERE Start_Date NOT IN (
SELECT End_Date
FROM Projects
)
),
-- Get rows that mark the end of a project
Project_Ends AS (
SELECT End_Date,
ROW_NUMBER() OVER (ORDER BY End_Date) AS Num
FROM Projects
WHERE End_Date NOT IN (
SELECT Start_Date
FROM Projects
)
)
-- Join the start and end points to create a complete list of projects
SELECT
PS.Start_Date,
PE.End_Date
FROM
Project_Starts PS
JOIN
Project_Ends PE
ON
PS.Num = PE.Num
-- Order the projects by duration (shortest first) and then by start date
ORDER BY
DATEDIFF(DAY, PS.Start_Date, PE.End_Date),
PS.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 →
MS SQL SERVER
-- Get rows that mark the beginning of a new project WITH Project_Starts AS ( SELECT Start_Date, ROW_NUMBER() OVER (ORDER BY Start_Date) AS Num FROM Projects WHERE Start_Date NOT IN ( SELECT End_Date FROM Projects ) ), -- Get rows that mark the end of a project Project_Ends AS ( SELECT End_Date, ROW_NUMBER() OVER (ORDER BY End_Date) AS Num FROM Projects WHERE End_Date NOT IN ( SELECT Start_Date FROM Projects ) ) -- Join the start and end points to create a complete list of projects SELECT PS.Start_Date, PE.End_Date FROM Project_Starts PS JOIN Project_Ends PE ON PS.Num = PE.Num -- Order the projects by duration (shortest first) and then by start date ORDER BY DATEDIFF(DAY, PS.Start_Date, PE.End_Date), PS.Start_Date;