SQL : Aggregating data that is not in the same place on the table-CodePudding

I would like to calculate a specific duration by substracting two dates that are not on the same record. Let me illustrate it, i have a table that looks like this :

projectid | sectionid | editingStartDate | diffusionEndDate
01        | 01        |   01/01/2022     | null
01        | 02        | null             |  11/02/2022

From this i would like to substract the editingStartDate from the diffusionEndDate. And the result would be 42 days. And I don't want to do a CROSS JOIN as i have thousands of records.

Does anyone have a solution ?

PS : Sorry if the question is not clear, it's my first time publishing here, I am not familiar with the formats.

CodePudding user response：

If a projectid has many sectionid (more than the two shown in your example) do you want the days between the earliest editingStartDate and the latest diffusionEndDate? If so:

DECLARE @Table TABLE
(
    projectid CHAR(2) NOT NULL,
    sectionid CHAR(2) NOT NULL,
    editingStartDate DATE NULL,
    diffusionEndDate DATE NULL
);


INSERT INTO @Table
(
    projectid,
    sectionid,
    editingStartDate,
    diffusionEndDate
)
VALUES
(   '01',       -- projectid - char(2)
    '01',       -- sectionid - char(2)
    '20220101', -- editingStartDate - date
    NULL        -- diffusionEndDate - date
    ),
(   '01',      -- projectid - char(2)
    '02',      -- sectionid - char(2)
    NULL,      -- editingStartDate - date
    '20220211' -- diffusionEndDate - date
);


SELECT projectid,
       DATEDIFF(DAY, MIN(editingStartDate), MAX(diffusionEndDate))   1 AS Duration
FROM @Table
GROUP BY projectid
ORDER BY projectid;

Gives the result: