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.
- Prepare
- SQL
- Advanced Join
- SQL Project Planning
- Discussions
SQL Project Planning
SQL Project Planning
Sort by
recency
|
1254 Discussions
|
Please Login in order to post a comment
MySQL query
MS SQL SERVER
-- Get rows that mark the beginning of a new project WITH Project_Starts AS ( SELECT Start_Date, ROW_NUMBER() OVER (ORDER BY Start_Date) AS Num FROM Projects WHERE Start_Date NOT IN ( SELECT End_Date FROM Projects ) ), -- Get rows that mark the end of a project Project_Ends AS ( SELECT End_Date, ROW_NUMBER() OVER (ORDER BY End_Date) AS Num FROM Projects WHERE End_Date NOT IN ( SELECT Start_Date FROM Projects ) ) -- Join the start and end points to create a complete list of projects SELECT PS.Start_Date, PE.End_Date FROM Project_Starts PS JOIN Project_Ends PE ON PS.Num = PE.Num -- Order the projects by duration (shortest first) and then by start date ORDER BY DATEDIFF(DAY, PS.Start_Date, PE.End_Date), PS.Start_Date;
MS SQL SERVER
Select PS.Start_Date, PE.End_Date From (Select Start_Date, ROW_NUMBER() Over(Order by Start_Date) As Num From Projects Where Start_Date Not in (Select End_Date From Projects)) PS Join (Select End_Date, ROW_NUMBER() Over(Order by End_Date) As Num From Projects Where End_Date Not in (Select Start_Date From Projects)) PE On PS.Num = PE.Num Order by Convert(Int, CONVERT(datetime,End_Date)) - Convert(Int, CONVERT(datetime,Start_Date)), Start_Date;
WITH ConsecutiveDates AS ( SELECT Start_Date, End_Date, DATEADD(day, -ROW_NUMBER() OVER (ORDER BY Start_Date), End_Date) AS GroupStart FROM Projects ) SELECT MIN(Start_Date) AS Start_Date, MAX(End_Date) AS End_Date FROM ConsecutiveDates GROUP BY GroupStart ORDER BY DATEDIFF(day, MIN(Start_Date), MAX(End_Date)) + 1,Start_Date
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