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 cte1 as (
select task_id, start_date, end_date,
sum(break_flag) over (order by end_Date) grp
from
( select task_id, start_date, end_Date,
lag(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date) as next_end_Date
--decode((to_date(end_date,'YYYY-MM-DD') + 1) = (lead(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date)),true,1,0),
, case when (to_date(start_date,'YYYY-MM-DD') = (lag(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date)))
then 0
when (lag(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date)) is null
then 0
else 1
end break_flag
from Projects
order by end_Date)
) select
min(start_date), max(end_date)
from cte1 group by grp
order by (to_char(min(end_date),'YYYYMMDD') - to_char(max(start_date),'YYYYMMDD')) desc, min(end_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 cte1 as ( select task_id, start_date, end_date, sum(break_flag) over (order by end_Date) grp from
( select task_id, start_date, end_Date, lag(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date) as next_end_Date --decode((to_date(end_date,'YYYY-MM-DD') + 1) = (lead(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date)),true,1,0), , case when (to_date(start_date,'YYYY-MM-DD') = (lag(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date))) then 0 when (lag(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date)) is null then 0 else 1 end break_flag from Projects order by end_Date) ) select min(start_date), max(end_date) from cte1 group by grp order by (to_char(min(end_date),'YYYYMMDD') - to_char(max(start_date),'YYYYMMDD')) desc, min(end_date)