반응형
두 날짜 사이의 근무 일수 수
SQL Server에서 두 날짜 사이의 근무 일수를 계산하려면 어떻게 해야 합니까?
월요일부터 금요일까지 T-SQL이어야 합니다.
월요일부터 금요일까지 근무일의 경우 다음과 같이 단일 선택으로 수행할 수 있습니다.
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
사용자 지정 달력을 사용해야 하는 경우 몇 가지 검사 및 매개 변수를 추가해야 할 수도 있습니다.좋은 출발점이 되기를 바랍니다.
보그단 막심 & 피터 모텐슨에게 모든 공을 돌립니다.이것은 그들의 게시물이며, 나는 방금 함수에 휴일을 추가했습니다(이것은 당신이 datetime 필드 "HolDate"가 있는 테이블 "tbl Holidays"가 있다고 가정합니다).
--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)
*/
을 .DATEPART 가 없습니다.
DATENAME(dw, @StartDate) = 'Sunday'
어쨌든, 여기 제 영업 날짜 diff 기능이 있습니다.
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
근무 일수를 계산하는 또 다른 방법은 기본적으로 날짜 범위를 반복하는 WHIE 루프를 사용하고 월요일에서 금요일 사이의 일이 발견될 때마다 1씩 증가시키는 것입니다.WHITE 루프를 사용하여 작업일을 계산하는 전체 스크립트는 다음과 같습니다.
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
WHITE 루프 옵션은 더 깨끗하고 코드 라인을 적게 사용하지만, 특히 날짜 범위가 여러 해에 걸쳐 있는 경우 환경에서 성능 병목 현상이 발생할 가능성이 있습니다.
이 기사에서는 근무일 및 시간을 계산하는 방법에 대한 자세한 내용을 확인할 수 있습니다. https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/
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
오래된 질문인 것은 알지만, 저는 여러 항목이 있고 정확하게 쌓이기 위해서는 날짜가 필요하기 때문에 시작일을 제외한 근무일에 대한 공식이 필요했습니다.
반복하지 않는 대답은 하나도 제게 통하지 않았습니다.
저는 이런 정의를 사용했습니다.
자정부터 월요일, 화요일, 수요일, 목요일, 금요일까지의 경과 횟수
(others은 월요일 대신 자정부터 토요일까지 계산할 수 있음)
저는 결국 이 공식을 가지게 되었습니다.
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 */
이것은 기본적으로 특정 언어 설정에 의존하지 않고 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, ...)는 항상 몇 주 동안 토요일과 일요일의 경계를 사용하므로 식을 결정적으로 사용하고 수정할 필요가 없습니다(평일에 대한 정의가 월요일부터 금요일까지 일관성 있게 유지되는 한). 번호는.@@datefirstsetting과 수정된 계산은 일부 모듈러 산술의 작은 복잡성으로 이 수정을 처리합니다.
토요일/일요일 일을 처리하는 더 깨끗한 방법은 요일 값을 추출하기 전에 날짜를 번역하는 것입니다.시프트 후 값은 일요일에 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) 새로워진 이후 약간의 수정이 필요했지만datetypes는 날짜 산술을 허용하지 않으며, 그렇지 않으면 동일합니다.
했습니다.+1어떻게 해서든 배제되어 있었던 것입니다 이 은 항상 한다는 점도 이 방법은 항상 시작일과 종료일을 계산한다는 점도 유의할 필요가 있습니다.또한 종료 날짜가 시작 날짜 이후인 것으로 가정합니다.
위의 어떤 기능도 같은 주 동안 작동하거나 휴일을 처리하지 않습니다.제가 쓴 글입니다.
create FUNCTION [dbo].[ShiftHolidayToWorkday](@date date)
RETURNS date
AS
BEGIN
IF DATENAME( dw, @Date ) = 'Saturday'
SET @Date = DATEADD(day, - 1, @Date)
ELSE IF DATENAME( dw, @Date ) = 'Sunday'
SET @Date = DATEADD(day, 1, @Date)
RETURN @date
END
GO
create FUNCTION [dbo].[GetHoliday](@date date)
RETURNS varchar(50)
AS
BEGIN
declare @s varchar(50)
SELECT @s = CASE
WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year] ) + '-01-01') = @date THEN 'New Year'
WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]+1) + '-01-01') = @date THEN 'New Year'
WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year] ) + '-07-04') = @date THEN 'Independence Day'
WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year] ) + '-12-25') = @date THEN 'Christmas Day'
--WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-12-31') = @date THEN 'New Years Eve'
--WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-11-11') = @date THEN 'Veteran''s Day'
WHEN [Month] = 1 AND [DayOfMonth] BETWEEN 15 AND 21 AND [DayName] = 'Monday' THEN 'Martin Luther King Day'
WHEN [Month] = 5 AND [DayOfMonth] >= 25 AND [DayName] = 'Monday' THEN 'Memorial Day'
WHEN [Month] = 9 AND [DayOfMonth] <= 7 AND [DayName] = 'Monday' THEN 'Labor Day'
WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'
WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 23 AND 29 AND [DayName] = 'Friday' THEN 'Day After Thanksgiving'
ELSE NULL END
FROM (
SELECT
[Year] = YEAR(@date),
[Month] = MONTH(@date),
[DayOfMonth] = DAY(@date),
[DayName] = DATENAME(weekday,@date)
) c
RETURN @s
END
GO
create FUNCTION [dbo].GetHolidays(@year int)
RETURNS TABLE
AS
RETURN (
select dt, dbo.GetHoliday(dt) as Holiday
from (
select dateadd(day, number, convert(varchar,@year) + '-01-01') dt
from master..spt_values
where type='p'
) d
where year(dt) = @year and dbo.GetHoliday(dt) is not null
)
create proc UpdateHolidaysTable
as
if not exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Holidays')
create table Holidays(dt date primary key clustered, Holiday varchar(50))
declare @year int
set @year = 1990
while @year < year(GetDate()) + 20
begin
insert into Holidays(dt, Holiday)
select a.dt, a.Holiday
from dbo.GetHolidays(@year) a
left join Holidays b on b.dt = a.dt
where b.dt is null
set @year = @year + 1
end
create FUNCTION [dbo].[GetWorkDays](@StartDate DATE = NULL, @EndDate DATE = NULL)
RETURNS INT
AS
BEGIN
IF @StartDate IS NULL OR @EndDate IS NULL
RETURN 0
IF @StartDate >= @EndDate
RETURN 0
DECLARE @Days int
SET @Days = 0
IF year(@StartDate) * 100 + datepart(week, @StartDate) = year(@EndDate) * 100 + datepart(week, @EndDate)
--same week
select @Days = (DATEDIFF(dd, @StartDate, @EndDate))
- (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
- (select count(*) from Holidays where dt between @StartDate and @EndDate)
ELSE
--diff weeks
select @Days = (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)
- (select count(*) from Holidays where dt between @StartDate and @EndDate)
RETURN @Days
END
날짜 테이블 사용:
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로 숫자 테이블을 만들 수 있습니다.
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
저는 여기서 다양한 예를 들었지만, 제 특정 상황에서는 @PromiseDate for delivery(납품 약속 날짜)와 @ReceivedDate(물건의 실제 수령 날짜)가 있습니다."약속된 날짜" 이전에 항목을 받았을 때 일정 순서로 함수에 전달된 날짜를 주문하지 않는 한 계산이 정확하게 집계되지 않았습니다.매번 날짜를 확인하고 싶지 않아서, 대신 처리할 수 있도록 기능을 바꿨습니다.
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
DATEIFF와 마찬가지로 종료일은 인터벌의 일부라고 생각하지 않습니다.@StartDate와 @EndDate 사이의 (예를 들어) 일요일 수는 "initial" 월요일과 @EndDate 사이의 일요일 수에서 이 "initial" 월요일과 @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
저는 그게 효과가 있어요, 우리나라는 토요일과 일요일이 비근로일이에요.
저는 @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이 상당히 우아한 솔루션임을 알게 되었습니다(기능을 실행할 수 있는 권한이 없습니다)..DATEDIFFDATEFIRST일주일 중 첫 날이 월요일이었으면 좋겠다고 생각했습니다.저는 또한 첫 근무일을 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).그리고 결국 평일의 수를 알려주기 위해 깃발을 합하기만 하면 됩니다(다른 깃발은 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)
언급URL : https://stackoverflow.com/questions/252519/count-work-days-between-two-dates
반응형
'programing' 카테고리의 다른 글
| UNIQUE 제약 조건(필드에 특정 값이 포함된 경우에만 해당) (0) | 2023.09.28 |
|---|---|
| 워드프레스 + Ajax 페이지 로딩 + Gravity 폼 + Gravity 폼 페이지 브레이크 (0) | 2023.09.28 |
| 람다 함수와 중첩 함수('def') 중 어느 것을 사용하는 것이 더 좋습니까? (0) | 2023.09.28 |
| R의 MySQL에서 UTF-8 텍스트를 가져오면 "???"가 반환됩니다. (0) | 2023.09.28 |
| 404를 유사한 URL로 리디렉션 (0) | 2023.09.28 |