SQL Project Planning

Sort by

recency

|

1554 Discussions

|

  • + 0 comments

    MS SQL

    SELECT StartDateTable.Start_Date, EndDateTable.End_Date
    FROM (
        SELECT Start_Date, ROW_NUMBER() OVER (ORDER BY Start_Date) as rowN
        FROM Projects 
        WHERE Start_Date NOT IN 
            (SELECT End_Date FROM Projects)
    ) AS StartDateTable
    JOIN (
        SELECT ROW_NUMBER() OVER (ORDER BY End_Date) as rowN, End_Date 
        FROM Projects 
        WHERE End_Date NOT IN 
            (SELECT Start_Date FROM Projects)
    ) AS EndDateTable
    ON StartDateTable.rowN = EndDateTable.rowN
    ORDER BY DATEDIFF(day, StartDateTable.Start_Date, EndDateTable.End_Date)
        , StartDateTable.Start_Date
    
  • + 0 comments

    WITH cte1 AS ( SELECT start_date, ROW_NUMBER() OVER (ORDER BY start_date) AS rn1 FROM projects WHERE start_date NOT IN ( SELECT end_date FROM projects ) ), cte2 AS ( SELECT end_date, ROW_NUMBER() OVER (ORDER BY end_date) AS rn2 FROM projects WHERE end_date NOT IN ( SELECT start_date FROM projects ) ) SELECT cte1.start_date, cte2.end_date FROM cte1 JOIN cte2 ON cte1.rn1 = cte2.rn2 ORDER BY DATEDIFF(cte2.end_date, cte1.start_date), cte1.start_date;

  • + 0 comments

    MS-SQL

    WITH seq AS ( SELECT Start_Date, End_Date, DATEADD(day, -ROW_NUMBER() OVER (ORDER BY End_Date), End_Date) AS grp, -ROW_NUMBER() OVER (ORDER BY End_Date) AS dd FROM Projects ), projects AS ( SELECT MIN(Start_Date) AS start_date, MAX(End_Date) AS end_date, DATEDIFF(day, MIN(Start_Date), MAX(End_Date)) AS duration_days FROM seq GROUP BY grp ) SELECT start_date, end_date FROM projects ORDER BY duration_days ASC, start_date ASC;

  • + 0 comments

    with cte1 as ( select task_id, start_date, end_date, sum(break_flag) over (order by end_Date) grp from
    ( select task_id, start_date, end_Date, lag(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date) as next_end_Date --decode((to_date(end_date,'YYYY-MM-DD') + 1) = (lead(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date)),true,1,0), , case when (to_date(start_date,'YYYY-MM-DD') = (lag(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date))) then 0 when (lag(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date)) is null then 0 else 1 end break_flag from Projects order by end_Date) ) select min(start_date), max(end_date) from cte1 group by grp order by (to_char(min(end_date),'YYYYMMDD') - to_char(max(start_date),'YYYYMMDD')) desc, min(end_date)

  • + 0 comments
    mysql:
    WITH raw1 as(SELECT 
    															Task_ID, 
    															Start_Date, 
    															End_Date, 
    															LAG(End_Date) OVER(ORDER BY End_Date) as E1 
    												FROM projects), 
    raw2 AS(SELECT 
    											Task_ID, 
    											Start_Date, 
    											End_Date, 
    											(CASE WHEN E1=Start_Date AND End_Date-E1=1 THEN 1 ELSE 0 END) AS binary1 
    									FROM raw1 
    									GROUP BY 1,2,3), 
    raw3 AS(SELECT 
    													task_ID, 
    													Start_Date, 
    													End_Date, 
    													SUM(CASE WHEN binary1 = 0 THEN 1 ELSE 0 END) OVER (ORDER BY Task_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS project_label FROM raw2) SELECT MIN(Start_Date),MAX(End_Date) FROM raw3 GROUP BY project_label ORDER BY COUNT(project_label) ASC, Start_Date ASC