Опубликовано: 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 )