Microsoft SQL Server
Скрипты
T-SQL

Подсчет рабочих дней в периоде

Опубликовано: 23 авг 05
Рейтинг:

Автор: SanyL
Прислал: SanyL

Вот задался задачей подсчета количества рабочих дней (рабочими будем считать все кроме субботы и воскресенья) в некотором интервале дат. Когда задал вопрос на форуме то мало вариантов было предложено, а интересных практически не было. Здесь хочу описать те два варианта которые удалось придумать, и которые мне кажутся наиболее удачными.

ПЕРВЫЙ ВАРИАНТ

DECLARE 
  @d1 SMALLDATETIME,
  @d2 SMALLDATETIME,
  @d SMALLDATETIME,
  @kol INT
  SET @d1 = '1951-05-25'
  SET @d2 = '1962-11-15'
  SET @kol = 0
  SET @d=@d1

WHILE @d <= @d2
BEGIN 
 SET @kol = @kol + (CASE WHEN DATENAME(dw, @d) IN ('Saturday','Sunday') THEN 1 ELSE 0 END)
 SET @d = @d + 1
END
SELECT DATEDIFF(DAY, @d1, @d2)-@kol

В принципе в этом варианте в самом цикле можно сразу считать эти самые «рабочие дни», для этого достаточно заменить IN на NOT IN. Этот вариант достаточно прост и, на мой взгляд, не нуждается в дополнительном разборе и комментариях.

ВТОРОЙ ВАРИАНТ

DECLARE 
  @d1 SMALLDATETIME,
  @d2 SMALLDATETIME
  SET @d1 = '1953-12-26'
  SET @d2 = '1964-10-25'

SELECT (ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7)*5+
(CASE WHEN (DATENAME(dw, (@d1+(ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7)*7)+1) IN ('Sunday','Saturday'))
                    OR (((@d1+(ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7)*7)+1) > @d2) THEN 0 ELSE 1 END) +
(CASE WHEN (DATENAME(dw, (@d1+(ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7)*7)+2) IN ('Sunday','Saturday')) 
		    OR (((@d1+(ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7)*7)+2) > @d2) THEN 0 ELSE 1 END)+
(CASE WHEN (DATENAME(dw, (@d1+(ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7)*7)+3) IN ('Sunday','Saturday')) 
		    OR (((@d1+(ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7)*7)+3) > @d2) THEN 0 ELSE 1 END)+
(CASE WHEN (DATENAME(dw, (@d1+(ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7)*7)+4) IN ('Sunday','Saturday'))
		    OR (((@d1+(ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7)*7)+4) > @d2) THEN 0 ELSE 1 END)+
(CASE WHEN (DATENAME(dw, (@d1+(ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7)*7)+5) IN ('Sunday','Saturday')) 
		    OR (((@d1+(ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7)*7)+5) > @d2) THEN 0 ELSE 1 END)+
(CASE WHEN (DATENAME(dw, (@d1+(ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7)*7)+6) IN ('Sunday','Saturday')) 
		    OR (((@d1+(ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7)*7)+6) > @d2) THEN 0 ELSE 1 END)+
(CASE WHEN DATENAME(dw,@d1) IN ('Sunday','Saturday') THEN 0 ELSE 1 END)

Вот этот вариант, на мой взгляд более интересен. Чем? Ну например хотя бы отсутствием цикла, его можно использовать в виде подзапроса, заменив @d1 и @d2 на значения дат соответствующих полей, да и разобрав его можно увидеть достаточно интересные вещи. Честно скажу – довелось наступить на ряд «грабель». Но давайте потихоньку: на первый взгляд очень простая задача, да и функция DATEDIFF(WEEK,@d1,@d2) должна вернуть количество полных недель в промежутке, а вот и не повезло… Оказалось что эта функция считает количество воскресений в промежутке. Весь смысл этой, несколько громоздкой конструкции вот в этом ROUND(DATEDIFF(DAY,@d1,@d2), 0, 1)/7. Здесь я считаю количество недельных периодов. Мы знаем что в неделе 5 рабочих дней и 2 выходных, ну а далее остается разобрать остаток который может быть от 1 до 6-ти дней. Но давайте вспомним про конструкцию, которая считает количество воскресений, сначала показалось – что мол вот враги, однако тут же родилась интересная идея посчитать количество воскресений умножить на 2 (чтобы получить субботы и воскресенья) и учесть концы интервалов, для различных частных случаев, и в итоге было получено одно из самых красивых решений, на мой взгляд.

ТРЕТИЙ ВАРИАНТ

DECLARE 
  @d1 SMALLDATETIME,
  @d2 SMALLDATETIME
  SET @d1 = '1942-11-15'
  SET @d2 = '1943-12-26'


SELECT DATEDIFF(DAY,@d1,@d2)+1-
(
DATEDIFF(WEEK,@d1,@d2)*2+
(CASE WHEN (DATENAME(dw,@d1) IN ('Sunday')) THEN 1 ELSE 0 END)+
(CASE WHEN (DATENAME(dw,@d2) IN ('Saturday')) THEN 1 ELSE 0 END)
)

Оказалась, в итоге, совсем детская задача

Стоит заметить что у меня @@datefirst=7

Комментарии


  • А нет, всё верно с третьим вариантом.
    Вот ещё вариант:
    DECLARE
    @StartDate DATETIME
    ,@EndDate DATETIME

    SET @StartDate = '2014-11-17'
    SET @EndDate = '2014-11-17'

    SELECT @StartDate, @EndDate

    SELECT
    (N/7)*5+dN-SSCount
    FROM(
    SELECT
    DATEDIFF(DAY, @StartDate, @EndDate)+1 N,
    (DATEDIFF(DAY, @StartDate, @EndDate)+1)%7 dN
    )T OUTER APPLY(
    SELECT COUNT(*)SSCount
    FROM (VALUES(0),(1),(2),(3),(4),(5))X(X)
    WHERE dN>X AND DATENAME(DW,@StartDate+X)IN('Sunday','Saturday')
    )OA

  • не могу вставить код:(

  • ТРЕТИЙ ВАРИАНТ неверный:

    DECLARE
    @d1 SMALLDATETIME,
    @d2 SMALLDATETIME
    SET @d1 = '2014-10-15'
    SET @d2 = '2014-10-18'


    SELECT DATEDIFF(DAY,@d1,@d2)+1-
    (
    DATEDIFF(WEEK,@d1,@d2)*2+
    (CASE WHEN (DATENAME(dw,@d1) IN ('Sunday')) THEN 1 ELSE 0 END)+
    (CASE WHEN (DATENAME(dw,@d2) IN ('Saturday')) THEN 1 ELSE 0 END)
    )

    показывает 3, хотя рабочих дня 2, пн и вт

  • --То же, но отвязанное от локали сервера
    set datefirst 1;
    declare @d1 datetime = '20120607',
    @d2 datetime = '20130606';
    with cte
    as
    (
    select @d1 as col
    union all
    select dateadd( day, 1, cte.col )
    from cte
    where col < @d2
    )
    select count(col) as count_working_days
    from cte
    where datepart( dw, col ) not in ( 6, 7 )
    option ( maxrecursion 0 );

  • --Для версий сервера 2005 и новее
    declare @d1 datetime = '2012-06-07',
    @d2 datetime = '2013-06-06';
    with cte
    as
    (
    select @d1 as col
    union all
    select dateadd( day, 1, cte.col )
    from cte
    where col < @d2
    )
    select count(col) as count_working_days
    from cte
    where datename( dw, col ) not in ( 'Saturday', 'Sunday' )
    option ( maxrecursion 0 )



Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Скрипты / T-SQL / Подсчет рабочих дней в периоде