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

|

1517 Discussions

|

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

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

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