Number of days for each project together with start date and end date for each project-CodePudding

It does not meter if you offer me a solution in Oracle or SQL Server or MySQL or PostgreSQL.

All I need is a different approach, another way of thinking. Thank you The start date and end date of each project are required along with the number of days - how long each project lasted. Projects do not overlap. I tried to solve it using lag and lead but I didn't succeed. Usually I look for several solutions to a problem but unfortunately I only found one solution to this problem on google. I'm interested in other approaches if that's the only way I'm learning. This is the solution I found on google

WITH STARTDATES AS (
SELECT startdate
FROM project
WHERE startdate NOT IN (SELECT enddate FROM project) ),
-- get end dates not present in start date column (these are “true” project end dates) 
ENDDATES AS (
SELECT enddate
FROM project
WHERE enddate NOT IN (SELECT startdate FROM project) ),
-- filter to plausible start-end pairs (start < end), then find correct end date for each start date (the minimum end date, since there are no overlapping projects)
t3 AS (
SELECT startdate, min(enddate) AS enddate
FROM STARTDATES, ENDDATES
WHERE startdate < enddate
GROUP BY startdate )
SELECT startdate, enddate, enddate - startdate AS project_duration
FROM t3 
ORDER BY 3,1;

Thank you in advance

CodePudding user response：

You can try to use LAG window function get the next row of STARTDATE then compare subtract ENDDATE

then use condition aggregate function get grp make grouping.

SELECT MIN(STARTDATE) STARTDATE,
       MAX(ENDDATE) ENDDATE,
       COUNT(*) DURATION
FROM (
    SELECT t1.*,SUM(CASE WHEN t1.daydiff = 0 THEN 0 ELSE 1 END) OVER(ORDER BY STARTDATE)  grp
    FROM (
        SELECT T.*,LAG(ENDDATE) OVER(ORDER BY STARTDATE) - STARTDATE daydiff
        FROM T
    ) t1
) t1
GROUP BY grp;

sqlfiddle