Я рассматривал случай рабочего дня с 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
SoulUran 