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으로 설정합니다. 당신이 찾고 있거나 찾지 못하는 것.