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 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
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 →
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