SQL : calculate the number of days between date range on a history table-CodePudding

I have a Person table which has a Status column that reflects the current status of that person's task.

PersonId    Status
--------------------------------------------
1           In Progress
2           In Progress
3           Completed
4           In Progress

I also have a PersonStatusHistory table which contains a LoggedDate column to indicate when did the status change occurred for each Person.

PersonId   Status            LoggedDate
--------------------------------------------
1          Created           11/11/2022
1          In Progress       11/15/2022

2          Created           11/05/2022
2          In Progress       11/07/2022
2          Blocked           11/10/2022
2          In Progress       11/15/2022

3          Created           11/03/2022
3          In Progress       11/12/2022
3          Completed         11/17/2022

4          Created           11/01/2022
4          In Progress       11/03/2022
4          Blocked           11/05/2022
4          In Progress       11/10/2022
4          Blocked           11/12/2022
4          In Progress       11/15/2022

I want to fetch all Person records where the current status = In Progress, the number of days it has been in In Progress minus the days they were Blocked.

The expected result should look like below:

Emp Id      No of Days In Progress
--------------------------------------------
1           4
              > 11/18 (date today) - 11/15 (first In Progress LoggedDate)
              > 18 - 15   1 (current day) = 4 days
2           7
              > 11/18 (date today) minus 11/07 (first In Progress LoggedDate)
              > less the number of Blocked days 
              > 18 - 7 - 5   1 (current day) = 7
4           8 days
              > 11/18 (date today) minus 11/03 (first In Progress LoggedDate)
              > less the number of Blocked days (5 and 3 days) 
              > 18 - 3 - 5 - 3   1 (current day) = 8

I can easily get the desired result for the first 2 but I'm having a hard time figuring out how to handle the last use case where it got Blocked several times. Here's my code so far

SELECT x.PersonId
       , (DATE_PART('day', CURRENT_DATE   1) - DATE_PART('day', x.start_in_progress_date)
         - DATE_PART('day', end_blocked_date) - DATE_PART('day', x.start_blocked_date)
         ) as no_of_days_in_progress
FROM
(
  SELECT p.PersonId
         , MIN (psh.LoggedDate) AS start_in_progress_date
         , (SELECT MIN(psh.LoggedDate)
             FROM PersonStatusHistory psh2
             WHERE psh2.PersonId = p.PersonId
             AND psh2.Status = 'Blocked'
           ) as start_blocked_date
         , (SELECT MAX(psh.LoggedDate)
             FROM PersonStatusHistory psh3
             WHERE psh3.PersonId = p.PersonId
             AND psh3.Status = 'In Progress'
           ) as end_blocked_date
  FROM Person p
  INNER JOIN PersonStatusHistory psh
     ON psh.PersonId = p.PersonId
  WHERE p.Status = 'In Progress'
  GROUP BY p.PersonId
) x

CodePudding user response：

I think nicer aproach is to get status change event end (via LEAD) and just summarize 'In Progress' status durations

select personid, status, sum(event_duration)   1
from (
    SELECT personid, status , LoggedDate event_start,  
    coalesce (lead(LoggedDate,1) OVER( partition by personid ORDER BY LoggedDate),
    current_date) - LoggedDate event_duration,
    coalesce (lead(LoggedDate,1) OVER( partition by personid ORDER BY LoggedDate), current_date) as event_end
            from personstatushistory p 
            order by 1,3 ) q
 where status = 'In Progress'
 group by personid, status
 order by 1,2