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

  • fa88af
     + 0 comments
    WITH tasks_with_flag AS (
  SELECT *,
         LAG(End_Date) OVER (ORDER BY Start_Date) as prev_end
  FROM Projects
),
tasks_with_project AS (
  SELECT *,
         SUM(CASE WHEN prev_end IS NULL OR Start_Date != prev_end THEN 1 ELSE 0 END)
             OVER (ORDER BY Start_Date) AS project_id
  FROM tasks_with_flag
)

  SELECT
    MIN(Start_Date) as project_start,
    MAX(End_Date) as project_end
  FROM tasks_with_project
  GROUP BY project_id
  ORDER BY DATEDIFF(DAY, MIN(Start_Date), MAX(End_Date)), project_start
    1. with tasks_with_flag - adds new column prev_end which shows end_date of previous row
    2. with tasks_with_project - if prev_end is equal to start_date, that means it is same project. if same project make new column 0 if different project make new column 1. Now cumulative sum will increase by 1 if there is a new project. hence giving new number to each project
    3. then finally showing min start date and max end date for each project . And sorting by no of days and start_date