Бывает иногда полезной такая функция, рассчитывает количество рабочих часов между датами
DECLARE @startdate datetime
DECLARE @enddate DATETIME
SET @startdate = '2015-04-28 12:33:19.000'
SET @enddate = '2015-04-30 09:45:20.000'
SELECT [dbo].[ufn_CalcWorkTime] (@startdate, @enddate)
Результат расчета в часах
Текст самой функции
ALTER FUNCTION [dbo].[ufn_CalcWorkTime]
(
@startdate DATETIME,
@enddate DATETIME
)
RETURNS DECIMAL(10, 3)
AS
BEGIN
—DECLARE @startdate datetime
—DECLARE @enddate DATETIME
DECLARE @ldate DATETIME,
@rdate DATETIME,
@ldaystartdate DATETIME,
@rdaystartdate DATETIME,
@StartWorkTime AS VARCHAR(8) = ' 9:30:00',
@EndWorkTime AS VARCHAR(8) = ' 18:30:00',
@DayzBetween AS INT
DECLARE @result AS DECIMAL(10, 3)
DECLARE @add_days INT,
@minus_days INT
—SET @startdate = '2015-12-18 16:07:54.000'
—SET @enddate = '2015-12-22 12:21:56.000'
SET @ldate = CAST(
(CONVERT(VARCHAR(10), @startdate, 102) + @EndWorkTime) AS DATETIME
)
SET @ldate = CASE
WHEN @ldate > @startdate THEN @ldate
ELSE @startdate
END
SET @ldaystartdate = CAST(
(CONVERT(VARCHAR(10), @startdate, 102) + @StartWorkTime) AS DATETIME
)
SET @rdaystartdate = CASE
WHEN @enddate > @rdaystartdate THEN @rdaystartdate
ELSE @enddate
END
SET @rdate = CAST(
(CONVERT(VARCHAR(10), @enddate, 102) + @EndWorkTime) AS DATETIME
)
SET @rdate = CASE
WHEN @rdate < @enddate THEN @rdate
ELSE @enddate
END
SET @rdaystartdate = CAST(
(CONVERT(VARCHAR(10), @enddate, 102) + @StartWorkTime) AS DATETIME
)
SET @rdaystartdate = CASE
WHEN @enddate > @rdaystartdate THEN @rdaystartdate
ELSE @enddate
END
SET @DayzBetween = (
(
DATEDIFF(
dd,
CONVERT(VARCHAR(10), @startdate, 102),
CONVERT(VARCHAR(10), @enddate, 102)
)
)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATEPART(DW, @StartDate) = 6 THEN 1 ELSE 0 END)
-(CASE WHEN DATEPART(DW, @StartDate) = 7 THEN 1 ELSE 0 END)
)
—добавим рабочие праздники
SELECT @add_days = COUNT(*)
FROM HolidaysAndWeekends (NOLOCK)
WHERE Дата BETWEEN @startdate AND @enddate
AND [Рабочий День] = 1
—вычтем выходные рабочие дни
SELECT @minus_days = COUNT(*)
FROM HolidaysAndWeekends (NOLOCK)