Вопрос:
Я хочу рассчитать количество рабочих дней между двумя заданными датами. Например, если я хочу рассчитать рабочие дни между 2013-01-10 и 2013-01-15, результат должен быть 3 рабочих дня (я не принимаю во внимание последний день в этом интервале, и я вычитаю субботу и воскресенье). У меня есть следующий код, который работает для большинства случаев, кроме одного в моем примере.
SELECT (DATEDIFF(day, ‘2013-01-10’, ‘2013-01-15’)) — (CASE WHEN DATENAME(weekday, ‘2013-01-10’) = ‘Sunday’ THEN 1 ELSE 0 END) — (CASE WHEN DATENAME(weekday, DATEADD(day, -1, ‘2013-01-15’)) = ‘Saturday’ THEN 1 ELSE 0 END)
Как я могу это сделать? Должен ли я проходить через все дни и проверять их? Или есть простой способ сделать это.
Лучший ответ:
Пожалуйста, пожалуйста, используйте таблицу календаря. SQL Server ничего не знает о национальных праздниках, событиях компании, стихийных бедствиях и т.д. Таблицу календаря довольно легко построить, занимает очень мало места и будет в памяти, если на нее ссылаются достаточно.
Вот пример, который создает таблицу календаря с 30-летними датами (2000 → 2029), но требует только 200 КБ на диске (136 КБ, если вы используете сжатие страницы). Это почти гарантированно меньше, чем выделение памяти, необходимое для обработки некоторого CTE или другого набора во время выполнения.
CREATE TABLE dbo.Calendar ( dt DATE PRIMARY KEY, — use SMALLDATETIME if < SQL Server 2008 IsWorkDay BIT ); DECLARE @s DATE, @e DATE; SELECT @s = ‘2000-01-01’ , @e = ‘2029-12-31’; INSERT dbo.Calendar(dt, IsWorkDay) SELECT DATEADD(DAY, n-1, ‘2000-01-01’), 1 FROM ( SELECT TOP (DATEDIFF(DAY, @s, @e)+1) ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 ) AS x(n); SET DATEFIRST 1; — weekends UPDATE dbo.Calendar SET IsWorkDay = 0 WHERE DATEPART(WEEKDAY, dt) IN (6,7); — Christmas UPDATE dbo.Calendar SET IsWorkDay = 0 WHERE MONTH(dt) = 12 AND DAY(dt) = 25 AND IsWorkDay = 1; — continue with other holidays, known company events, etc.
Теперь запрос, который вам нужен, довольно просто написать:
SELECT COUNT(*) FROM dbo.Calendar WHERE dt >= ‘20130110’ AND dt < ‘20130115’ AND IsWorkDay = 1;
Дополнительная информация о таблицах календаря:
Дополнительная информация о генераторных установках без циклов:
Также будьте осторожны с небольшими вещами, например, полагаясь на выход на английском языке DATENAME. Я видел, как несколько приложений прерывались, потому что у некоторых пользователей был другой языковой параметр, и если вы полагаетесь на WEEKDAY, убедитесь, что вы правильно настроили параметр DATEFIRST…
Ответ №1
Для таких вещей я склонен поддерживать таблицу календаря, которая также включает в себя праздничные дни и т.д.
script я для этого заключается в следующем (обратите внимание, что я не писал его @, я забыл, где я его нашел)
SET DATEFIRST 1 SET NOCOUNT ON GO —Create ISO week Function (thanks BOL) CREATE FUNCTION ISOweek ( @DATE DATETIME ) RETURNS INT AS BEGIN DECLARE @ISOweek INT SET @ISOweek = DATEPART(wk, @DATE) + 1 — DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + ‘0104’) —Special cases: Jan 1-3 may belong to the previous year IF ( @ISOweek = 0 ) SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) — 1 AS CHAR(4)) + ’12’ + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1 —Special case: Dec 29-31 may belong to the next year IF ( ( DATEPART(mm, @DATE) = 12 ) AND ( ( DATEPART(dd, @DATE) — DATEPART(dw, @DATE) ) >= 28 ) ) SET @ISOweek = 1 RETURN(@ISOweek) END GO —END ISOweek —CREATE Easter algorithm function —Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689) CREATE FUNCTION fnDLA_GetEasterdate ( @year INT ) RETURNS CHAR(8) AS BEGIN — Easter date algorithm of Delambre DECLARE @A INT , @B INT , @C INT , @D INT , @E INT , @F INT , @G INT , @H INT , @I INT , @K INT , @L INT , @M INT , @O INT , @R INT SET @A = @YEAR % 19 SET @B = @YEAR / 100 SET @C = @YEAR % 100 SET @D = @B / 4 SET @E = @B % 4 SET @F = ( @B + 8 ) / 25 SET @G = ( @B — @F + 1 ) / 3 SET @H = ( 19 * @A + @B — @D — @G + 15 ) % 30 SET @I = @C / 4 SET @K = @C % 4 SET @L = ( 32 + 2 * @E + 2 * @I — @H — @K ) % 7 SET @M = ( @A + 11 * @H + 22 * @L ) / 451 SET @O = 22 + @H + @L — 7 * @M IF @O > 31 BEGIN SET @R = @O — 31 + 400 + @YEAR * 10000 END ELSE BEGIN SET @R = @O + 300 + @YEAR * 10000 END RETURN @R END GO —END fnDLA_GetEasterdate —Create the table CREATE TABLE MyDateTable ( FullDate DATETIME NOT NULL CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED , Period INT , ISOWeek INT , WorkingDay VARCHAR(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT ‘Y’ ) GO —End table create —Populate table with required dates DECLARE @DateFrom DATETIME , @DateTo DATETIME , @Period INT SET @DateFrom = CONVERT(DATETIME, ‘20000101’) —yyyymmdd (1st Jan 2000) amend as required SET @DateTo = CONVERT(DATETIME, ‘20991231’) —yyyymmdd (31st Dec 2099) amend as required WHILE @DateFrom <= @DateTo BEGIN SET @Period = CONVERT(INT, LEFT(CONVERT(VARCHAR(10), @DateFrom, 112), 6)) INSERT MyDateTable ( FullDate , Period , ISOWeek ) SELECT @DateFrom , @Period , dbo.ISOweek(@DateFrom) SET @DateFrom = DATEADD(dd, +1, @DateFrom) END GO —End population /* Start of WorkingDays UPDATE */ UPDATE MyDateTable SET WorkingDay = ‘B’ —B = Bank Holiday ———————————EASTER——————————————— WHERE FullDate = DATEADD(dd, -2, CONVERT(DATETIME, dbo.fnDLA_GetEasterdate(DATEPART(yy, FullDate)))) —Good Friday OR FullDate = DATEADD(dd, +1, CONVERT(DATETIME, dbo.fnDLA_GetEasterdate(DATEPART(yy, FullDate)))) —Easter Monday GO UPDATE MyDateTable SET WorkingDay = ‘B’ ———————————NEW YEAR——————————————- WHERE FullDate IN ( SELECT MIN(FullDate) FROM MyDateTable WHERE DATEPART(mm, FullDate) = 1 AND DATEPART(dw, FullDate) NOT IN ( 6, 7 ) GROUP BY DATEPART(yy, FullDate) ) ———————MAY BANK HOLIDAYS(Always Monday)—————————— OR FullDate IN ( SELECT MIN(FullDate) FROM MyDateTable WHERE DATEPART(mm, FullDate) = 5 AND DATEPART(dw, FullDate) = 1 GROUP BY DATEPART(yy, FullDate) ) OR FullDate IN ( SELECT MAX(FullDate) FROM MyDateTable WHERE DATEPART(mm, FullDate) = 5 AND DATEPART(dw, FullDate) = 1 GROUP BY DATEPART(yy, FullDate) ) ———————AUGUST BANK HOLIDAY(Always Monday)—————————— OR FullDate IN ( SELECT MAX(FullDate) FROM MyDateTable WHERE DATEPART(mm, FullDate) = 8 AND DATEPART(dw, FullDate) = 1 GROUP BY DATEPART(yy, FullDate) ) ———————XMAS(Move to next working day if on Sat/Sun)——————— OR FullDate IN ( SELECT CASE WHEN DATEPART(dw, FullDate) IN ( 6, 7 ) THEN DATEADD(dd, +2, FullDate) ELSE FullDate END FROM MyDateTable WHERE DATEPART(mm, FullDate) = 12 AND DATEPART(dd, FullDate) IN ( 25, 26 ) ) GO —————————————WEEKENDS————————————— UPDATE MyDateTable SET WorkingDay = ‘N’ WHERE DATEPART(dw, FullDate) IN ( 6, 7 ) GO /* End of WorkingDays UPDATE */ —SELECT * FROM MyDateTable ORDER BY 1 DROP FUNCTION fnDLA_GetEasterdate DROP FUNCTION ISOweek —DROP TABLE MyDateTable SET NOCOUNT OFF
Как только вы создали таблицу, найти количество рабочих дней легко:
SELECT COUNT(FullDate) AS WorkingDays FROM dbo.tbl_WorkingDays WHERE WorkingDay = ‘Y’ AND FullDate >= CONVERT(DATETIME, ’10/01/2013′, 103) AND FullDate < CONVERT(DATETIME, ’15/01/2013′, 103)
Обратите внимание, что этот script включает в себя праздничные дни в Великобритании, я не уверен, в каком регионе вы находитесь.
Ответ №2
Здесь простая функция, которая учитывает рабочие дни, не включая субботу и воскресенье (при подсчете праздничных дней не требуется):
CREATE FUNCTION dbo.udf_GetBusinessDays ( @START_DATE DATE, @END_DATE DATE ) RETURNS INT WITH EXECUTE AS CALLER AS BEGIN DECLARE @NUMBER_OF_DAYS INT = 0; DECLARE @DAY_COUNTER INT = 0; DECLARE @BUSINESS_DAYS INT = 0; DECLARE @CURRENT_DATE DATE; DECLARE @DAYNAME NVARCHAR(9) SET @NUMBER_OF_DAYS = DATEDIFF(DAY, @START_DATE, @END_DATE); WHILE @DAY_COUNTER <= @NUMBER_OF_DAYS BEGIN SET @CURRENT_DATE = DATEADD(DAY, @DAY_COUNTER, @START_DATE) SET @DAYNAME = DATENAME(WEEKDAY, @CURRENT_DATE) SET @DAY_COUNTER += 1 IF @DAYNAME = N’Saturday’ OR @DAYNAME = N’Sunday’ BEGIN CONTINUE END ELSE BEGIN SET @BUSINESS_DAYS += 1 END END RETURN @BUSINESS_DAYS END GO Ответ №3
Это метод, который я обычно использую (когда не используется таблица календаря):
DECLARE @T TABLE (Date1 DATE, Date2 DATE); INSERT @T VALUES (‘20130110’, ‘20130115’), (‘20120101’, ‘20130101’), (‘20120611’, ‘20120701’); SELECT Date1, Date2, WorkingDays FROM @T t CROSS APPLY ( SELECT [WorkingDays] = COUNT(*) FROM Master..spt_values s WHERE s.Number BETWEEN 1 AND DATEDIFF(DAY, t.date1, t.Date2) AND s.[Type] = ‘P’ AND DATENAME(WEEKDAY, DATEADD(DAY, s.number, t.Date1)) NOT IN (‘Saturday’, ‘Sunday’) ) wd
Если у меня есть таблица с праздниками, вы можете добавить это тоже:
SELECT Date1, Date2, WorkingDays FROM @T t CROSS APPLY ( SELECT [WorkingDays] = COUNT(*) FROM Master..spt_values s WHERE s.Number BETWEEN 1 AND DATEDIFF(DAY, t.date1, t.Date2) AND s.[Type] = ‘P’ AND DATENAME(WEEKDAY, DATEADD(DAY, s.number, t.Date1)) NOT IN (‘Saturday’, ‘Sunday’) AND NOT EXISTS ( SELECT 1 FROM HolidayTable ht WHERE ht.Date = DATEADD(DAY, s.number, t.Date1) ) ) wd
Вышеуказанное будет работать только в том случае, если ваши даты находятся в пределах 2047 дней друг от друга, если вы, вероятно, будете рассчитывать большие диапазоны дат, вы можете использовать это:
SELECT Date1, Date2, WorkingDays FROM @T t CROSS APPLY ( SELECT [WorkingDays] = COUNT(*) FROM ( SELECT [Number] = ROW_NUMBER() OVER(ORDER BY s.number) FROM Master..spt_values s CROSS JOIN Master..spt_values s2 ) s WHERE s.Number BETWEEN 1 AND DATEDIFF(DAY, t.date1, t.Date2) AND DATENAME(WEEKDAY, DATEADD(DAY, s.number, t.Date1)) NOT IN (‘Saturday’, ‘Sunday’) ) wd Ответ №4
Я сделал свой код в SQL SERVER 2008 (MS SQL). Он отлично работает для меня. Надеюсь, это поможет вам.
DECLARE @COUNTS int, @STARTDATE date, @ENDDATE date SET @STARTDATE =’01/21/2013′ /*Start date in mm/dd/yyy */ SET @ENDDATE =’01/26/2013′ /*End date in mm/dd/yyy */ SET @COUNTS=0 WHILE (@STARTDATE<=@ENDDATE) BEGIN /*Check for holidays*/ IF ( DATENAME(weekday,@STARTDATE)<>’Saturday’ and DATENAME(weekday,@STARTDATE)<>’Sunday’) BEGIN SET @COUNTS=@COUNTS+1 END SET @STARTDATE=DATEADD(day,1,@STARTDATE) END /* Display the no of working days */ SELECT @COUNTS
