SQL Project Planning

Sort by

recency

|

1515 Discussions

|

  • + 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;
    
  • + 0 comments

    with NumberedTasks as ( Select *, row_number() over (order by Task_ID) as rn from Projects ),

    grouptask as ( select *, date_sub(Start_Date, interval rn day) as grp from ) SELECT MIN(Start_Date) AS Start_Date, MAX(End_Date) AS End_Date, DATEDIFF(MAX(End_Date), MIN(Start_Date)) + 1 AS Project_Duration FROM GroupedTasks GROUP BY grp ORDER BY Project_Duration, Start_Date;

  • + 0 comments

    Used Oracle:

    WITH proj_prev_dates AS 
    (
        SELECT Task_ID, Start_Date, End_Date, 
            LAG(End_Date) OVER(ORDER BY Start_Date) AS prev_date
        FROM Projects
        ),
    proj_flags AS
    (
        SELECT 
            ppd.*,
            CASE 
                WHEN prev_date IS NULL THEN 1
                WHEN prev_date = Start_Date THEN 0 
                ELSE 1
            END as proj_flag  
        FROM proj_prev_dates ppd
        ),
    proj_labelled AS 
    (
        SELECT pf.*,
            SUM(proj_flag) OVER(ORDER BY Start_Date) AS proj_ID
        FROM proj_flags pf  
        )
    SELECT min_date, max_date
    FROM
    (   SELECT 
            proj_ID, 
            MIN(Start_Date) AS min_date, 
            MAX(End_Date) AS max_date,
            MAX(End_Date) - MIN(Start_Date) AS  time_taken
        FROM proj_labelled
        GROUP BY proj_ID
        )
    ORDER BY time_taken, min_date;
    
  • + 0 comments

    /* Enter your query here. */ with cte as (select start_date, end_date, - row_number() over (order by end_date) as date_lag, end_date - row_number() over (order by end_date) as date_lag_2 from projects order by start_date asc)

    , cte2 as ( select min(start_date) min_date, max(end_date) max_date, count(*) total_days from cte group by date_lag_2 )

    select min_date, max_date from cte2 order by total_days asc, min_date asc

  • + 0 comments

    `with all_date as ( select Start_Date as date_ from Projects union all select End_Date as date_ from Projects ) , table_data_point as ( select date_, count(date_) as number_dup from all_date group by 1 having count(date_) = 1 ) , table_add_key_point as ( select date_, case when date_ in (select Start_Date as date_ from Projects) then 'Start' else 'End' end as Segment from table_data_point ) , start_table as ( select date_, row_number() over(order by date_) as rk from table_add_key_point where 1=1 and Segment = 'Start' ) , end_table as ( select date_, row_number() over(order by date_) as rk from table_add_key_point where 1=1 and Segment = 'End' ) select start_table.date_ as start_date, end_table.date_ as end_date from start_table join end_table using(rk) order by datediff(end_table.date_, start_table.date_) asc, 1 asc