SQL: вычислить количество дней между диапазоном дат в таблице истории

У меня есть таблица Person, в которой есть столбец Status, отражающий текущий статус задачи этого человека.

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

У меня также есть таблица PersonStatusHistory, которая содержит столбец LoggedDate, чтобы указать, когда произошло изменение статуса для каждого 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

Я хочу получить все записи о людях, у которых текущий статус = «Выполняется», количество дней, в течение которых это было In Progress, минус дни, в которые они были Blocked.

Ожидаемый результат должен выглядеть следующим образом:

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

Я могу легко получить желаемый результат для первых двух, но мне трудно понять, как справиться с последним вариантом использования, когда он был заблокирован несколько раз. Вот мой код до сих пор

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
Шаблоны Angular PrimeNg
Шаблоны Angular PrimeNg
Как привнести проверку типов в наши шаблоны Angular, использующие компоненты библиотеки PrimeNg, и настроить их отображение с помощью встроенной...
Создайте ползком, похожим на звездные войны, с помощью CSS и Javascript
Создайте ползком, похожим на звездные войны, с помощью CSS и Javascript
Если вы веб-разработчик (или хотите им стать), то вы наверняка гик и вам нравятся "Звездные войны". А как бы вы хотели, чтобы фоном для вашего...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Начала с розового дизайна
Начала с розового дизайна
Pink Design - это система дизайна Appwrite с открытым исходным кодом для создания последовательных и многократно используемых пользовательских...
Шлюз в PHP
Шлюз в PHP
API-шлюз (AG) - это сервер, который действует как единая точка входа для набора микросервисов.
14 Задание: Типы данных и структуры данных Python для DevOps
14 Задание: Типы данных и структуры данных Python для DevOps
проверить тип данных используемой переменной, мы можем просто написать: your_variable=100
1
0
58
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Я думаю, что более хороший подход - получить конец события изменения статуса (через LEAD) и просто суммировать продолжительность статуса «В процессе».

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

Это хороший подход! Обратите внимание, что (1) event_end кажется излишним, поскольку внешний запрос не использует его. (2) coalesce(lead(..., 1) over(...), current_date) можно упростить в Postgres, используя трехаргументную форму лида, которая поддерживает значение по умолчанию: lead(..., 1, current_date) over(...).

GMB 18.11.2022 23:06

event_end был там, чтобы объяснить запрос для новичков: D хороший совет для 3 аргументов LEAD, кстати

esmin 19.11.2022 01:19

@esmin вау, сегодня я узнал кое-что новое. Возможно, я смогу использовать LEAD(), чтобы получить желаемое поведение. Спасибо!

Kiamoyman 19.11.2022 01:47

@GSM Я согласен, отличный совет по использованию 3 аргументов!

Kiamoyman 19.11.2022 01:48

Другие вопросы по теме