Для решения задачи расширенного отчета мне требовалось вычислять рабочее время потраченное сотрудником на поставленную ему задачу в днях.
Попробовал использовать уже имеющиеся решения этой задачи, но столкнулся с небольшими проблемами, поэтому написал свой вариант решения.
На мой взгляд, получилось совсем не плохо :-)
Первый вариант пришлось немного изменить (для тех кто уже успел с ним ознакомиться), так как для расчета необходимо было вызывать процедуру, а не функцию, что осложняло использование решения в запросах для построения отчетов.
И так, решение теперь состоит из 4 функций:
1. Функция расчета рабочего времени в первый день заданного периода
2. Функция расчета общего рабочего времени в первый день заданного периода
3. Функция расчета рабочего времени в последний день заданного периода
4. Функция расчета рабочего времени за период
Есть несколько допущений:
1. Если дата старта не задана, то она приравнивается к началу текущего дня
2. Если дата окончания не задача, то она приравнивается к текущему моменту времени
3. Если не указан идентификатор бизнес-календаря, то выполняется поиск первого в системе.
4. Если календарей нет в системе, то календарем по умолчанию считается рабочий день с 09 до 18 с перерывом на обед с 13 до 14 пять дней в неделю
При запуске процедуры расчета рабочего времени есть параметры:
@OnlyFullDay:
1 - учитываются только полные рабочие дни, т.е. без первого рабочего дня и последнего;
0 - в дополнение к полным дням учитываются первый и последний день, если в них было рабочее время
@Mode:
1 - результат возвращается в минутах (параметр @OnlyFullDay тогда игнорируется)
0 - результат в днях
Функция возвращает количество рабочих дней или количество рабочего времени в минутах.
Рабочее время вычисляется за любой промежуток времени, в том числе, при переходе между годами.
У себя тестил на различных вариантах настроек календаря и без него - работает.
Скачать скрипт
Для тех, у кого не работает первая ссылка
Перед вызовом функции не забываем ставить
ОтветитьУдалитьSET DATEFIRST 1
Также для SQL 2000 ниже SP4 нужно заменить функцию GETDATE() как передаваемый параметр в функцию расчета рабочего времени. Иначе функция не скомпилируется :-)
Можно ли прикладывать также пример скрипта для вызова функции? Тогда проблем с SET DATEFIRST 1 вообще не будет.
ОтветитьУдалитьСпасибо
SET DATEFIRST 1 - необходимо указывать потому, что решение предоставлено в виде функции и зашить в неё его нельзя.
ОтветитьУдалитьВот пример: длительность с 01 октября по 01 ноября 2009 в минутах по бизнес-календарю по умолчанию или же по первому в системе
SET DATEFIRST 1
SELECT [dbo].[dd_get_duration_{1E15DF2F-B4E8-44c4-A827-2F2309A4C8AC}] (NULL, '2009-10-01', '2009-11-01', 0, 1) "Duration"
ссылки не рабочие! :(
ОтветитьУдалитьПоявилось несколько вопросов и замечаний по процедурам:
ОтветитьУдалить1) Работает только на английской локали сервера. Если сервер русский, то все конвертирования перестают работать. Необходимо перед вызовами добавить SET DATEFORMAT MDY.
2) Похоже, что неверно работает расчёт выходных, если вообще нет календарей в системе. Например, 21.04.2012 считает как целый рабочий день, хотя это суббота. Похоже, что это как-то связано со строками типа
SELECT
@IsWeekend = 1
FROM
[dbo].[dvtable_{F12C1136-B351-4037-9DC9-21C042A238AF}] tDays WITH (NOLOCK)
INNER JOIN [dbo].[dvtable_{D8B0DEB3-FAE7-4C06-8728-B495985183C9}] tYears WITH (NOLOCK)
ON tDays.[ParentRowID] = tYears.[RowID]
WHERE
tYears.[InstanceID] = @CalendarID
AND tDays.[Type] IN (1, 2)
AND DATEADD(day, tDays.[DayNumber] - 1, CONVERT(varchar(4), tYears.[Year]) + '-01-01') = @EndDate
AND DATEPART(weekday, DATEADD(day, tDays.[DayNumber] - 1, CONVERT(varchar(4), tYears.[Year]) + '-01-01')) < 6
OR DATEPART(weekday, @EndDate) > 5
Если в системе нет календарей или не задан ни один нестандартный день (праздник или типа того), то запрос ничего не вернёт. И поэтому не произойдёт даже проверка выходного дня DATEPART(weekday, @EndDate) > 5.
Хотелось бы получить комментарии по этим пунктам.