SQL Project Planning

  • + 0 comments
    WITH ProjectGroups AS (
        SELECT *, 
               DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY Start_Date), Start_Date) AS grp
        FROM Projects
    ), 
    GroupedProjects AS (
        SELECT 
            MIN(Start_Date) AS project_start,
            MAX(End_Date) AS project_end,
            DATEDIFF(DAY, MIN(Start_Date), MAX(End_Date)) AS project_days
        FROM ProjectGroups
        GROUP BY grp
    )
    
    SELECT project_start, project_end
    FROM GroupedProjects
    ORDER BY project_days ASC, project_start;