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

|

1535 Discussions

|

  • pushkar633
     + 0 comments

    SQL Server:

    With cte1 as ( select Start_date, end_date, row_number() over(order by end_date) rn from projects p ) select min(start_date), max(end_date) from cte1 group by day(end_date) - rn order by datediff(day,min(start_date),max(end_date)),1 ;

  • Divine17121
     + 1 comment

    Why 30-31 is considered as different project

  • reuadam10
     + 0 comments
    WITH tagged AS (
    SELECT
        Task_ID,
        Start_Date,
        End_Date,
        CASE 
            WHEN Start_Date = LAG(End_Date) OVER (ORDER BY Start_Date) 
            THEN 0 ELSE 1 
        END AS is_break
    FROM Projects
),
projected AS (
    SELECT
        *,
        SUM(is_break) OVER (ORDER BY Start_Date) AS project_id
    FROM tagged
)
SELECT
    MIN(Start_Date) AS project_start,
    MAX(End_Date)   AS project_end
FROM projected
GROUP BY project_id
ORDER BY DATEDIFF(project_end, project_start), project_start;

  • nisarbaig1990
     + 0 comments

    WITH project_groups AS ( SELECT Task_ID, Start_Date, End_Date, SUM(is_new_project) OVER (ORDER BY Start_Date) AS project_id FROM (SELECT p.*, CASE WHEN LAG(End_Date) OVER (ORDER BY Start_Date) = Start_Date THEN 0 ELSE 1 END AS is_new_project FROM Projects p ) t ) SELECT MIN(Start_Date) AS project_start, MAX(End_Date) AS project_end FROM project_groups GROUP BY project_id ORDER BY DATEDIFF(MAX(End_Date), MIN(Start_Date)), MIN(Start_Date);

  • sagnikhalder10
     + 0 comments

    with cte as (select task_id,start_date,end_date, lag(end_date) over(order by start_date) as prev_end from Projects) ,daysdifference as (select *,(case when datediff(end_date,prev_end)=1 then 0 else 1 end) as days_diff from cte ) , full_model as (SELECT *, SUM(days_diff) OVER (ORDER BY start_date) AS gap_group FROM daysdifference ) , new_full_model as ( SELECT MIN(start_date) AS project_start_date, MAX(end_date) AS project_end_date, DATEDIFF(MAX(end_date), MIN(start_date)) + 1 AS duration FROM full_model GROUP BY gap_group ORDER BY duration, project_start_date ) select project_start_date,project_end_date 1. from new_full_modelhttps://)