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

Sort by

recency

|

1515 Discussions

|

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

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

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

  • ameenhydermirza
     + 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

  • tamphan_31211021
     + 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