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.
- Prepare
- SQL
- Advanced Join
- SQL Project Planning
- Discussions
SQL Project Planning
SQL Project Planning
Sort by
recency
|
1554 Discussions
|
Please Login in order to post a comment
MS SQL
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;
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;
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)