Расчет общего времени работы при сбросе часов.

Вопрос или проблема

exec sys_run_hrs @start_date="2024-01-01", @end_date="2024-01-04", @locations="2;4";
-- Я хотел бы разделить список местоположений в хранимой процедуре по точкам с запятой, но открыт для других вариантов

declare 
    @l_StartDate datetimeoffset(3),
    @l_EndDate datetimeoffset(3);
set @l_StartDate = CAST('2023-01-01 00:00:00.000 -05:00' as datetimeoffset(3));
select @l_EndDate = DATEADD(year, 1, @l_StartDate);

print @l_StartDate;
print @l_EndDate;

with run_hrs as
(
    select distinct hb.LOCATION_ID, 
        l.location_name, 
            FIRST_VALUE(hb.SYS_RUN_HRS) OVER (
                PARTITION BY hb.location_id 
                ORDER BY hb.STATUS_DATE )  AS sys_run_hours_Start,
            LAST_VALUE(hb.SYS_RUN_HRS) OVER (
                PARTITION BY hb.location_id 
                ORDER BY hb.STATUS_DATE  
                RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  AS sys_run_hours_End
    from history_table hb
    inner join locations l on l.LOCATION_ID = hb.LOCATION_ID
    where  hb.STATUS_DATE >= @l_StartDate and hb.STATUS_DATE < @l_EndDate
        and l.ACTIVE_INDC = 1
        and l.LOCATION_ID in (28, 196, 38, 1478, 1474, 1469, 1468)
) 
select run_hrs.LOCATION_ID, 
run_hrs.LOCATION_NAME, 
run_hrs.sys_run_hours_start, 
run_hrs.sys_run_hours_end, 
(run_hrs.sys_run_hours_end - run_hrs.sys_run_hours_start) as Run_hours
from run_hrs
order by LOCATION_NAME

Ответ или решение

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

Шаг 1: Создание хранимой процедуры

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

CREATE PROCEDURE sys_run_hrs 
    @start_date DATETIME, 
    @end_date DATETIME, 
    @locations VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    -- Преобразование переменной @locations в таблицу для использования в запросе
    DECLARE @LocationsTable TABLE (location_id INT);

    INSERT INTO @LocationsTable(location_id)
    SELECT value 
    FROM STRING_SPLIT(@locations, ';') 
    WHERE TRY_CAST(value AS INT) IS NOT NULL;

    WITH RunHours AS (
        SELECT 
            hb.location_id,
            hb.status_date,
            hb.sys_run_hrs,
            ROW_NUMBER() OVER (PARTITION BY hb.location_id ORDER BY hb.status_date) AS rn
        FROM history_table hb
        WHERE hb.status_date BETWEEN @start_date AND @end_date
        AND hb.location_id IN (SELECT location_id FROM @LocationsTable)
    ),
    AggregatedRunHours AS (
        SELECT 
            rh.location_id,
            SUM(rh.sys_run_hrs) as total_hours,
            COUNT(*) as entries
        FROM RunHours rh
        -- Идентифицируем сбросы. Сброс определяется как sys_run_hrs = 0
        -- Мы отбираем только те записи, где есть последовательные значения, отличные от нуля
        WHERE rh.sys_run_hrs > 0
        GROUP BY 
            rh.location_id, 
            (SELECT SUM(CASE WHEN sys_run_hrs = 0 THEN 1 ELSE 0 END) 
             OVER (PARTITION BY rh.location_id ORDER BY rh.status_date ROWS UNBOUNDED PRECEDING CURRENT ROW))
    )

    SELECT 
        loc.location_id,
        ISNULL(SUM(aru.total_hours), 0) AS total_hours
    FROM @LocationsTable loc
    LEFT JOIN AggregatedRunHours aru ON loc.location_id = aru.location_id
    GROUP BY loc.location_id
    ORDER BY loc.location_id;
END

Шаг 2: Объяснение логики

1. Разделение входных данных: Мы разбиваем строку с идентификаторами локаций (@locations) на отдельные значения с помощью функции STRING_SPLIT и вставляем их во временную таблицу @LocationsTable. Это позволяет нам легко обрабатывать список локаций.

2. Выбор данных: В общем запросе RunHours мы выбираем все записи из history_table, которые находятся в заданном диапазоне времени и принадлежат указанным локациям.

3. Фильтрация нулевых значений: Мы фильтруем записи таким образом, чтобы учитывать только те, где sys_run_hrs больше нуля. Это позволяет нам игнорировать периоды, в которых часы были сброшены (то есть, когда sys_run_hrs равны нулю).

4. Группировка и суммирование: В AggregatedRunHours мы группируем записи по location_id и применяем суммирование, чтобы получить общее количество часов для каждой локации.

5. Итерация и вывод: Наконец, мы возвращаем итоговые часы для каждой локации. В случае, если данных для локации нет, возвращаем 0.

Пример использования

Вызов процедуры будет производиться следующим образом:

EXEC sys_run_hrs '2024-01-01', '2024-01-04', '2;4';

Этот вызов вернет результаты, которые показывают общее количество системных часов для указанных локаций с учетом всех сбросов в заданном диапазоне дат.

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