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 ROW_NUMBER() over (order by start_date ) as rn1, start_date
from projects
where start_date not in (select distinct end_date from projects)
),
cte2 as
(
select ROW_NUMBER() over (order by end_date ) as rn2, end_date
from projects
where end_date not in (select distinct start_date from projects)
)
select t.start_date, t.end_date from
(
select start_date, end_date, datediff(day, start_date, end_date) as date_
from
cte1
inner join
cte2
on rn1 = rn2
)t
order by t.date_ asc, t.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 SERVER
with cte1 as ( select ROW_NUMBER() over (order by start_date ) as rn1, start_date from projects where start_date not in (select distinct end_date from projects) ),
cte2 as ( select ROW_NUMBER() over (order by end_date ) as rn2, end_date from projects where end_date not in (select distinct start_date from projects) )
select t.start_date, t.end_date from ( select start_date, end_date, datediff(day, start_date, end_date) as date_ from cte1 inner join cte2 on rn1 = rn2 )t order by t.date_ asc, t.start_date asc