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

|

1554 Discussions

|

  • vkucherov
     + 0 comments

    MS SQL

    SELECT StartDateTable.Start_Date, EndDateTable.End_Date
FROM (
    SELECT Start_Date, ROW_NUMBER() OVER (ORDER BY Start_Date) as rowN
    FROM Projects 
    WHERE Start_Date NOT IN 
        (SELECT End_Date FROM Projects)
) AS StartDateTable
JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY End_Date) as rowN, End_Date 
    FROM Projects 
    WHERE End_Date NOT IN 
        (SELECT Start_Date FROM Projects)
) AS EndDateTable
ON StartDateTable.rowN = EndDateTable.rowN
ORDER BY DATEDIFF(day, StartDateTable.Start_Date, EndDateTable.End_Date)
    , StartDateTable.Start_Date

  • bachuvikas2
     + 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 end_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), cte1.start_date;

  • projectgcpdev
     + 0 comments

    MS-SQL

    WITH seq AS ( SELECT Start_Date, End_Date, DATEADD(day, -ROW_NUMBER() OVER (ORDER BY End_Date), End_Date) AS grp, -ROW_NUMBER() OVER (ORDER BY End_Date) AS dd FROM Projects ), projects AS ( SELECT MIN(Start_Date) AS start_date, MAX(End_Date) AS end_date, DATEDIFF(day, MIN(Start_Date), MAX(End_Date)) AS duration_days FROM seq GROUP BY grp ) SELECT start_date, end_date FROM projects ORDER BY duration_days ASC, start_date ASC;

  • jpconcerman
     + 0 comments

    with cte1 as ( select task_id, start_date, end_date, sum(break_flag) over (order by end_Date) grp from
    ( select task_id, start_date, end_Date, lag(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date) as next_end_Date --decode((to_date(end_date,'YYYY-MM-DD') + 1) = (lead(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date)),true,1,0), , case when (to_date(start_date,'YYYY-MM-DD') = (lag(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date))) then 0 when (lag(to_date(end_date,'YYYY-MM-DD')) over (order by end_Date)) is null then 0 else 1 end break_flag from Projects order by end_Date) ) select min(start_date), max(end_date) from cte1 group by grp order by (to_char(min(end_date),'YYYYMMDD') - to_char(max(start_date),'YYYYMMDD')) desc, min(end_date)

  • khangan_10hlvc
     + 0 comments
    mysql:
WITH raw1 as(SELECT 
															Task_ID, 
															Start_Date, 
															End_Date, 
															LAG(End_Date) OVER(ORDER BY End_Date) as E1 
												FROM projects), 
raw2 AS(SELECT 
											Task_ID, 
											Start_Date, 
											End_Date, 
											(CASE WHEN E1=Start_Date AND End_Date-E1=1 THEN 1 ELSE 0 END) AS binary1 
									FROM raw1 
									GROUP BY 1,2,3), 
raw3 AS(SELECT 
													task_ID, 
													Start_Date, 
													End_Date, 
													SUM(CASE WHEN binary1 = 0 THEN 1 ELSE 0 END) OVER (ORDER BY Task_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS project_label FROM raw2) SELECT MIN(Start_Date),MAX(End_Date) FROM raw3 GROUP BY project_label ORDER BY COUNT(project_label) ASC, Start_Date ASC