복붙노트

[SQL] 두 날짜 사이에 작업 일 카운트

SQL

두 날짜 사이에 작업 일 카운트

어떻게 SQL Server의 두 날짜 사이에 작업 일 수를 계산할 수있다?

월요일부터 금요일까지 그리고 T-SQL해야합니다.

해결법

  1. ==============================

    1.근무일를 들어, 월요일부터 금요일까지,이 같은 하나의 SELECT, 함께 할 수 있습니다 :

    근무일를 들어, 월요일부터 금요일까지,이 같은 하나의 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)

    당신은 휴일을 포함 할 경우, 당신은 조금 그것을 밖으로 일해야 ...

  2. ==============================

    2.직장 일을 계산에서는이 주제에 대한 좋은 기사를 찾을 수 있지만, 당신이 볼 수로는 고급 없습니다.

    직장 일을 계산에서는이 주제에 대한 좋은 기사를 찾을 수 있지만, 당신이 볼 수로는 고급 없습니다.

    --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

    사용자 정의 달력을 사용해야하는 경우, 당신은 몇 가지 검사와 몇 가지 매개 변수를 추가해야 할 수도 있습니다. 희망이 지점을 시작하는 좋은를 제공 할 것입니다.

  3. ==============================

    3.보그 맥심 & 피터 모텐슨에 대한 모든 신용. 이 난 그냥이 당신이 날짜 필드 "HolDate"이있는 테이블 "tblHolidays"가 가정 (함수에 휴일을 추가, 자신의 게시물입니다.

    보그 맥심 & 피터 모텐슨에 대한 모든 신용. 이 난 그냥이 당신이 날짜 필드 "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)
*/
  4. ==============================

    4.금요일 - 일을 계산하는 또 다른 방법은 날짜 범위를 통해 기본적으로 반복 while 루프를 사용하여 일 월요일 이내에 발견 될 때마다 1을 증가하는 것입니다. while 루프를 사용하여 일을 계산하기위한 전체 스크립트는 다음과 같습니다 :

    금요일 - 일을 계산하는 또 다른 방법은 날짜 범위를 통해 기본적으로 반복 while 루프를 사용하여 일 월요일 이내에 발견 될 때마다 1을 증가하는 것입니다. 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/

  5. ==============================

    5.내가있는 라인에 문자열 비교를 할 필요가 없습니다, DATEPART를 사용하여 함수로 허용 대답의 내 버전

    내가있는 라인에 문자열 비교를 할 필요가 없습니다, DATEPART를 사용하여 함수로 허용 대답의 내 버전

    DATENAME(dw, @StartDate) = 'Sunday'

    어쨌든, 여기 내 비즈니스 DATEDIFF 기능입니다

    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
  6. ==============================

    6.

     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
  7. ==============================

    7.(나는 몇 가지 포인트가 권한을 주석으로 부끄러워 해요)

    (나는 몇 가지 포인트가 권한을 주석으로 부끄러워 해요)

    당신은 CMS의 우아한 솔루션에 일일를 포기하기로 결정하는 경우 귀하의 시작 날짜와 종료 날짜가 같은 주말에있는 경우, 참고, 당신은 부정적인 응답을 얻을. 즉., 2008년 10월 26일 2008년 10월 26일에 -1을 반환한다.

    내 오히려 단순한 솔루션 :

    select @Result = (..CMS's answer..)
if  (@Result < 0)
        select @Result = 0
    RETURN @Result

    ... 이는 또한 제로에 종료 날짜 이후에 시작 날짜 모든 잘못된 게시물을 설정합니다. 당신이 수도 있고 찾고되지 않을 수 있습니다 뭔가.

  8. ==============================

    8.휴일을 포함 날짜 사이의 차이를 들어 나는이 길을 갔다 :

    휴일을 포함 날짜 사이의 차이를 들어 나는이 길을 갔다 :

    휴일 1) 테이블 :

        CREATE TABLE [dbo].[Holiday](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Date] [datetime] NOT NULL)

    2) 나는이 같은 내 plannings 테이블이 있고 비어 열 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)

    내 열에서 나중에 채우기에 "Work_Days"를 얻기 위하여 그래서 3)했다 단지 :

    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

    내가 도움이 될 수 있기를 바랍니다.

    건배

  9. ==============================

    9.여기에 (내 생각) 잘 작동하는 버전입니다. 휴일 테이블은 회사가 준수 휴일을 포함 HOLIDAY_DATE 열이 포함되어 있습니다.

    여기에 (내 생각) 잘 작동하는 버전입니다. 휴일 테이블은 회사가 준수 휴일을 포함 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
  10. ==============================

    10.나는이 오래된 질문은 알고 있지만 내가 몇 가지 항목이 제대로 축적하기 위해 일해야 할 때부터 시작 날짜를 제외한 근무일에 대한 수식을 필요로했다.

    나는이 오래된 질문은 알고 있지만 내가 몇 가지 항목이 제대로 축적하기 위해 일해야 할 때부터 시작 날짜를 제외한 근무일에 대한 수식을 필요로했다.

    비 반복적 인 답변을 아무도 나를 위해 일하지 않는다.

    나는 같은 정의를 사용

    (다른 사람이 대신 월요일의 토요일 자정 카운트 수)

    나는이 공식 결국

    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 */
  11. ==============================

    11.이것은 기본적으로 특정 언어 설정에 의존하지 않고 CMS의 대답이다. 그리고 일반적인 대한 우린 촬영하기 때문에, 그 수단은 모든 @@ DATEFIRST 설정에 대한뿐만 아니라 작동합니다.

    이것은 기본적으로 특정 언어 설정에 의존하지 않고 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 (주, ...) 항상 주 동안 토요일에 일요일 경계를 사용합니다 (금요일만큼 평일의 우리의 정의는 지속적으로 월요일한다.) 날 번호를 합니까가에 따라 달라질 @@ DATEFIRST 설정 및 수정 계산 일부 모듈러 산술 작은 합병증이 보정 처리.

    토요일 / 일요일 일을 처리하기위한 청소기 방법은 주 값의 일을 추출하기 전에 날짜를 번역하는 것입니다. 이동 후, 값에 맞춰 돌아올 것이다 고정 된 (그리고 아마도 더 익숙한) 토요일에 7이 일요일에 1로 시작하고 끝 번호.

    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 벤의 GaN 문서의이 양식을 추적했습니다. 이 날짜 계산을 허용하지 않는 새로운 타입 일 보낸 작은 조정할 필요했지만 (https://technet.microsoft.com/en-us/library/aa175781(v=sql.80).aspx), 그렇지 않으면 동일하다.

    편집하다: 어떻게 든 중단되었던 일을 다시 추가. 또한이 방법은 항상 시작과 끝 일 카운트 있음을 주목할 필요가있다. 또한 종료일이나 시작일 날짜 이후 있다고 가정합니다.

  12. ==============================

    12.날짜 테이블을 사용 :

    날짜 테이블을 사용 :

        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

    그들은 둘 다 빠른해야하며 모호성 / 복잡성을합니다. 첫 번째 옵션은 최고입니다하지만 당신은 달력 테이블이없는 경우에 당신은에 allways는 CTE와 숫자 테이블을 만들 수 있습니다.

  13. ==============================

    13.

    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
  14. ==============================

    14.

    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
  15. ==============================

    15.나는 여기에 다양한 예제를했다,하지만 내 특정 상황에서 우리는 전달을위한 @PromisedDate 및 항목의 실제 수령에 대한 @ReceivedDate 있습니다. 항목은 I가 일정 순서로 함수에 전달 날짜를 주문하지 않는 한 "PromisedDate"계산이 제대로 총되지 않은 이전에 접수 된 때. 때마다 날짜를 확인하고자하지, 나는 나를 위해이 문제를 처리 할 수있는 기능을 변경했습니다.

    나는 여기에 다양한 예제를했다,하지만 내 특정 상황에서 우리는 전달을위한 @PromisedDate 및 항목의 실제 수령에 대한 @ReceivedDate 있습니다. 항목은 I가 일정 순서로 함수에 전달 날짜를 주문하지 않는 한 "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
  16. ==============================

    16.당신은 주어진 날짜에 작업 일을 추가해야하는 경우, 당신은 아래에 설명 달력 테이블에 의존하는 함수를 만들 수 있습니다 :

    당신은 주어진 날짜에 작업 일을 추가해야하는 경우, 당신은 아래에 설명 달력 테이블에 의존하는 함수를 만들 수 있습니다 :

    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
  17. ==============================

    17.토요일과 일요일에 우리 나라에서 나를 위해 일하고 그 이외의 일이다.

    토요일과 일요일에 우리 나라에서 나를 위해 일하고 그 이외의 일이다.

    나를 위해 @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
  18. ==============================

    18.기능을 같이 만듭니다

    기능을 같이 만듭니다

    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
  19. ==============================

    19.

    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

    종료

  20. ==============================

    20.나는 TSQL 아래에 상당히 우아한 해결책 (나는 기능을 실행할 수있는 권한이없는)을 발견했다. 나는 DATEDIFF는 DATEFIRST 내가 월요일로 일주일의 첫날을 원 무시 발견했다. 또한 첫 번째 작업 일 0을 설정해야하고 주말에 떨어지는 경우 월요일이 0이됩니다 싶었다. 이것은 약간 다른 요구 사항이있는 사람을 도움이 될 수 있습니다 :

    나는 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*/
  21. ==============================

    21.한 가지 방법은 날이 토요일이나 일요일이 아닌 경우 체크 경우 표현과 함께하고 (주말 1 평일, 0)을 신고에서 처음부터 끝까지 '날짜를 걸어'하는 것입니다. 당신에게 평일의 번호를 알려 그냥 플래그를 요약하면 결국 (다른 플래그가 0으로 1 - 플래그의 수와 동일 할 것이다).

    한 가지 방법은 날이 토요일이나 일요일이 아닌 경우 체크 경우 표현과 함께하고 (주말 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)
  22. ==============================

    22.DATEDIFF과 마찬가지로, 나는 간격의 일부로 종료 날짜를 고려하지 않습니다. @StartDate 및 @EndDate 사이 (예) 일의 수는 "초기"월요일과 마이너스 @EndDate이 "초기"월요일과 @StartDate 사이 일 수 사이 일 수있다. 다음과 같이 알고, 우리는 근무일 수를 계산할 수 있습니다 :

    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

    친애하는!

  23. ==============================

    23.내 솔루션을 만들기 위해 다른 사람으로부터 아이디어를 빌렸다. 나는 주말과 미국 연방 공휴일을 무시하는 인라인 코드를 사용합니다. 내 환경에서, 종료 날짜는 null도 가능하지만, startDate를 선행하지 않습니다.

    내 솔루션을 만들기 위해 다른 사람으로부터 아이디어를 빌렸다. 나는 주말과 미국 연방 공휴일을 무시하는 인라인 코드를 사용합니다. 내 환경에서, 종료 날짜는 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

    24. from https://stackoverflow.com/questions/252519/count-work-days-between-two-dates by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 (또는 내가 수 있습니다) 여러 열에서 DISTINCT 선택합니까?  (0) 2020.03.09
[SQL] 이를 이미 없으면 만 행을 삽입  (0) 2020.03.09
[SQL] 어떻게 PostgreSQL의에서 many-to-many 관계를 구현하는 방법?  (0) 2020.03.09
[SQL] 여러 열의 SQL MAX?  (0) 2020.03.09
[SQL] 한 성명에서 자바 실행 여러 개의 쿼리  (0) 2020.03.09
,

티스토리툴바