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

|

1519 Discussions

|

  • dontakeman1
     + 0 comments

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

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

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