SQL Project Planning

Sort by

recency

|

1519 Discussions

|

  • + 0 comments

    Horrible problem. What if there are two different tasks with the same start and end date?

  • + 0 comments

    WITH ordered_tasks AS ( SELECT Task_ID, Start_Date, End_Date, ROW_NUMBER() OVER (ORDER BY Start_Date) AS rn FROM Projects ), grouped_tasks AS ( SELECT *, DATE_SUB(Start_Date, INTERVAL rn DAY) AS grp FROM ordered_tasks ), project_ranges AS ( SELECT MIN(Start_Date) AS project_start, MAX(End_Date) AS project_end, DATEDIFF(MAX(End_Date), MIN(Start_Date)) AS duration FROM grouped_tasks GROUP BY grp ) SELECT project_start, project_end FROM project_ranges ORDER BY duration, project_start;

  • + 0 comments

    with t1 as( select start_date, end_date, row_number() over (order by end_date) as rn from projects)

    select sd, ed from ( select min(start_date) as sd, max(end_date) as ed, count(*) as cnt from ( select start_date, end_date, date_sub(end_date, interval rn day) as flag_date from t1) a group by flag_date ) b order by cnt, sd

  • + 0 comments

    with CTE1 as ( select start_date, ROW_NUMBER() over (order by start_date) as str_rank 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 end_rank from projects where end_date not in (select start_date from projects) )

    select start_date, end_date from CTE1, CTE2 where str_rank = end_rank order by Datediff(day,start_date,end_date), start_date

  • + 0 comments

    This is MySQL version how this can be done

    SELECT 
        T1.Start_Date,
        T2.End_Date
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY Start_Date ASC) AS r,
        Start_Date
        FROM Projects
        WHERE (Start_Date NOT IN (SELECT End_Date FROM Projects))
    ) AS T1
    LEFT JOIN 
        (SELECT ROW_NUMBER() OVER (ORDER BY End_Date ASC) AS r,
        End_Date
        FROM Projects
        WHERE (End_Date NOT IN (SELECT Start_Date FROM Projects))
    ) AS T2 ON T1.r=T2.r
    ORDER BY DATEDIFF(T2.End_Date, T1.Start_Date) ASC, T1.Start_Date ASC
    

    This is explanation

    -- Select the unmatched Start_Date and End_Date pairs
    SELECT 
        T1.Start_Date,     -- Start date of a project (not matched to any end date)
        T2.End_Date        -- End date of a project (not matched to any start date)
    FROM (
        -- Subquery T1: Get all Start_Dates that are not listed as End_Dates
        SELECT 
            ROW_NUMBER() OVER (ORDER BY Start_Date ASC) AS r,  -- Assign a row number for matching
            Start_Date
        FROM Projects
        WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)  -- Filter: unmatched start dates
    ) AS T1
    
    -- LEFT JOIN to match each unmatched Start_Date with an unmatched End_Date
    LEFT JOIN (
        -- Subquery T2: Get all End_Dates that are not listed as Start_Dates
        SELECT 
            ROW_NUMBER() OVER (ORDER BY End_Date ASC) AS r,  -- Assign a row number for matching
            End_Date
        FROM Projects
        WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)  -- Filter: unmatched end dates
    ) AS T2 
    ON T1.r = T2.r  -- Join rows by row number (to pair start and end dates in order)
    
    -- Order results by shortest duration first, then by Start_Date
    ORDER BY 
        DATEDIFF(T2.End_Date, T1.Start_Date) ASC,  -- Sort by number of days between Start and End
        T1.Start_Date ASC;