MS SQL WORKTIME — расчет рабочего времени

Я рассматривал случай рабочего дня с 08:00 по 17:00, включая обед с 12:00 по 13:00. Функция рассчитывает количество часов,
которые попадают в заданный диапазон времени с учетом выходных дней (суббота и воскресенье), рамок рабочего дня и обеденного времени. Исправил одно исключение в расчете и добавил проверку праздничных дней для Беларуси.

CREATE FUNCTION [worktime] (@beg_dt datetime, @end_dt datetime)
RETURNS FLOAT
with schemabinding
AS

BEGIN

declare @beg_datetime datetime
declare @end_datetime datetime
declare @kol_day INT
declare @res FLOAT
declare @dt FLOAT
declare @i INT

set @beg_datetime = @beg_dt;
set @end_datetime = @end_dt;
set @dt = 0;
set @res = 0;
set @i = 0;
      
    IF @beg_datetime < @end_datetime
    BEGIN
        --Расчет количества дней в заданном периоде дат
       set @kol_day = datediff(day, @beg_datetime, @end_datetime);
 
        WHILE (@i <= @kol_day)
        BEGIN
          -- Проверка выходных дней и праздничные дни
            if not datepart(w, dateadd(day, @i, @beg_datetime)) in (7, 1)
            OR  ( convert(date, dateadd(day, @i, @beg_datetime)) IN (
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-01-01'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-01-07'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-03-08'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-04-29'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-05-01'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-05-09'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-07-04'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-11-07'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-12-25')) )
            
            BEGIN
                -- Расчет количества рабочего времени по каждому дню
                set @res = case       
                -- если промежуточный день
                when not cast(dateadd(day, @i, @beg_datetime) as date) in (cast(@beg_datetime as date), cast(@end_datetime as date))
                    then 8
                -- если даты заданного дипазона совпадают
                when @kol_day = 0 and cast(dateadd(day, @i, @beg_datetime) as date) = cast(@end_datetime as date) then
                cast(datediff(second, dateadd(day, @i, @beg_datetime), @end_datetime) as float)/60/60
                else case
                    -- если начало периода
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@beg_datetime as date) and
                    datepart(hh, @beg_datetime) < 8
                        then 8
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@beg_datetime as date) and
                    datepart(hh, @beg_datetime) >= 8 and datepart(hh, @beg_datetime) < 12
                        then cast(datediff(second, @beg_datetime, dateadd(hour, 17, cast(cast(@beg_datetime as date) as datetime))) as float)/60/60 - 1
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@beg_datetime as date) and
                    datepart(hh, @beg_datetime) >= 13 and datepart(hh, @beg_datetime) < 17
                        then cast(datediff(second, @beg_datetime, dateadd(hour, 17, cast(cast(@beg_datetime as date) as datetime))) as float)/60/60
                    -- если конец периода
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@end_datetime as date) and
                    datepart(hh, @end_datetime) >= 8 and datepart(hh, @end_datetime) < 12
                        then cast(datediff(second, dateadd(hour, 8, cast(cast(@end_datetime as date) as datetime)), @end_datetime) as float)/60/60
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@end_datetime as date) and
                    datepart(hh, @end_datetime) >= 13 and datepart(hh, @end_datetime) < 17
                        then cast(datediff(second, dateadd(hour, 9, cast(cast(@end_datetime as date) as datetime)), @end_datetime) as float)/60/60
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@end_datetime as date) and
                    datepart(hh, @end_datetime) > 17
                        then 8
                    -- ошибка
                    else 0
                    END
                END
            -- Расчет суммароного рабочего времени за период
            set @dt = @dt + @res;

            END
            set @i = @i + 1;
        END
    END
    ELSE
        set @dt = -1;

RETURN @dt

END

Пример вызова функции:

select [worktime]('2014-06-02 10:20:00.000', '2014-06-15 16:45:37.000') dt
dt
---
77,6666666666667

Расчет рабочих часов в указанном периоде

Я рассматривал случай рабочего дня с 08:00 по 17:00, включая обед с 12:00 по 13:00. Функция рассчитывает количество часов, которые попадают в заданный диапазон времени с учетом выходных дней (суббота и воскресенье), рамок рабочего дня и обеда.

CREATE OR REPLACE function worktime(beg_datetime in date, end_datetime in date)
return number
as
    kol_day number;
    type arr_table is table of number index by binary_integer;
    arr arr_table;
    res number default 0;
    
begin

   -- Чистим массив
   arr.delete;
   
 if beg_datetime < end_datetime then
   -- Расчет количества дней в заданном периоде дат
   kol_day := round(trunc(end_datetime) - trunc(beg_datetime));
   
   for i in 0..kol_day loop
      -- Проверка выходных дней (exclude Sunday, Saturday)
      if not to_char(beg_datetime + i, 'd') in (7, 1) then 
        -- Расчет количества рабочего времени по каждому дню
        arr(i) := case        
        -- если промежуточный день
        when not trunc(beg_datetime + i) in (trunc(beg_datetime), trunc(end_datetime)) 
            then 8
        -- если даты заданного дипазона совпадают
        when kol_day = 0 and trunc(beg_datetime + i) = trunc(end_datetime) then
           round(end_datetime - (beg_datetime + i), 3) 
        else case 
             -- если начало периода
             when trunc(beg_datetime + i) = trunc(beg_datetime) and
             to_char(beg_datetime, 'hh24') >= 8 and to_char(beg_datetime, 'hh24') < 12 
                then round((((trunc(beg_datetime) + 17/24) - beg_datetime)*24) - 1,3)
             when trunc(beg_datetime + i) = trunc(beg_datetime) and 
             to_char(beg_datetime, 'hh24') >= 13 and to_char(beg_datetime, 'hh24') < 17
                then round((((trunc(beg_datetime) + 17/24) - beg_datetime)*24),3)
             -- если конец периода
             when trunc(beg_datetime + i) = trunc(end_datetime) and
             to_char(end_datetime, 'hh24') >= 8 and to_char(end_datetime, 'hh24') < 12 
                then round((end_datetime - (trunc(end_datetime) + 8/24)) * 24,3)
             when trunc(beg_datetime + i) = trunc(end_datetime) and 
             to_char(end_datetime, 'hh24') >= 13 and to_char(end_datetime, 'hh24') < 17
                then round((end_datetime - (trunc(end_datetime ) + 8/24 + 1/24)) * 24,3)     
             -- ошибка
             else 0
             end
        end;
        
        -- Расчет суммароного рабочего времени за период
        res:= res + arr(i);
      
      end if;
                        
   end loop;

 else
 res:=-1;    
    DBMS_OUTPUT.PUT_LINE('Указан неверный диапазон дат!');
 
 end if;
   return res;
end;
/

Пример, вызова функции:

select worktime(to_date('07.04.2014 16:12:34','dd.mm.yyyy hh24:mi:ss'), to_date('20.04.2014 09:20:15','dd.mm.yyyy hh24:mi:ss')) RES from dual;

RES
---
72.791