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 task_id,start_date,end_date,
lag(end_date) over(order by start_date) as prev_end
from Projects)
,daysdifference as (select *,(case when datediff(end_date,prev_end)=1 then 0
else 1
end) as days_diff
from cte
)
, full_model as (SELECT *,
SUM(days_diff) OVER (ORDER BY start_date) AS gap_group
FROM daysdifference
)
, new_full_model as ( SELECT
MIN(start_date) AS project_start_date,
MAX(end_date) AS project_end_date,
DATEDIFF(MAX(end_date), MIN(start_date)) + 1 AS duration
FROM full_model
GROUP BY gap_group
ORDER BY duration, project_start_date
)
select project_start_date,project_end_date
1. from new_full_modelhttps://)
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 task_id,start_date,end_date, lag(end_date) over(order by start_date) as prev_end from Projects) ,daysdifference as (select *,(case when datediff(end_date,prev_end)=1 then 0 else 1 end) as days_diff from cte ) , full_model as (SELECT *, SUM(days_diff) OVER (ORDER BY start_date) AS gap_group FROM daysdifference ) , new_full_model as ( SELECT MIN(start_date) AS project_start_date, MAX(end_date) AS project_end_date, DATEDIFF(MAX(end_date), MIN(start_date)) + 1 AS duration FROM full_model GROUP BY gap_group ORDER BY duration, project_start_date ) select project_start_date,project_end_date 1. from new_full_modelhttps://)