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

|

1547 Discussions

|

  • vinamrahnemani11
     + 0 comments

    WITH temp AS ( SELECT task_id, start_date, end_date, end_date - ROW_NUMBER() OVER (ORDER BY end_date) AS group_id FROM projects ), t2 AS ( SELECT MIN(start_date) OVER(PARTITION BY group_id) AS min_date, MAX(end_date) OVER(PARTITION BY group_id) AS max_date FROM temp ) SELECT DISTINCT(min_date), max_date FROM t2 ORDER BY max_date - min_date ASC;

  • sks9766981058
     + 0 comments

    WITH cte1 AS ( SELECT start_date, ROW_NUMBER() OVER (ORDER BY start_date) AS rn1 FROM projects WHERE start_date NOT IN ( SELECT end_date FROM projects ) ), cte2 AS ( SELECT end_date, ROW_NUMBER() OVER (ORDER BY start_date) AS rn2 FROM projects WHERE end_date NOT IN ( SELECT start_date FROM projects ) ) SELECT cte1.start_date, cte2.end_date FROM cte1 JOIN cte2 ON cte1.rn1 = cte2.rn2 ORDER BY DATEDIFF(cte2.end_date, cte1.start_date) ASC, cte1.start_date;

  • hanzalahali1062
     + 0 comments
    with cte1 as(
    select start_date,
    row_number() over (order by start_date) as rn1
    from projects
    where start_date not in(
    select end_date from projects)
)
,cte2 as(
    select end_date,
    row_number() over (order by start_date) as rn2
    from projects
    where end_date not in( 
    select start_date from projects)
    
)
select cte1.start_date,cte2.end_date from cte1,cte2
where rn1=rn2
order by datediff(day,cte1.start_date,cte2.end_date) asc, cte1.start_date;

  • hzerryn
     + 0 comments
    WITH row_n AS (
    SELECT
        task_id,
        start_date,
        end_date,
        ROW_NUMBER() OVER (ORDER BY end_date ASC) AS rn
    FROM projects
),
day_seq AS (
    SELECT
        task_id,
        start_date,
        end_date,
        rn,
        end_date - INTERVAL rn DAY AS grp
    FROM row_n
)
SELECT MIN(start_date), MAX(end_date)
FROM day_seq
GROUP BY grp
ORDER BY COUNT(grp) ASC, MIN(start_date) ASC

  • shreyab2406
     + 0 comments

    SELECT MIN(Start_Date) AS project_start, MAX(End_Date) AS project_end FROM ( SELECT *, DATE_SUB(Start_Date, INTERVAL ROW_NUMBER() OVER (ORDER BY Start_Date) DAY) AS grp FROM Projects ) t GROUP BY grp ORDER BY DATEDIFF(MAX(End_Date), MIN(Start_Date)), MIN(Start_Date);