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.
  1. Prepare
  2. SQL
  3. Advanced Join
  4. SQL Project Planning
  5. Discussions

SQL Project Planning

  • dorirgob
     + 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 ;