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

|

1531 Discussions

|

  • 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://)

  • mrmioxin
     + 0 comments

    MySQL

    set @st_date = CURDATE();
with 
f as (
    SELECT Start_Date st,
            (LAG(end_date) over w) lend,
            End_Date
    FROM Projects
    Window w as (ORDER BY Start_Date)
),
f2 as (
    select
        if (f.lend = f.St, @st_date, @st_date := f.St) st,
        f.end_date end_date
    from f
)
select distinct
    f2.st, 
    MAX(f2.end_date) over start_d
from f2
window start_d as (partition by f2.st)
order by (count(*) over start_d), f2.st
;

  • piyushverma_cg
     + 0 comments

    Working solution for MySQL:

    WITH CTE AS 
(
SELECT
START_DATE
,END_DATE
,CASE 
WHEN ROW_NUMBER() OVER(ORDER BY END_DATE DESC) = 1  THEN END_DATE  
WHEN LEAD(END_DATE) OVER(ORDER BY END_DATE ASC)- END_DATE>1 THEN END_DATE ELSE NULL END AS PROJECT_END_DATE
,CASE WHEN ROW_NUMBER() OVER(ORDER BY END_DATE ASC) = 1 THEN START_DATE 
WHEN START_DATE - LAG(START_DATE) OVER(ORDER BY END_DATE ASC)>1 THEN START_DATE
ELSE NULL END AS PROJECT_START_DATE
FROM PROJECTS
)
,SUBQ AS 
(
SELECT
START_DATE
,END_DATE
,PROJECT_START_DATE
,PROJECT_END_DATE
FROM CTE
)
,CALC AS
(
SELECT 
STRT.PROJECT_START_DATE
,END.PROJECT_END_DATE
,1+END.PROJECT_END_DATE - STRT.PROJECT_START_DATE AS PROJECT_LENGTH
FROM 
(
SELECT 
x.PROJECT_START_DATE
,ROW_NUMBER() OVER(ORDER BY PROJECT_START_DATE ASC) AS RN__
FROM (SELECT * FROM SUBQ WHERE  PROJECT_START_DATE IS NOT NULL) x
)STRT
INNER JOIN 
(
SELECT 
y.PROJECT_END_DATE
,ROW_NUMBER() OVER(ORDER BY PROJECT_END_DATE ASC) AS RN__
FROM (SELECT * FROM SUBQ WHERE  PROJECT_END_DATE IS NOT NULL) y
)END
ON STRT.RN__ = END.RN__
)
SELECT
PROJECT_START_DATE, PROJECT_END_DATE
FROM CALC
ORDER BY PROJECT_LENGTH ASC, PROJECT_START_DATE ASC;

  • rk21chb0b41
     + 0 comments
    -- Oracle
SELECT 
    a.start_date,
    MIN(b.end_date) AS end_date
FROM 
    (SELECT start_date FROM projects 
     WHERE start_date NOT IN (SELECT end_date FROM projects)) a,
    (SELECT end_date FROM projects 
     WHERE end_date NOT IN (SELECT start_date FROM projects)) b
WHERE 
    a.start_date < b.end_date
GROUP BY 
    a.start_date
ORDER BY 
    min(b.end_date) - a.start_date, a.start_date;

  • gs06sid
     + 0 comments
    WITH
cte AS( SELECT
            COALESCE(LAG(start_date) OVER(ORDER BY start_date), '1900-01-01') AS prev_date,
            start_date, end_date,
            COALESCE(LEAD(end_date) OVER(ORDER BY end_date), '2100-01-01') AS next_date
        FROM projects),
        
cte2 AS ( SELECT start_date, ROW_NUMBER() OVER(ORDER BY start_date) AS rn
        FROM cte 
        WHERE ABS(DATEDIFF(DAY, start_date, prev_date)) <> 1),
        
cte3 AS ( SELECT end_date , ROW_NUMBER() OVER(ORDER BY end_date) AS rn
        FROM cte 
        WHERE ABS(DATEDIFF(DAY, next_date, end_date)) <> 1)

SELECT cte2.start_date, cte3.end_date
FROM cte2
JOIN cte3 ON cte2.rn = cte3.rn
ORDER BY ABS(DATEDIFF(DAY, cte2.start_date, cte3.end_date)), cte2.start_date;