Complex SQL Interview Question | Find Start date and End date of Project | Project Planning

[@kapur.betzy2023]

Nice series on SQL. It would be more helpful if you provide the script along with the description

[@dataprojecthub]

Hi, Glad you liked it😊. Added table script in description.

[@VijayKumar-ho2fj]

Hi, with cte as( select start_date, end_date, day(end_date) - row_number() over(order by end_date asc) as group_split from tasks ) select min(start_date) as start_date, max(end_date) as end_date, datediff(day, min(start_date), max(end_date)) as no_of_days from cte group by group_split;

[@HARSHRAJ-gp6ve]

with cte as( select tasks.*,DAY(end_date) as date1 FROM tasks ),cte1 as( select start_date,end_date,(date1-task_id) as date2 FROM cte ) select date2,COUNT(*) AS no_of_days,MIN(start_date) as start_date,MAX(end_date) as end_date FROM cte1 GROUP BY date2;

[@HARSHRAJ-gp6ve]

with cte as( select tasks.*,DAY(end_date) as date1,ROW_NUMBER()OVER() as date2 FROM tasks ),cte1 as( select task_id,start_date,end_date,(date1-date2) as d1 FROM cte ),cte2 as( select d1,COUNT(*) AS no_of_days,MIN(start_date) as start_date,MAX(end_date) as end_date FROM cte1 GROUP BY d1 ) select start_date,ROW_NUMBER()OVER() as row_1,end_date,no_of_days FROM cte2;