I searched and saw potential results but with my skills I'm not able to adapt them.

I have a table:

and need the date difference between 'open' and 'approved' within groups starting with 'open' and ending with 'approved'. The last group is not yet approved. There the date difference is between the last open and today (=15.10.2022 just as example)

Questions then:

1. How do I define the groups. I thought maybe with a rank because the orig table has thousands of rows
2. How does the date diff function look like that considers only open and approved plus the special case where the record is not yet approved
3. How to apply this date diff to the groups only

Thanks a lot :-)

CodePudding user response：

Your idea was good; a CTE to manage the case where you need to use the current day was all you were missing (See EXISTS).

WITH RankedStatus AS (
    SELECT MyTable.*,
           DENSE_RANK() OVER (PARTITION BY RecordStatus ORDER BY Date) AS Rank
    FROM MyTable
)
SELECT RS.*,
       CASE WHEN EXISTS(SELECT FROM RankedStatus WHERE Rank = RS.Rank and RecordStatus = 'approved')
       THEN MAX(Date) OVER (PARTITION BY Rank)
       ELSE CURRENT_DATE END
       - MIN(Date) OVER (PARTITION BY Rank) AS DateDiff
FROM RankedStatus RS

CodePudding user response：

Thanks a lot for the response. To make it a bit more complicated. How can I then partition as displayed in the Group column added for illustration?

In words but don't know to convert to code: status 'approved' always defines the end. Only an 'open' after 'approval' defines the start. The other 'open' are not relevant. The date diff is then between start and end of each group.