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
+ 0 comments Here is my take on Mysql. Any advice is appreciated.
/* This outer query is only for ordering by project duration */ SELECT P3.sd,P3.ed FROM (SELECT MIN(P1.Start_Date) AS sd , P2.End_Date AS ed FROM Projects AS P1 INNER JOIN Projects AS P2 ON p2.End_Date NOT IN (SELECT Start_Date From Projects) /*we exclude the end dates that exists on Start_Date which means they are part of the same project*/ AND /*Once we've found an end date not same as any of the start dates we check backwards how many other cols there are within the same time span count them first if the number equals to the day difference that means they are actually linked. Use "group by" End_date with MIN() to find the earliest start date there is that is linked */ (SELECT count(*) From Projects WHERE Start_Date BETWEEN P1.Start_Date AND P2.End_Date ) = DATEDIFF(P2.End_Date,P1.Start_Date) GROUP BY P2.End_Date) AS P3 ORDER by DATEDIFF(P3.ed, P3.sd), P3.sd ;
+ 0 comments WITH difference AS ( SELECT start_date, end_date, @group := IF(@prev_end = start_date, @group, @group + 1) AS grp, @prev_end := end_date FROM projects, (SELECT @prev_end := NULL, @group := 0) vars ORDER BY 1 ) SELECT MIN(start_date) AS start_date, MAX(end_date) AS end_date FROM difference GROUP BY grp ORDER BY count(grp), 1;
+ 0 comments select min(Start_Date), max(End_date) from ( select *, day(End_date) - row_number() over (order by end_date) dif from Projects) x group by dif order by datediff(day,min(Start_Date), max(End_date)), min(Start_Date);
+ 0 comments mysql solution
select start_date, end_date from (select start_date, row_number() over(order by start_date) as x from projects where start_date not in (select end_date from projects)) as a join (select end_date, row_number() over(order by end_date) as y from projects where end_date not in (select start_date from projects)) as b on a.x = b.y order by datediff(end_date, start_date), 1
+ 0 comments select start_date,end_date from
(select start_date,x from (select start_date,row_number() over(order by start_date) x from projects where start_date not in (select end_date from projects)) e ) a join
(select end_date,y from (select end_date,row_number() over(order by end_date) y from projects where end_date not in (select start_date from projects)) t) b on a.x=b.y
order by Datediff(end_date, start_date), start_date
Load more conversations
Sort 935 Discussions, By:
Please Login in order to post a comment