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.
SELECT T1.Start_Date,T2.End_Date FROM
(
SELECT Start_Date,ROW_NUMBER() OVER (ORDER BY Start_Date) RN FROM Projects
WHERE Start_Date NOT IN
(SELECT END_Date FROM Projects)
) AS T1 INNER JOIN
(
SELECT End_Date,ROW_NUMBER() OVER (ORDER BY End_Date) RN FROM Projects
WHERE End_Date NOT IN
(SELECT Start_Date FROM Projects)
) AS T2 ON T1.RN = T2.RN
ORDER BY DATEDIFF(Day,T1.Start_Date,T2.End_Date),T1.Start_Date
For everyone:
The way to solve this problem is to find Start_date that is not in End_Date and End_Date that is not in Start_Date.
Why? because if a Start_date is not in EndDate, it means it is a new project. And if a End_Date is not in Start_Date, it means this End_Date is the End_Date of a project.
We Gerenate a row number and Order by start_date so that it can match the Project's EndDate.
Same logic, We Gerenate a row number and Order by End_date so that it can match the Project's StartDate.
If a rownumber from Start_Date match the rownumber from End_Date,
they must be the start_Date and End_Date in the same project.
think about it.
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 →
For MS SQL users:
SELECT T1.Start_Date,T2.End_Date FROM ( SELECT Start_Date,ROW_NUMBER() OVER (ORDER BY Start_Date) RN FROM Projects WHERE Start_Date NOT IN (SELECT END_Date FROM Projects) ) AS T1 INNER JOIN (
SELECT End_Date,ROW_NUMBER() OVER (ORDER BY End_Date) RN FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)
) AS T2 ON T1.RN = T2.RN ORDER BY DATEDIFF(Day,T1.Start_Date,T2.End_Date),T1.Start_Date
For everyone:
The way to solve this problem is to find Start_date that is not in End_Date and End_Date that is not in Start_Date.
Why? because if a Start_date is not in EndDate, it means it is a new project. And if a End_Date is not in Start_Date, it means this End_Date is the End_Date of a project.
We Gerenate a row number and Order by start_date so that it can match the Project's EndDate.
Same logic, We Gerenate a row number and Order by End_date so that it can match the Project's StartDate.
If a rownumber from Start_Date match the rownumber from End_Date, they must be the start_Date and End_Date in the same project.
think about it.