SQL Project Planning

Sort by

recency

|

1463 Discussions

|

  • + 0 comments

    with cte as( select min(start_date) as min_start_date from projects),

    TaskGroups AS ( SELECT Task_ID, Start_Date, End_Date, ROW_NUMBER() OVER (ORDER BY Start_Date) - DATEDIFF(DAY, (select min_start_date from cte), Start_Date) AS GroupID FROM Projects ), cte2 as( SELECT MIN(Start_Date) AS Project_Start_Date, MAX(End_Date) AS Project_End_Date, COUNT(*) AS Project_Days FROM TaskGroups GROUP BY GroupID

    )

    select Project_Start_Date, Project_end_Date from cte2 order by project_days, project_start_date

  • + 0 comments

    SET @project_id = 0; SET @prev_end_date = NULL; SELECT MIN(Start_Date) AS Project_Start, MAX(End_Date) AS Project_End FROM ( SELECT Start_Date, End_Date, @project_id := IF(Start_Date = @prev_end_date, @project_id, @project_id + 1) AS Project_ID, @prev_end_date := End_Date FROM Projects, (SELECT @project_id := 0, @prev_end_date := NULL) AS vars ORDER BY Start_Date ) AS ProjectGroups GROUP BY Project_ID ORDER BY DATEDIFF(MAX(End_Date), MIN(Start_Date)), MIN(Start_Date);

  • + 0 comments
    WITH ProjectGroups AS (
       SELECT 
            Task_ID,
            Start_Date,
            End_Date,
            DATE_SUB(Start_Date, INTERVAL ROW_NUMBER() OVER (ORDER BY Start_Date) DAY) AS ProjectGroup
        FROM Projects
    ), ProjectDates AS (
        SELECT 
            MIN(Start_Date) AS startDate,
            MAX(End_Date) AS endDate,
            COUNT(*) AS total_time
        FROM ProjectGroups 
        GROUP BY ProjectGroup
    )SELECT 
        startDate,
        endDate
        FROM ProjectDates
        ORDER BY total_time ASC,startDate ASC;
    
  • + 0 comments
        SELECT 
            Task_ID, 
            Start_Date, 
            End_Date, 
            DATE_SUB(Start_Date, INTERVAL ROW_NUMBER() OVER (ORDER BY Start_Date) DAY) AS PGroup
        FROM Projects
    )
    SELECT 
        MIN(Start_Date) AS PStart, 
        MAX(End_Date) AS PEnd
    FROM PSEQ
    GROUP BY PGroup
    ORDER BY DATEDIFF(MAX(End_Date), MIN(Start_Date)), MIN(Start_Date);
    
  • + 0 comments

    --MS SQL solution for beginer WITH cte1 AS ( SELECT start_date, row_number() OVER(ORDER BY start_date) AS rowNumber 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 rowNumber FROM projects WHERE end_date NOT IN (SELECT start_date FROM projects) ) SELECT cte1.start_date, cte2.end_date FROM cte1 INNER JOIN cte2 ON cte1.rowNumber = cte2.rowNumber ORDER BY datediff(day, cte1.start_date, cte2.end_date) , cte1.start_date;