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

|

1471 Discussions

|

  • ishatyagi36
     + 0 comments
    with temp as (
select *, row_number() over(order by end_date) as rn
from projects
),
temp2 as (select a.task_id
, a.start_date
, a.end_date
, b.end_date as next_end_date
, datediff(dd, b.end_date, a.end_date) datedif
, case when datediff(dd, b.end_date, a.end_date) = 1 then 0 else 1 end flag
from temp as a
left join temp as b on a.rn = b.rn+1
),
temp3 as (select *
, sum(flag) over(order by end_date) as group_id
from temp2
)
select min(start_date), max(end_date)
from temp3
group by group_id
order by datediff(dd, min(start_date), max(end_date)), min(start_date)

  • kapagantipavan12
     + 0 comments

    WITH LaggedProjects AS ( SELECT Task_ID, Start_Date, End_Date, LAG(End_Date) OVER (ORDER BY End_Date) AS Prev_End_Date FROM Projects ), ProjectGroups AS ( SELECT Task_ID, Start_Date, End_Date, SUM(CASE WHEN Prev_End_Date = Start_Date THEN 0 ELSE 1 END) OVER (ORDER BY End_Date) AS Project_ID FROM LaggedProjects ), ProjectDurations AS ( SELECT Project_ID, MIN(Start_Date) AS Project_Start, MAX(End_Date) AS Project_End, DATEDIFF(day, MIN(Start_Date),MAX(End_Date)) AS Duration FROM ProjectGroups GROUP BY Project_ID ) SELECT Project_Start, Project_End FROM ProjectDurations ORDER BY Duration ASC, Project_Start ASC;

  • soumyadeepbala_1
     + 0 comments
    SELECT 
    s.Start_Date, 
    MIN(e.End_Date) AS End_Date
FROM 
    (SELECT Start_Date 
     FROM Projects 
     WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) s
JOIN 
    (SELECT End_Date 
     FROM Projects 
     WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) e
ON 
    s.Start_Date < e.End_Date
GROUP BY 
    s.Start_Date
ORDER BY 
    DATEDIFF(MIN(e.End_Date), s.Start_Date), 
    s.Start_Date;

  • chintalayashwan1
     + 0 comments

    With Cte1 as (Select Start_Date, row_number() over(order by Start_Date) as rn 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 rn From Projects where End_Date not in (Select Start_Date From Projects)) Select Cte1.Start_Date,Cte2.End_Date From Cte1 Join Cte2 on Cte2.rn = Cte1.rn order by Datediff(Day,start_Date,End_Date), Start_Date

  • vijim1702
     + 0 comments

    %sql with start_date_cte as ( select *,dense_rank() over ( order by task_id asc) as rownum from ( select *,datediff(day,l,start_date) as dd from ( select start_date,end_date,task_id ,lag(start_date,1) over(order by end_date) as l from projects ) ) where dd!=-1 or dd is null) , end_date_cte as ( select *, dense_rank() over ( order by task_id asc) as rownum from ( select *,datediff(day,l,start_date) as dd from( select start_date,end_date,task_id ,lead(start_date,1) over(order by end_date) as l from projects ) ) where dd!=1 or dd is null ) select s.start_date,e.end_date from start_date_cte s join end_date_cte e on s.rownum=e.rownum;