Рассчитать количество дней между сменой статуса

Обновленная версия

На основе отзывов ОП. Это не просто ситуация с мин/максом, это проблема пробелов и островов. В этом случае нам приходится иметь дело с несколькими записями «on_rent» и, возможно, с несколькими записями «off_rent», а также с промежутками между «off_rent» и «on_rent».

На данный момент это мое решение (см. нижнюю часть ответа, чтобы заполнить таблицу образцов данных):

WITH cte AS (
    -- Collapse groups to get the start and end rent dates for each
    SELECT x.tag, x.GroupID
        , DateOnRent  = MIN(IIF(x.on_rent_status = 1, x.[date_on-off_rent], NULL)) -- Using min because we want the FIRST time  on_rent was logged
        , DateOffRent = MIN(IIF(x.on_rent_status = 0, x.[date_on-off_rent], NULL)) -- Using min because we want the FIRST time off_rent was logged
    FROM (
        SELECT x.tag, x.[date_on-off_rent], x.on_rent_status
            -- If the status changed from off_rent to on_rent, then we know a new group has started
            -- Returning 1 to the running total will generate a grouping ID that keeps sequential rent_status records together
            , GroupID = SUM(IIF(x.on_rent_status = 1 AND x.prev_on_rent_status = 0, 1, 0)) OVER (PARTITION BY x.tag ORDER BY x.position)
        FROM (
            SELECT tag, on_rent_status, [date_on-off_rent], position
                -- Getting the previous value will help us later determine whether the status changed
                , prev_on_rent_status = LAG(on_rent_status) OVER (PARTITION BY tag ORDER BY position)
            FROM #rent_history
        ) x
    ) x
    GROUP BY x.tag, x.GroupID
)
SELECT x.tag
    , FirstDateOnRent = MIN(x.DateOnRent)
    , LastDateOffRent = MAX(x.LastDateOffRent) -- NULL if still on_rent
    , DaysOnRent      = SUM(x.DaysOnRent)
    , DaysOffRent     = DATEDIFF(DAY, MAX(x.LastDateOffRent), GETDATE()) -- NULL if still on_rent
FROM (
    SELECT x.tag, x.DateOnRent, x.DateOffRent
        -- We want the most recent state of DateOffRent, rather than MAX, which means if they are still on_rent, it should return null
        , LastDateOffRent = LAST_VALUE(x.DateOffRent) OVER (PARTITION BY x.tag ORDER BY x.DateOnRent)
        -- Number of DaysOnrent needs to be calc'd at the group level so we don't accidentally include gaps
        , DaysOnRent  = DATEDIFF(DAY, x.DateOnRent, COALESCE(x.DateOffRent, GETDATE()))
    FROM cte x
) x
GROUP BY x.tag;

Я постарался как можно лучше прокомментировать код, поэтому не буду полностью погружаться в то, как это работает, построчно.

Но основная идея состоит в том, чтобы... вычислить некий идентификатор группировки, который сможет объединять последовательные события. В этом случае я использовал как LAG(), так и SUM({state change logic}) OVER() для создания этого идентификатора.

Затем для получения окончательного результата требуется просто дополнительная группировка и агрегирование.

Эта новая версия запроса будет поддерживать несколько записей on_rent, несколько записей off_rent, а также пробелы между off_rent и on_rent.

Старый/Оригинальный ответ

Судя по вашему объяснению, у вас есть таблица/набор данных под названием «История аренды», которая представляет собой верхний набор данных на вашем снимке экрана.

И вам нужен запрос к этому набору данных, выходные данные которого соответствуют второму набору данных на снимке экрана с надписью «Окончательный результат».

Если предположить, что это правда, то это то, что я придумал... проблема в том, что я делаю кучу предположений относительно бизнес-логики, которые могут быть неверными, даже если они соответствуют вашей выходной таблице...

SELECT x.tag, x.DateOnRent, x.DateOffRent
    , DaysOnRent  = DATEDIFF(DAY, x.DateOnRent, COALESCE(x.DateOffRent, GETDATE()))
    , DaysOffRent = DATEDIFF(DAY, x.DateOffRent, GETDATE())
FROM (
    SELECT tag
        , DateOnRent  = MIN(IIF(on_rent_status = 1, [date_on-off_rent], NULL))
        , DateOffRent = MAX(IIF(on_rent_status = 0, [date_on-off_rent], NULL))
    FROM #rent_history
    GROUP BY tag
) x

Я также не знаю, насколько велики базовые наборы данных, с которыми это работает, поэтому понятия не имею, насколько это будет эффективно.

Хотя концепция кажется довольно простой... возьмите самую раннюю дату «on_rent» и самую старую дату «off_rent». Затем выполните вычисления датировки с их использованием?

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

CREATE TABLE #rent_history (
    tag                         nvarchar(20) NOT NULL,
    progress_complete_date_time datetime     NOT NULL,
    create_date                 datetime     NOT NULL,
    required_until_date         datetime     NOT NULL,
    modify_date                 datetime     NOT NULL,
    on_rent_status              bit          NOT NULL,
    [date_on-off_rent]          datetime     NOT NULL,
    position                    int          NOT NULL,
    [first_time_on-off_rent]    datetime         NULL,
    [RANK]                      int          NOT NULL,
);

INSERT INTO #rent_history
VALUES ('TAG-000479', '08/12/21 00:00', '11/06/23 19:31', '01/31/24 00:00', '11/06/23 19:31', 1, '08/12/21 00:00', 1, '08/12/21 00:00', 1)
     , ('TAG-000479', '08/12/21 00:00', '11/06/23 19:31', '01/31/24 00:00', '11/06/23 19:31', 1, '11/06/23 00:00', 2, NULL            , 1)
     , ('TAG-000479', '08/12/21 00:00', '11/06/23 19:31', '01/31/24 00:00', '11/06/23 19:31', 1, '11/06/23 00:00', 3, NULL            , 1)
     , ('TAG-000479', '08/12/21 00:00', '11/06/23 19:31', '04/01/24 00:00', '04/04/24 05:00', 0, '04/04/24 00:00', 4, NULL            , 4)
     , ('TAG-000479', '08/12/21 00:00', '11/06/23 19:31', '04/01/24 00:00', '04/04/24 05:00', 1, '04/04/24 00:00', 5, NULL            , 5)
     , ('TAG-000479', '08/12/21 00:00', '11/06/23 19:31', '12/31/24 00:00', '04/30/24 15:11', 1, '04/30/24 00:00', 6, NULL            , 6)
     , ('TAG-000479', '08/12/21 00:00', '11/06/23 19:31', '12/31/24 00:00', '06/10/24 17:02', 0, '06/10/24 00:00', 7, NULL            , 7)
     , ('TAG-001832', '03/28/24 00:00', '01/17/24 15:41', '03/31/24 00:00', '03/28/24 03:32', 1, '03/28/24 00:00', 1, '03/28/24 00:00', 1)
     , ('TAG-003420', '06/24/24 00:00', '06/24/24 18:55', '12/15/24 00:00', '06/25/24 15:18', 1, '06/24/24 00:00', 1, '06/24/24 00:00', 1);