반응형
두 날짜 사이의 근무일 계산
SQL Server에서 두 날짜 사이의 작업 일수를 어떻게 계산할 수 있습니까?
월요일부터 금요일까지는 T-SQL이어야합니다.
월요일부터 금요일까지 평일에는 다음과 같이 단일 SELECT로 수행 할 수 있습니다.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
공휴일을 포함하려면 약간의 휴가를 보내야합니다 ...
에서 직장 일을 계산 이 주제에 대한 좋은 기사를 찾을 수 있지만, 당신이 볼 수로는 고급 없습니다.
--Changing current database to the Master database allows function to be shared by everyone.
USE MASTER
GO
--If the function already exists, drop it.
IF EXISTS
(
SELECT *
FROM dbo.SYSOBJECTS
WHERE ID = OBJECT_ID(N'[dbo].[fn_WorkDays]')
AND XType IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fn_WorkDays]
GO
CREATE FUNCTION dbo.fn_WorkDays
--Presets
--Define the input parameters (OK if reversed by mistake).
(
@StartDate DATETIME,
@EndDate DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)
--Define the output data type.
RETURNS INT
AS
--Calculate the RETURN of the function.
BEGIN
--Declare local variables
--Temporarily holds @EndDate during date reversal.
DECLARE @Swap DATETIME
--If the Start Date is null, return a NULL and exit.
IF @StartDate IS NULL
RETURN NULL
--If the End Date is null, populate with Start Date value so will have two dates (required by DATEDIFF below).
IF @EndDate IS NULL
SELECT @EndDate = @StartDate
--Strip the time element from both dates (just to be safe) by converting to whole days and back to a date.
--Usually faster than CONVERT.
--0 is a date (01/01/1900 00:00:00.000)
SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate), 0),
@EndDate = DATEADD(dd,DATEDIFF(dd,0,@EndDate) , 0)
--If the inputs are in the wrong order, reverse them.
IF @StartDate > @EndDate
SELECT @Swap = @EndDate,
@EndDate = @StartDate,
@StartDate = @Swap
--Calculate and return the number of workdays using the input parameters.
--This is the meat of the function.
--This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes.
RETURN (
SELECT
--Start with total number of days including weekends
(DATEDIFF(dd,@StartDate, @EndDate)+1)
--Subtact 2 days for each full weekend
-(DATEDIFF(wk,@StartDate, @EndDate)*2)
--If StartDate is a Sunday, Subtract 1
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'
THEN 1
ELSE 0
END)
--If EndDate is a Saturday, Subtract 1
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday'
THEN 1
ELSE 0
END)
)
END
GO
사용자 정의 달력을 사용해야하는 경우 검사와 매개 변수를 추가해야 할 수 있습니다. 바라건대 좋은 출발점이 될 것입니다.
Bogdan Maxim & Peter Mortensen에게 모든 크레딧. 이 게시물은 방금 함수에 휴일을 추가했습니다 (날짜 시간 필드가 "HolDate"인 "tblHolidays"테이블이 있다고 가정합니다.
--Changing current database to the Master database allows function to be shared by everyone.
USE MASTER
GO
--If the function already exists, drop it.
IF EXISTS
(
SELECT *
FROM dbo.SYSOBJECTS
WHERE ID = OBJECT_ID(N'[dbo].[fn_WorkDays]')
AND XType IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fn_WorkDays]
GO
CREATE FUNCTION dbo.fn_WorkDays
--Presets
--Define the input parameters (OK if reversed by mistake).
(
@StartDate DATETIME,
@EndDate DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)
--Define the output data type.
RETURNS INT
AS
--Calculate the RETURN of the function.
BEGIN
--Declare local variables
--Temporarily holds @EndDate during date reversal.
DECLARE @Swap DATETIME
--If the Start Date is null, return a NULL and exit.
IF @StartDate IS NULL
RETURN NULL
--If the End Date is null, populate with Start Date value so will have two dates (required by DATEDIFF below).
IF @EndDate IS NULL
SELECT @EndDate = @StartDate
--Strip the time element from both dates (just to be safe) by converting to whole days and back to a date.
--Usually faster than CONVERT.
--0 is a date (01/01/1900 00:00:00.000)
SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate), 0),
@EndDate = DATEADD(dd,DATEDIFF(dd,0,@EndDate) , 0)
--If the inputs are in the wrong order, reverse them.
IF @StartDate > @EndDate
SELECT @Swap = @EndDate,
@EndDate = @StartDate,
@StartDate = @Swap
--Calculate and return the number of workdays using the input parameters.
--This is the meat of the function.
--This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes.
RETURN (
SELECT
--Start with total number of days including weekends
(DATEDIFF(dd,@StartDate, @EndDate)+1)
--Subtact 2 days for each full weekend
-(DATEDIFF(wk,@StartDate, @EndDate)*2)
--If StartDate is a Sunday, Subtract 1
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'
THEN 1
ELSE 0
END)
--If EndDate is a Saturday, Subtract 1
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday'
THEN 1
ELSE 0
END)
--Subtract all holidays
-(Select Count(*) from [DB04\DB04].[Gateway].[dbo].[tblHolidays]
where [HolDate] between @StartDate and @EndDate )
)
END
GO
-- Test Script
/*
declare @EndDate datetime= dateadd(m,2,getdate())
print @EndDate
select [Master].[dbo].[fn_WorkDays] (getdate(), @EndDate)
*/
작업 일을 계산하는 또 다른 방법은 기본적으로 날짜 범위를 반복하고 월요일에서 금요일까지 일이 발견 될 때마다 1 씩 증가시키는 WHILE 루프를 사용하는 것입니다. WHILE 루프를 사용하여 근무일을 계산하기위한 전체 스크립트는 다음과 같습니다.
CREATE FUNCTION [dbo].[fn_GetTotalWorkingDaysUsingLoop]
(@DateFrom DATE,
@DateTo DATE
)
RETURNS INT
AS
BEGIN
DECLARE @TotWorkingDays INT= 0;
WHILE @DateFrom <= @DateTo
BEGIN
IF DATENAME(WEEKDAY, @DateFrom) IN('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')
BEGIN
SET @TotWorkingDays = @TotWorkingDays + 1;
END;
SET @DateFrom = DATEADD(DAY, 1, @DateFrom);
END;
RETURN @TotWorkingDays;
END;
GO
WHILE 루프 옵션은 더 깨끗하고 적은 코드 줄을 사용하지만 특히 날짜 범위가 몇 년에 걸쳐있을 때 환경에서 성능 병목 현상이 발생할 가능성이 있습니다.
https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/ 기사에서 근무일과 시간을 계산하는 방법에 대한 더 많은 방법을 볼 수 있습니다
을 사용하는 함수로 허용되는 답변의 버전을 사용 DATEPART하므로 다음과 같이 줄에서 문자열 비교를 수행 할 필요가 없습니다.
DATENAME(dw, @StartDate) = 'Sunday'
어쨌든, 여기 내 사업 날짜 분석 기능이 있습니다.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION BDATEDIFF
(
@startdate as DATETIME,
@enddate as DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @res int
SET @res = (DATEDIFF(dd, @startdate, @enddate) + 1)
-(DATEDIFF(wk, @startdate, @enddate) * 2)
-(CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 ELSE 0 END)
-(CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END)
RETURN @res
END
GO
DECLARE @TotalDays INT,@WorkDays INT
DECLARE @ReducedDayswithEndDate INT
DECLARE @WeekPart INT
DECLARE @DatePart INT
SET @TotalDays= DATEDIFF(day, @StartDate, @EndDate) +1
SELECT @ReducedDayswithEndDate = CASE DATENAME(weekday, @EndDate)
WHEN 'Saturday' THEN 1
WHEN 'Sunday' THEN 2
ELSE 0 END
SET @TotalDays=@TotalDays-@ReducedDayswithEndDate
SET @WeekPart=@TotalDays/7;
SET @DatePart=@TotalDays%7;
SET @WorkDays=(@WeekPart*5)+@DatePart
RETURN @WorkDays
(저는 특권에 대한 의견이 수줍어합니다.)
CMS의 우아한 솔루션 에서 +1 일을 취소하기로 결정한 경우 시작 날짜와 종료 날짜가 같은 주말에 있으면 부정적인 답변을 얻을 수 있습니다. 즉, 2008/10/26에서 2008/10/26은 -1을 반환합니다.
오히려 단순한 솔루션 :
select @Result = (..CMS's answer..)
if (@Result < 0)
select @Result = 0
RETURN @Result
.. 또한 종료 날짜 이후 시작 날짜가있는 모든 잘못된 게시물 을 0으로 설정합니다. 당신이 찾고 있거나 찾지 못하는 것.
휴일을 포함하여 날짜 간의 차이를 위해 다음과 같이했습니다.
1) 휴일 표 :
CREATE TABLE [dbo].[Holiday](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Date] [datetime] NOT NULL)
2) 계획표가 다음과 같으며 비어있는 Work_Days 열을 채우고 싶었습니다.
CREATE TABLE [dbo].[Plan_Phase](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Id_Plan] [int] NOT NULL,
[Id_Phase] [int] NOT NULL,
[Start_Date] [datetime] NULL,
[End_Date] [datetime] NULL,
[Work_Days] [int] NULL)
3) 따라서 "Work_Days"를 나중에 내 열에 채우려면 다음을 수행해야했습니다.
SELECT Start_Date, End_Date,
(DATEDIFF(dd, Start_Date, End_Date) + 1)
-(DATEDIFF(wk, Start_Date, End_Date) * 2)
-(SELECT COUNT(*) From Holiday Where Date >= Start_Date AND Date <= End_Date)
-(CASE WHEN DATENAME(dw, Start_Date) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, End_Date) = 'Saturday' THEN 1 ELSE 0 END)
-(CASE WHEN (SELECT COUNT(*) From Holiday Where Start_Date = Date) > 0 THEN 1 ELSE 0 END)
-(CASE WHEN (SELECT COUNT(*) From Holiday Where End_Date = Date) > 0 THEN 1 ELSE 0 END) AS Work_Days
from Plan_Phase
내가 도울 수 있기를 바랍니다.
건배
다음은 잘 작동하는 버전입니다. 휴일 테이블에는 회사에서 준수하는 휴일이 포함 된 Holiday_date 열이 포함됩니다.
DECLARE @RAWDAYS INT
SELECT @RAWDAYS = DATEDIFF(day, @StartDate, @EndDate )--+1
-( 2 * DATEDIFF( week, @StartDate, @EndDate ) )
+ CASE WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN 1 ELSE 0 END
- CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END
SELECT @RAWDAYS - COUNT(*)
FROM HOLIDAY NumberOfBusinessDays
WHERE [Holiday_Date] BETWEEN @StartDate+1 AND @EndDate
나는 이것이 오래된 질문이라는 것을 알고 있지만 여러 항목이 있고 올바르게 누적되기 위해 일이 필요하기 때문에 시작 날짜를 제외한 근무일에 대한 수식이 필요했습니다.
반복적이지 않은 답변 중 어느 것도 나를 위해 일하지 않았습니다.
나는 같은 정의를 사용
자정부터 월요일, 화요일, 수요일, 목요일과 금요일이 지나는 횟수
(다른 사람들은 월요일 대신 토요일 자정부터 토요일까지 계산할 수 있습니다)
나는이 공식으로 끝났다
SELECT DATEDIFF(day, @StartDate, @EndDate) /* all midnights passed */
- DATEDIFF(week, @StartDate, @EndDate) /* remove sunday midnights */
- DATEDIFF(week, DATEADD(day, 1, @StartDate), DATEADD(day, 1, @EndDate)) /* remove saturday midnights */
날짜 테이블 사용하기 :
DECLARE
@StartDate date = '2014-01-01',
@EndDate date = '2014-01-31';
SELECT
COUNT(*) As NumberOfWeekDays
FROM dbo.Calendar
WHERE CalendarDate BETWEEN @StartDate AND @EndDate
AND IsWorkDay = 1;
없는 경우 숫자 표를 사용할 수 있습니다.
DECLARE
@StartDate datetime = '2014-01-01',
@EndDate datetime = '2014-01-31';
SELECT
SUM(CASE WHEN DATEPART(dw, DATEADD(dd, Number-1, @StartDate)) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) As NumberOfWeekDays
FROM dbo.Numbers
WHERE Number <= DATEDIFF(dd, @StartDate, @EndDate) + 1 -- Number table starts at 1, we want a 0 base
그들은 빠르며 모호성 / 복잡성을 취합니다. 첫 번째 옵션이 가장 좋지만 달력 테이블이 없으면 CTE를 사용하여 숫자 테이블을 만들 수 있습니다.
이것은 기본적으로 특정 언어 설정에 의존하지 않는 CMS의 답변입니다. 일반 촬영을하기 때문에 모든 @@datefirst설정에서도 작동 합니다.
datediff(day, <start>, <end>) + 1 - datediff(week, <start>, <end>) * 2
/* if start is a Sunday, adjust by -1 */
+ case when datepart(weekday, <start>) = 8 - @@datefirst then -1 else 0 end
/* if end is a Saturday, adjust by -1 */
+ case when datepart(weekday, <end>) = (13 - @@datefirst) % 7 + 1 then -1 else 0 end
datediff(week, ...)그 표현이 결정하고 수정할 필요가 없습니다 그래서 항상 주 동안 토요일에 일요일 경계를 사용합니다 (금요일만큼 평일의 우리의 정의는 지속적으로 월요일한다.) 날 번호가하는 릅니다에 따라 @@datefirst설정하고, 수정 된 계산은 일부 모듈 식 산술의 작은 합병증으로이 수정을 처리합니다.
토요일 / 일요일을 처리하는 더 확실한 방법은 요일 값을 추출하기 전에 날짜를 변환하는 것입니다. 이동 후, 값은 일요일에 1로 시작하고 토요일에 7로 끝나는 고정 된 (그리고 아마도 더 친숙한) 번호로 정렬됩니다.
datediff(day, <start>, <end>) + 1 - datediff(week, <start>, <end>) * 2
+ case when datepart(weekday, dateadd(day, @@datefirst, <start>)) = 1 then -1 else 0 end
+ case when datepart(weekday, dateadd(day, @@datefirst, <end>)) = 7 then -1 else 0 end
적어도 2002 년까지 Itzik Ben-Gan 기사 와이 솔루션의 형태를 추적했습니다. ( https://technet.microsoft.com/en-us/library/aa175781(v=sql.80).aspx ) 최신 date유형은 날짜 산술을 허용하지 않으므로 약간의 조정이 필요하지만 그렇지 않은 경우 동일합니다.
편집 : +1어떻게 든 중단 된 것을 다시 추가 했습니다. 이 방법은 항상 시작일과 종료일을 계산한다는 점도 주목할 가치가 있습니다. 또한 종료 날짜가 시작 날짜 또는 그 이후라고 가정합니다.
DECLARE @StartDate datetime,@EndDate datetime
select @StartDate='3/2/2010', @EndDate='3/7/2010'
DECLARE @TotalDays INT,@WorkDays INT
DECLARE @ReducedDayswithEndDate INT
DECLARE @WeekPart INT
DECLARE @DatePart INT
SET @TotalDays= DATEDIFF(day, @StartDate, @EndDate) +1
SELECT @ReducedDayswithEndDate = CASE DATENAME(weekday, @EndDate)
WHEN 'Saturday' THEN 1
WHEN 'Sunday' THEN 2
ELSE 0 END
SET @TotalDays=@TotalDays-@ReducedDayswithEndDate
SET @WeekPart=@TotalDays/7;
SET @DatePart=@TotalDays%7;
SET @WorkDays=(@WeekPart*5)+@DatePart
SELECT @WorkDays
CREATE FUNCTION x
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @Teller INT
SET @StartDate = DATEADD(dd,1,@StartDate)
SET @Teller = 0
IF DATEDIFF(dd,@StartDate,@EndDate) <= 0
BEGIN
SET @Teller = 0
END
ELSE
BEGIN
WHILE
DATEDIFF(dd,@StartDate,@EndDate) >= 0
BEGIN
IF DATEPART(dw,@StartDate) < 6
BEGIN
SET @Teller = @Teller + 1
END
SET @StartDate = DATEADD(dd,1,@StartDate)
END
END
RETURN @Teller
END
나는 여기에 다양한 예를 들었지만, 특정 상황에서 우리는 배송을위한 @PromisedDate와 품목의 실제 수령을위한 @ReceivedDate를 가지고 있습니다. "PromisedDate"이전에 항목을 받았을 때 달력 순서로 함수에 전달 된 날짜를 주문하지 않으면 계산이 제대로 이루어지지 않았습니다. 매번 날짜를 확인하고 싶지 않아서이 기능을 변경했습니다.
Create FUNCTION [dbo].[fnGetBusinessDays]
(
@PromiseDate date,
@ReceivedDate date
)
RETURNS integer
AS
BEGIN
DECLARE @days integer
SELECT @days =
Case when @PromiseDate > @ReceivedDate Then
DATEDIFF(d,@PromiseDate,@ReceivedDate) +
ABS(DATEDIFF(wk,@PromiseDate,@ReceivedDate)) * 2 +
CASE
WHEN DATENAME(dw, @PromiseDate) <> 'Saturday' AND DATENAME(dw, @ReceivedDate) = 'Saturday' THEN 1
WHEN DATENAME(dw, @PromiseDate) = 'Saturday' AND DATENAME(dw, @ReceivedDate) <> 'Saturday' THEN -1
ELSE 0
END +
(Select COUNT(*) FROM CompanyHolidays
WHERE HolidayDate BETWEEN @ReceivedDate AND @PromiseDate
AND DATENAME(dw, HolidayDate) <> 'Saturday' AND DATENAME(dw, HolidayDate) <> 'Sunday')
Else
DATEDIFF(d,@PromiseDate,@ReceivedDate) -
ABS(DATEDIFF(wk,@PromiseDate,@ReceivedDate)) * 2 -
CASE
WHEN DATENAME(dw, @PromiseDate) <> 'Saturday' AND DATENAME(dw, @ReceivedDate) = 'Saturday' THEN 1
WHEN DATENAME(dw, @PromiseDate) = 'Saturday' AND DATENAME(dw, @ReceivedDate) <> 'Saturday' THEN -1
ELSE 0
END -
(Select COUNT(*) FROM CompanyHolidays
WHERE HolidayDate BETWEEN @PromiseDate and @ReceivedDate
AND DATENAME(dw, HolidayDate) <> 'Saturday' AND DATENAME(dw, HolidayDate) <> 'Sunday')
End
RETURN (@days)
END
주어진 날짜에 작업 일을 추가해야하는 경우 아래 설명 된 달력 테이블에 따라 함수를 작성할 수 있습니다.
CREATE TABLE Calendar
(
dt SMALLDATETIME PRIMARY KEY,
IsWorkDay BIT
);
--fill the rows with normal days, weekends and holidays.
create function AddWorkingDays (@initialDate smalldatetime, @numberOfDays int)
returns smalldatetime as
begin
declare @result smalldatetime
set @result =
(
select t.dt from
(
select dt, ROW_NUMBER() over (order by dt) as daysAhead from calendar
where dt > @initialDate
and IsWorkDay = 1
) t
where t.daysAhead = @numberOfDays
)
return @result
end
우리 나라에서는 토요일과 일요일이 휴무일입니다.
저에게는 @StartDate와 @EndDate의 시간이 중요합니다.
CREATE FUNCTION [dbo].[fnGetCountWorkingBusinessDays]
(
@StartDate as DATETIME,
@EndDate as DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @res int
SET @StartDate = CASE
WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN DATEADD(dd, 2, DATEDIFF(dd, 0, @StartDate))
WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN DATEADD(dd, 1, DATEDIFF(dd, 0, @StartDate))
ELSE @StartDate END
SET @EndDate = CASE
WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN DATEADD(dd, 0, DATEDIFF(dd, 0, @EndDate))
WHEN DATENAME(dw, @EndDate) = 'Sunday' THEN DATEADD(dd, -1, DATEDIFF(dd, 0, @EndDate))
ELSE @EndDate END
SET @res =
(DATEDIFF(hour, @StartDate, @EndDate) / 24)
- (DATEDIFF(wk, @StartDate, @EndDate) * 2)
SET @res = CASE WHEN @res < 0 THEN 0 ELSE @res END
RETURN @res
END
GO
다음과 같은 함수를 작성하십시오.
CREATE FUNCTION dbo.fn_WorkDays(@StartDate DATETIME, @EndDate DATETIME= NULL )
RETURNS INT
AS
BEGIN
DECLARE @Days int
SET @Days = 0
IF @EndDate = NULL
SET @EndDate = EOMONTH(@StartDate) --last date of the month
WHILE DATEDIFF(dd,@StartDate,@EndDate) >= 0
BEGIN
IF DATENAME(dw, @StartDate) <> 'Saturday'
and DATENAME(dw, @StartDate) <> 'Sunday'
and Not ((Day(@StartDate) = 1 And Month(@StartDate) = 1)) --New Year's Day.
and Not ((Day(@StartDate) = 4 And Month(@StartDate) = 7)) --Independence Day.
BEGIN
SET @Days = @Days + 1
END
SET @StartDate = DATEADD(dd,1,@StartDate)
END
RETURN @Days
END
다음과 같이 함수를 호출 할 수 있습니다.
select dbo.fn_WorkDays('1/1/2016', '9/25/2016')
또는 같은 :
select dbo.fn_WorkDays(StartDate, EndDate)
from table1
Create Function dbo.DateDiff_WeekDays
(
@StartDate DateTime,
@EndDate DateTime
)
Returns Int
As
Begin
Declare @Result Int = 0
While @StartDate <= @EndDate
Begin
If DateName(DW, @StartDate) not in ('Saturday','Sunday')
Begin
Set @Result = @Result +1
End
Set @StartDate = DateAdd(Day, +1, @StartDate)
End
Return @Result
종료
아래 TSQL이 상당히 우아한 솔루션이라는 것을 알았습니다 (함수를 실행할 권한이 없습니다). 나는 DATEDIFF무시한 것을 발견하고 DATEFIRST첫 주를 월요일로 원했습니다. 또한 첫 번째 근무일을 0으로 설정하고 주말에 떨어지면 월요일이 0이됩니다. 이것은 약간 다른 요구 사항을 가진 사람에게 도움이 될 수 있습니다. :)
공휴일은 취급하지 않습니다
SET DATEFIRST 1
SELECT
,(DATEDIFF(DD, [StartDate], [EndDate]))
-(DATEDIFF(wk, [StartDate], [EndDate]))
-(DATEDIFF(wk, DATEADD(dd,-@@DATEFIRST,[StartDate]), DATEADD(dd,-@@DATEFIRST,[EndDate]))) AS [WorkingDays]
FROM /*Your Table*/
한 가지 방법은 요일이 토요일 또는 일요일이 아닌지 확인하고 플래그를 지정하는 케이스 표현식 (주중은 1, 주말은 0)과 함께 시작부터 끝까지 '날짜'를 걷는 것입니다. 그리고 마지막으로 요일 수를 알려주는 sum 플래그 (다른 플래그가 0이므로 1 플래그 수와 같습니다).
시작 날짜부터 종료 날짜까지 '루핑'에 대한 일련의 숫자를 생성하는 GetNums (startNumber, endNumber) 유형의 유틸리티 함수를 사용할 수 있습니다. 구현에 대해서는 http://tsql.solidq.com/SourceCodes/GetNums.txt 를 참조하십시오 . 공휴일에 맞게 로직을 확장 할 수도 있습니다 (휴일 테이블이있는 경우 등).
declare @date1 as datetime = '19900101'
declare @date2 as datetime = '19900120'
select sum(case when DATENAME(DW,currentDate) not in ('Saturday', 'Sunday') then 1 else 0 end) as noOfWorkDays
from dbo.GetNums(0,DATEDIFF(day,@date1, @date2)-1) as Num
cross apply (select DATEADD(day,n,@date1)) as Dates(currentDate)
DATEDIFF와 마찬가지로 종료 날짜를 간격의 일부로 간주하지 않습니다. 예를 들어 @StartDate와 @EndDate 사이의 일요일 수는 "초기"월요일과 @EndDate 사이의 일요일 수에서이 "초기"월요일과 @StartDate 사이의 일요일 수를 뺀 것입니다. 이를 알면 다음과 같이 근무일 수를 계산할 수 있습니다.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2018/01/01'
SET @EndDate = '2019/01/01'
SELECT DATEDIFF(Day, @StartDate, @EndDate) -- Total Days
- (DATEDIFF(Day, 0, @EndDate)/7 - DATEDIFF(Day, 0, @StartDate)/7) -- Sundays
- (DATEDIFF(Day, -1, @EndDate)/7 - DATEDIFF(Day, -1, @StartDate)/7) -- Saturdays
친애하는!
내 솔루션을 만들기 위해 다른 사람들의 아이디어를 빌 렸습니다. 주말과 미국 공휴일을 무시하기 위해 인라인 코드를 사용합니다. 내 환경에서 EndDate는 null 일 수 있지만 StartDate보다 우선하지는 않습니다.
CREATE FUNCTION dbo.ufn_CalculateBusinessDays(
@StartDate DATE,
@EndDate DATE = NULL)
RETURNS INT
AS
BEGIN
DECLARE @TotalBusinessDays INT = 0;
DECLARE @TestDate DATE = @StartDate;
IF @EndDate IS NULL
RETURN NULL;
WHILE @TestDate < @EndDate
BEGIN
DECLARE @Month INT = DATEPART(MM, @TestDate);
DECLARE @Day INT = DATEPART(DD, @TestDate);
DECLARE @DayOfWeek INT = DATEPART(WEEKDAY, @TestDate) - 1; --Monday = 1, Tuesday = 2, etc.
DECLARE @DayOccurrence INT = (@Day - 1) / 7 + 1; --Nth day of month (3rd Monday, for example)
--Increment business day counter if not a weekend or holiday
SELECT @TotalBusinessDays += (
SELECT CASE
--Saturday OR Sunday
WHEN @DayOfWeek IN (6,7) THEN 0
--New Year's Day
WHEN @Month = 1 AND @Day = 1 THEN 0
--MLK Jr. Day
WHEN @Month = 1 AND @DayOfWeek = 1 AND @DayOccurrence = 3 THEN 0
--G. Washington's Birthday
WHEN @Month = 2 AND @DayOfWeek = 1 AND @DayOccurrence = 3 THEN 0
--Memorial Day
WHEN @Month = 5 AND @DayOfWeek = 1 AND @Day BETWEEN 25 AND 31 THEN 0
--Independence Day
WHEN @Month = 7 AND @Day = 4 THEN 0
--Labor Day
WHEN @Month = 9 AND @DayOfWeek = 1 AND @DayOccurrence = 1 THEN 0
--Columbus Day
WHEN @Month = 10 AND @DayOfWeek = 1 AND @DayOccurrence = 2 THEN 0
--Veterans Day
WHEN @Month = 11 AND @Day = 11 THEN 0
--Thanksgiving
WHEN @Month = 11 AND @DayOfWeek = 4 AND @DayOccurrence = 4 THEN 0
--Christmas
WHEN @Month = 12 AND @Day = 25 THEN 0
ELSE 1
END AS Result);
SET @TestDate = DATEADD(dd, 1, @TestDate);
END
RETURN @TotalBusinessDays;
END
참고 URL : https://stackoverflow.com/questions/252519/count-work-days-between-two-dates
반응형
'Programming' 카테고리의 다른 글
| Git을 사용하여 원격 저장소에 초기 푸시를 어떻게 수행합니까? (0) | 2020.06.12 |
|---|---|
| Ruby on Rails : 문자열을 HTML로 렌더링하는 방법? (0) | 2020.06.12 |
| C ++과 Java에서 "일반"유형의 차이점은 무엇입니까? (0) | 2020.06.12 |
| jquery check 버튼으로 모든 확인란을 선택 취소하십시오. (0) | 2020.06.12 |
| 다차원 배열의 너비와 높이를 어떻게 얻습니까? (0) | 2020.06.12 |