복붙노트

[SQL] 두 날짜 사이의 계산 업무 시간

SQL

두 날짜 사이의 계산 업무 시간

어떻게 두 날짜 사이의 업무 시간을 계산할 수있다? 예를 들어 우리는 두 날짜가; 01/01/2010 15:00 2010년 4월 1일 12시 그리고 우리는 평일에 00 ~ 17 시간 9 시부 터 작업 한 어떻게 SQL 작업 시간을 산출 할 수있다?

해결법

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

    1.바란의 대답은 고정 및 SQL 2005에 대한 수정

    바란의 대답은 고정 및 SQL 2005에 대한 수정

    SQL 2008 이상 :

    -- =============================================
-- Author:      Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime] 
(
    @StartDate DATETIME,
    @FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
    DECLARE @Temp BIGINT
    SET @Temp=0

    DECLARE @FirstDay DATE
    SET @FirstDay = CONVERT(DATE, @StartDate, 112)

    DECLARE @LastDay DATE
    SET @LastDay = CONVERT(DATE, @FinishDate, 112)

    DECLARE @StartTime TIME
    SET @StartTime = CONVERT(TIME, @StartDate)

    DECLARE @FinishTime TIME
    SET @FinishTime = CONVERT(TIME, @FinishDate)

    DECLARE @WorkStart TIME
    SET @WorkStart = '09:00'

    DECLARE @WorkFinish TIME
    SET @WorkFinish = '17:00'

    DECLARE @DailyWorkTime BIGINT
    SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)

    IF (@StartTime<@WorkStart)
    BEGIN
        SET @StartTime = @WorkStart
    END
    IF (@FinishTime>@WorkFinish)
    BEGIN
        SET @FinishTime=@WorkFinish
    END
    IF (@FinishTime<@WorkStart)
    BEGIN
        SET @FinishTime=@WorkStart
    END
    IF (@StartTime>@WorkFinish)
    BEGIN
        SET @StartTime = @WorkFinish
    END

    DECLARE @CurrentDate DATE
    SET @CurrentDate = @FirstDay
    DECLARE @LastDate DATE
    SET @LastDate = @LastDay

    WHILE(@CurrentDate<=@LastDate)
    BEGIN       
        IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
        BEGIN
            IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + @DailyWorkTime
            END
            --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
            END

            ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
            END
            --IF it starts and finishes in the same date
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
            END
        END
        SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
    END

    -- Return the result of the function
    IF @Temp<0
    BEGIN
        SET @Temp=0
    END
    RETURN @Temp

END

    SQL 2005 이하 :

    -- =============================================
-- Author:      Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime] 
(
    @StartDate DATETIME,
    @FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
    DECLARE @Temp BIGINT
    SET @Temp=0

    DECLARE @FirstDay DATETIME
    SET @FirstDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate))

    DECLARE @LastDay DATETIME
    SET @LastDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @FinishDate))

    DECLARE @StartTime DATETIME
    SET @StartTime = @StartDate - DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)

    DECLARE @FinishTime DATETIME
    SET @FinishTime = @FinishDate - DATEADD(dd, DATEDIFF(dd, 0, @FinishDate), 0)

    DECLARE @WorkStart DATETIME
    SET @WorkStart = CONVERT(DATETIME, '09:00', 8)

    DECLARE @WorkFinish DATETIME
    SET @WorkFinish = CONVERT(DATETIME, '17:00', 8)

    DECLARE @DailyWorkTime BIGINT
    SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)

    IF (@StartTime<@WorkStart)
    BEGIN
        SET @StartTime = @WorkStart
    END
    IF (@FinishTime>@WorkFinish)
    BEGIN
        SET @FinishTime=@WorkFinish
    END
    IF (@FinishTime<@WorkStart)
    BEGIN
        SET @FinishTime=@WorkStart
    END
    IF (@StartTime>@WorkFinish)
    BEGIN
        SET @StartTime = @WorkFinish
    END

    DECLARE @CurrentDate DATETIME
    SET @CurrentDate = @FirstDay
    DECLARE @LastDate DATETIME
    SET @LastDate = @LastDay

    WHILE(@CurrentDate<=@LastDate)
    BEGIN       
        IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
        BEGIN
            IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + @DailyWorkTime
            END
            --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
            END

            ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
            END
            --IF it starts and finishes in the same date
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
            END
        END
        SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
    END

    -- Return the result of the function
    IF @Temp<0
    BEGIN
        SET @Temp=0
    END
    RETURN @Temp

END
  2. ==============================

    2.나는이 게시물은 매우 오래지만 여기에 내가 두 사건 사이에 / 분 영업 시간을 계산하기 위해 최근에 쓴 기능입니다 알고있다. 테이블에 정의해야하는 공휴일 계정에 또한합니다.

    나는이 게시물은 매우 오래지만 여기에 내가 두 사건 사이에 / 분 영업 시간을 계산하기 위해 최근에 쓴 기능입니다 알고있다. 테이블에 정의해야하는 공휴일 계정에 또한합니다.

    이 기능은 간격 (분)을 반환 - 필요에 따라이 시간을 얻을 수 (60)에 의해 나눌 수 있습니다.

    이것은 누군가를하는 데 도움이 SQL Server 2008의 희망에서 테스트되었습니다.

    Create Function GetWorkingMin(@StartDate DateTime, @EndDate DateTime, @Country Varchar(2)) Returns Int
AS
Begin
    Declare @WorkMin int = 0   -- Initialize counter
    Declare @Reverse bit       -- Flag to hold if direction is reverse
    Declare @StartHour int = 9   -- Start of business hours (can be supplied as an argument if needed)
    Declare @EndHour int = 17    -- End of business hours (can be supplied as an argument if needed)
    Declare @Holidays Table (HDate DateTime)   --  Table variable to hold holidayes

    -- If dates are in reverse order, switch them and set flag
    If @StartDate>@EndDate 
    Begin
        Declare @TempDate DateTime=@StartDate
        Set @StartDate=@EndDate
        Set @EndDate=@TempDate
        Set @Reverse=1
    End
    Else Set @Reverse = 0

    -- Get country holidays from table based on the country code (Feel free to remove this or modify as per your DB schema)
    Insert Into @Holidays (HDate) Select HDate from HOLIDAY Where COUNTRYCODE=@Country and HDATE>=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)

    If DatePart(HH, @StartDate)<@StartHour Set @StartDate = DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate))  -- If Start time is less than start hour, set it to start hour
    If DatePart(HH, @StartDate)>=@EndHour+1 Set @StartDate = DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) -- If Start time is after end hour, set it to start hour of next day
    If DatePart(HH, @EndDate)>=@EndHour+1 Set @EndDate = DateAdd(hour, @EndHour, DateDiff(DAY, 0, @EndDate)) -- If End time is after end hour, set it to end hour
    If DatePart(HH, @EndDate)<@StartHour Set @EndDate = DateAdd(hour, @EndHour-24, DateDiff(DAY, 0, @EndDate)) -- If End time is before start hour, set it to end hour of previous day

    If @StartDate>@EndDate Return 0

    -- If Start and End is on same day
    If DateDiff(Day,@StartDate,@EndDate) <= 0
    Begin
        If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7  -- If day is between sunday and saturday
            If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0  -- If day is not a holiday
                If @EndDate<@StartDate Return 0 Else Set @WorkMin=DATEDIFF(MI, @StartDate, @EndDate) -- Calculate difference
            Else Return 0
        Else Return 0
    End
    Else Begin
        Declare @Partial int=1   -- Set partial day flag
        While DateDiff(Day,@StartDate,@EndDate) > 0   -- While start and end days are different
        Begin
            If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7    --  If this is a weekday
            Begin
                If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0  -- If this is not a holiday
                Begin
                    If @Partial=1  -- If this is the first iteration, calculate partial time
                    Begin 
                        Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, DateAdd(hour, @EndHour, DateDiff(DAY, 0, @StartDate)))
                        Set @StartDate=DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) 
                        Set @Partial=0 
                    End
                    Else Begin      -- If this is a full day, add full minutes
                        Set @WorkMin=@WorkMin + (@EndHour-@StartHour)*60        
                        Set @StartDate = DATEADD(DD,1,@StartDate)
                    End
                End
                Else Set @StartDate = DATEADD(DD,1,@StartDate)  
            End
            Else Set @StartDate = DATEADD(DD,1,@StartDate)
        End
        If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7  -- If last day is a weekday
            If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0   -- And it is not a holiday
                If @Partial=0 Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, @EndDate) Else Set @WorkMin=@WorkMin + DATEDIFF(MI, DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)), @EndDate)
    End 
    If @Reverse=1 Set @WorkMin=-@WorkMin
    Return @WorkMin
End
  3. ==============================

    3.더 많은 데이터 기반의 각도에서 사물을 오는 Pavanred의 @에서 대안 솔루션 :

    더 많은 데이터 기반의 각도에서 사물을 오는 Pavanred의 @에서 대안 솔루션 :

    당신이 고려해야 할 모든 날짜와 테이블을 만듭니다. 매일 들어,과 같이, 근무 시간의 수를 설정 :

    WorkingDate Hours Comment
=========== ===== ==================
 1 Jan 2011     0 Saturday
 2 Jan 2011     0 Sunday
 3 Jan 2011     0 Public Holiday
 4 Jan 2011     8 Normal working day
 5 Jan 2011     8 Normal working day

 -- and so on, for all the days you want to report on.

    이 설정의 작은 금액을 취할 것입니다 - 당신은 자동으로 주말 대비 주를 미리 채울 필요에 따라 공휴일 등을 위해 조정할 수 있습니다.

    그러나, 당신이 잃을 무슨 설정, 당신은 쿼리의 용이성 얻을 :

    SELECT
  SUM(Hours) 
FROM
  working_days 
WHERE
  WorkingDate BETWEEN @StartDate AND @EndDate

    당신의 근무 시간이 다를 경우 ... 그리고 당신이 근무 시간 정의 무엇을 더 복잡하게 규칙을 추가하기 시작해야하는 경우이 쉽게 접근 방식으로 해결할 수, 또는 요일에 따라 등

    당신은 작업 일의 정의를 변경하는 실제 코드를 변경 등 공휴일을 추가 할 필요가 없기 때문에 그것은 또한, "편집"더 쉽게 규칙을 만든다

  4. ==============================

    4.첫 번째 단계는 아래의 스크립트에서 같이 일을 계산하는 것입니다 :

    첫 번째 단계는 아래의 스크립트에서 같이 일을 계산하는 것입니다 :

    DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2), @DateFrom DATETIME, @DateTo DATETIME;
SET @DateFrom = '2017-06-05 11:19:11.287';
SET @DateTo = '2017-06-07 09:53:14.750';

SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)
    -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2)
   -CASE
                                    WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday'
                                    THEN 1
                                    ELSE 0
                                END+CASE
                                        WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday'
                                        THEN 1
                                        ELSE 0
                                    END;

    두 번째 단계는 두 날짜 사이의 초에 변화를 가져오고,이 다음 스크립트에 도시 된 바와 같이 3600.0로 나눈 시간에 해당 차분 변환 수반 :

    SET @TotalTimeDiff =
(
    SELECT DATEDIFF(SECOND,
                   (
                       SELECT CONVERT(TIME, @DateFrom)
                   ),
                   (
                       SELECT CONVERT(TIME, @DateTo)
                   )) / 3600.0
);

    마지막 부분을 첨가 한 후 24 일 후 (하루 시간의 총계)에 의해 먼저 상기 출력을 곱한 것을 포함하는 두 번째 단계의 출력 :

    SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff;

    마지막으로, 작업 시간을 산출하는 함수를 정의하여 사용자를 만드는 데 사용할 수있는 완전한 스크립트는 아래와 같다 :

    CREATE FUNCTION [dbo].[fn_GetTotalWorkingHours]
(
    @DateFrom Datetime,
    @DateTo Datetime
)
RETURNS DECIMAL(18,2)
AS
BEGIN

DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2)

SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)
    -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2)
   -CASE
                                    WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday'
                                    THEN 1
                                    ELSE 0
                                END+CASE
                                        WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday'
                                        THEN 1
                                        ELSE 0
                                    END;
SET @TotalTimeDiff =
(
    SELECT DATEDIFF(SECOND,
                   (
                       SELECT CONVERT(TIME, @DateFrom)
                   ),
                   (
                       SELECT CONVERT(TIME, @DateTo)
                   )) / 3600.0
);

RETURN  (SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff)

END
GO

    완전한 방법은이 문서에서 VII로부터됩니다 : https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/

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

    5.

    DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @WORKINGHOURS INT
DECLARE @Days INT
SET @StartDate = '2010/01/01'
SET @EndDate = '2010/04/01'

--number of working days
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)

--8 hours a day    
SET @WORKINGHOURS = @Days * 8 

SELECT @WORKINGHOURS
  6. ==============================

    6.

        -- =============================================
-- Author:      Baran Kaynak
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime] 
(
    @StartDate DATETIME,
    @FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
    DECLARE @Temp BIGINT
    SET @Temp=0

    DECLARE @FirstDay DATE
    SET @FirstDay = CONVERT(DATE, @StartDate, 112)

    DECLARE @LastDay DATE
    SET @LastDay = CONVERT(DATE, @FinishDate, 112)

    DECLARE @StartTime TIME
    SET @StartTime = CONVERT(TIME, @StartDate)

    DECLARE @FinishTime TIME
    SET @FinishTime = CONVERT(TIME, @FinishDate)

    DECLARE @WorkStart TIME
    SET @WorkStart = '09:30'

    DECLARE @WorkFinish TIME
    SET @WorkFinish = '17:30'

    IF (@StartTime<@WorkStart)
    BEGIN
        SET @StartTime = @WorkStart
    END
    IF (@FinishTime>@WorkFinish)
    BEGIN
        SET @FinishTime=@WorkFinish
    END

    DECLARE @CurrentDate DATE
    SET @CurrentDate = CONVERT(DATE, @StartDate, 112)
    DECLARE @LastDate DATE
    SET @LastDate = CONVERT(DATE, @FinishDate, 112)

    WHILE(@CurrentDate<=@LastDate)
    BEGIN       
        IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
        BEGIN
            IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = (@Temp + (9*60))
            END
            --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
            END

            ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
            END
            --IF it starts and finishes in the same date
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = DATEDIFF(MINUTE, @StartDate, @FinishDate)
            END
        END
        SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
    END

    -- Return the result of the function
    IF @Temp<0
    BEGIN
        SET @Temp=0
    END
    RETURN @Temp

END

GO
  7. ==============================

    7.

    ALTER FUNCTION WorkTime_fn (@StartDate DATETIME, @FinishDate DATETIME)
RETURNS VARCHAR(9)
AS
BEGIN
    DECLARE @Temp BIGINT
    SET @Temp=0

    DECLARE @FirstDay VARCHAR(9)
    SET @FirstDay = CONVERT(VARCHAR(9),@StartDate, 112)

    DECLARE @LastDay VARCHAR(9)
    SET @LastDay = CONVERT(VARCHAR(9),@FinishDate, 112)

    DECLARE @StartTime VARCHAR(9)
    SET @StartTime = CONVERT(VARCHAR(9),@StartDate, 108)

    DECLARE @FinishTime VARCHAR(9)
    SET @FinishTime = CONVERT(VARCHAR(9),@FinishDate, 108)

    DECLARE @WorkStart VARCHAR(9)
    SET @WorkStart = '09:30:00'

    DECLARE @WorkFinish VARCHAR(9)
    SET @WorkFinish = '17:30:00'

    IF (@StartTime<@WorkStart)
    BEGIN
        SET @StartTime = @WorkStart
    END
    IF (@FinishTime>@WorkFinish)
    BEGIN
        SET @FinishTime=@WorkFinish
    END

DECLARE @CurrentDate VARCHAR(9)
    SET @CurrentDate = CONVERT(VARCHAR(9),@StartDate, 112)
    DECLARE @LastDate VARCHAR(9)
    SET @LastDate = CONVERT(VARCHAR(9),@FinishDate, 112)

WHILE(@CurrentDate<=@LastDate)
BEGIN       

        IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
        BEGIN
              IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
              BEGIN
                   SET @Temp = (@Temp + (8*60))

              END

              ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
              BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)

              END

              ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
              BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)

              END

              ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
              BEGIN
                SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)

              END

             END

SET @CurrentDate = CONVERT(VARCHAR(9),DATEADD(day, 1, @CurrentDate),112)

END
        Return @TEMP

END
  8. ==============================

    8.여기에 인라인 버전입니다 시작 / EndDateTime 09 2015년 3월 16일 같은 : 52 : 24.000 시작 / 종료 시각 (businesshours) 7시 0분 0초 같은 그것은 부피가 있지만 select 문에서 작동

    여기에 인라인 버전입니다 시작 / EndDateTime 09 2015년 3월 16일 같은 : 52 : 24.000 시작 / 종료 시각 (businesshours) 7시 0분 0초 같은 그것은 부피가 있지만 select 문에서 작동

    나뿐만 아니라 기능 버전에 게시 할 예정입니다.

    Case when  <StartDate>= <EndDate> then 0
    When Convert(date,<StartDate>) = Convert(date,<EndDate>) Then 
         IIF( DATEPART(Dw,<StartDate>)  in(1,7)
                  or Convert(time,<StartDate>) > Convert(time,<EndTime>)
                  or Convert(time,<EndDate>) < Convert(time,<StartTime>),0, 
        DateDiff(S,IIF(Convert(time,<StartDate>) < Convert(time,<StartTime>),Convert(time,<StartTime>),Convert(time,<StartDate>))
                ,IIF(Convert(time,<EndDate>) > Convert(time,<EndTime>), Convert(time,<EndTime>), Convert(time,<EndDate>))))
    when  Convert(date,<StartDate>) <> Convert(date,<EndDate>) then 
        IIF(DATEPART(Dw,<StartDate>) in(1,7) or Convert(time,<StartDate>) >  Convert(time,<EndTime>),0 ,DateDiff(S,IIF(Convert(time,<StartDate>) < Convert(time,<StartTime>),Convert(time,<StartTime>),Convert(time,<StartDate>)), Convert(time,<EndTime>)))
        + IIF(DATEPART(Dw,<EndDate>) in(1,7) or  Convert(time,<EndDate>) <  Convert(time,<StartTime>),0,DateDiff(S,Convert(time,<StartTime>),IIF(Convert(time,<EndDate>) > Convert(time,<EndTime>), Convert(time,<EndTime>), Convert(time,<EndDate>))))
    else -333
    end --as pday

+IIF(DatePart(wEEk,<StartDate>)  = DatePart(wEEk,<EndDate>) 
,0, (DateDiff(wk,dateadd(d,-datepart(dw,<StartDate>),dateadd(ww,1,<StartDate>)),DATEADD(wk, DATEDIFF(wk, 6, <EndDate>), 6)-1) * 5)) * Datediff(S, Convert(time,<StartTime>),Convert(time,<EndTime>)) --Fullweek_days

+Case When Convert(date,<StartDate>) = Convert(date,<EndDate>) then 0
      When DatePart(wEEk,<StartDate>)  <> DatePart(wEEk,<EndDate>) then
                        IIF( datepart(dw,<StartDate>) = 7,0,DateDIFF(DAY,<StartDate>+1,dateadd(d,-datepart(dw,<StartDate>),dateadd(ww,1,<StartDate>))))  -- beginFulldays
                        +IIF( datepart(dw,<EndDate>) = 1,0,DateDIFF(DAY,DATEADD(wk, DATEDIFF(wk, 6, <EndDate>), 6),<EndDate> -1))  --Endfulldays
      When DatePart(wEEk,<StartDate>)  = DatePart(wEEk,<EndDate>) then
            DateDiff(DAY,<StartDate>+1,<EndDate> ) 
    ELSE -333 END * Datediff(S, Convert(time,<StartTime>),Convert(time,<EndTime>))

    여기에 기능 버전은 다음과 같습니다

    CREATE FUNCTION [dbo].[rsf_BusinessTime]
(
@startDateTime Datetime,
@endDateTime Datetime ,
@StartTime VarChar(12),
@EndTime VarChar(12) )
RETURNS BIGINT
As
BEGIN
Declare @totalSeconds BigInt,
    @SecondsInDay int,
    @dayStart Time = Convert(time,@StartTime),
    @dayEnd Time =Convert(time,@EndTime),
    @SatAfterStart Datetime = dateadd(d,-datepart(dw,@startDateTime),dateadd(ww,1,@startDateTime)), 
    @Sunbeforend Datetime = DATEADD(wk, DATEDIFF(wk, 6, @endDateTime), 6) 

-- This function calculates the seconds between the start and end dates provided for business hours. 
-- It only returns the time between the @start and @end time (hour of day) of the work week. 
-- Weekend days are removed.
-- Holidays are not considered.  

Set @SecondsInDay = Datediff(S, @dayStart,@dayEnd) 


Set @totalSeconds = 
 --first/last/sameday
    Case when  @startDateTime= @endDateTime then 0
    When Convert(date,@startDateTime) = Convert(date,@endDateTime) Then 
         IIF( DATEPART(Dw,@startDateTime)  in(1,7)
                  or Convert(time,@startDateTime) > @dayEnd
                  or Convert(time,@endDateTime) < @dayStart,0, 
        DateDiff(S,IIF(Convert(time,@startDateTime) < @dayStart,@dayStart,Convert(time,@startDateTime))
                ,IIF(Convert(time,@endDateTime) > @dayEnd, @dayEnd, Convert(time,@endDateTime))))
    when  Convert(date,@startDateTime) <> Convert(date,@endDateTime) then 
        IIF(DATEPART(Dw,@startDateTime) in(1,7) or Convert(time,@startDateTime) >  @dayEnd,0 ,DateDiff(S,IIF(Convert(time,@startDateTime) < @dayStart,@dayStart,Convert(time,@startDateTime)), @dayEnd))
        + IIF(DATEPART(Dw,@endDateTime) in(1,7) or  Convert(time,@endDateTime) <  @dayStart,0,DateDiff(S,@dayStart,IIF(Convert(time,@endDateTime) > @dayEnd, @dayEnd, Convert(time,@endDateTime))))
    else -333
    end --as pday

+IIF(DatePart(wEEk,@startDateTime)  = DatePart(wEEk,@endDateTime)   
,0, (DateDiff(wk,@SatAfterStart,@Sunbeforend-1) * 5)) * @SecondsInDay --Fullweek_days

+Case When Convert(date,@startDateTime) = Convert(date,@endDateTime) then 0
      When DatePart(wEEk,@startDateTime)  <> DatePart(wEEk,@endDateTime) then
                        IIF( datepart(dw,@startDateTime) = 7,0,DateDIFF(DAY,@startDateTime+1,@SatAfterStart))  -- beginFulldays
                        +IIF( datepart(dw,@endDateTime) = 1,0,DateDIFF(DAY,@Sunbeforend,@endDateTime -1))  --Endfulldays
      When DatePart(wEEk,@startDateTime)  = DatePart(wEEk,@endDateTime) then
            DateDiff(DAY,@startDateTime+1,@endDateTime ) 
    ELSE -333 END * @SecondsInDay


Return @totalSeconds
END
  9. ==============================

    9.계산 업무 시간 계정에 주말을 복용하지만, 가능한 공휴일을 무시 - 질문이 대답은 그냥 않도록 공휴일은 고려되지 않아야 함을 말한다.

    계산 업무 시간 계정에 주말을 복용하지만, 가능한 공휴일을 무시 - 질문이 대답은 그냥 않도록 공휴일은 고려되지 않아야 함을 말한다.

    또한 주어진 시작 및 종료 날짜 / 시간은 업무 시간에 있다고 가정합니다.

    영업일의 시작 또는 끝, 그것은 단지 하루 업무 시간의 총 수에 대한 관심 때이 가정으로 코드는 시간에 대해 상관하지 않는다. 당신의 예에서, 09 : 00 ~ 17 : 00 (8) 업무 시간이있다. 그것은 정수가 될 필요가 없습니다. 하나 개 정밀도이를 계산하기 화학식 있지만 그것을 1 초 기타 정밀하게 간단하다.

    당신이 계정에 공휴일을해야하는 경우가 매년와 국가에서 국가 주 또는 국가에 따라 다를 수 있습니다 공휴일을위한 날짜를 나열합니다 별도의 테이블이 필요합니다 것입니다. 주요 공식은 동일하게 유지 할 수 있습니다,하지만 당신은 날짜의 지정된 범위 내에 공휴일에 대한 그 결과 시간에서 차감해야 할 것입니다.

    공식

    SELECT
    DATEDIFF(minute, StartDT, EndDT) / 60.0
    - DATEDIFF(day,  StartDT, EndDT) * 16
    - DATEDIFF(week, StartDT, EndDT) * 16 AS BusinessHours
FROM T

    그것의 일부 샘플 데이터가 커버 다양한 경우에 테이블을 만들 수 있습니다 작동 방식을 이해하려면 :

    DECLARE @T TABLE (StartDT datetime2(0), EndDT datetime2(0));

INSERT INTO @T VALUES
('2012-03-05 09:00:00', '2012-03-05 15:00:00'), -- simple part of the same day
('2012-03-05 10:00:00', '2012-03-06 10:00:00'), -- full day across the midnight
('2012-03-05 11:00:00', '2012-03-06 10:00:00'), -- less than a day across the midnight
('2012-03-05 10:00:00', '2012-03-06 15:00:00'), -- more than a day across the midnight
('2012-03-09 16:00:00', '2012-03-12 10:00:00'), -- over the weekend, less than 7 days
('2012-03-06 16:00:00', '2012-03-15 10:00:00'), -- over the weekend, more than 7 days
('2012-03-09 16:00:00', '2012-03-19 10:00:00'); -- over two weekends

    쿼리

    SELECT
    StartDT, 
    EndDT,
    DATEDIFF(minute, StartDT, EndDT) / 60.0
    - DATEDIFF(day,  StartDT, EndDT) * 16
    - DATEDIFF(week, StartDT, EndDT) * 16 AS BusinessHours
FROM @T;

    다음과 같은 결과를 생성합니다 :

    +---------------------+---------------------+---------------+
|       StartDT       |        EndDT        | BusinessHours |
+---------------------+---------------------+---------------+
| 2012-03-05 09:00:00 | 2012-03-05 15:00:00 |  6.000000     |
| 2012-03-05 10:00:00 | 2012-03-06 10:00:00 |  8.000000     |
| 2012-03-05 11:00:00 | 2012-03-06 10:00:00 |  7.000000     |
| 2012-03-05 10:00:00 | 2012-03-06 15:00:00 | 13.000000     |
| 2012-03-09 16:00:00 | 2012-03-12 10:00:00 |  2.000000     |
| 2012-03-06 16:00:00 | 2012-03-15 10:00:00 | 50.000000     |
| 2012-03-09 16:00:00 | 2012-03-19 10:00:00 | 42.000000     |
+---------------------+---------------------+---------------+

    SQL에서 서버 DATEDIFF 지정된 STARTDATE 및 ENDDATE 사이에 교차 지정된 날짜 부분의 경계의 수를 반환하기 때문에 그것은 작동합니다.

    매일 8 업무 시간이있다. 나는 두 날짜 사이의 총 시간 수를 계산 한 후 다음 (토 + 일 8 + 8 근무 시간) 16를 곱한 주말의 수를 빼, 하루 16 업무 시간을 곱한 midnights의 수를 뺍니다.

  10. ==============================

    10.실제로 매우 어려운 업무 시간 동안 계정에 모든 변수 (주말, 공휴일 등)을 복용하기 전에이 작업을 수행 한,이 작업이 제일 완료 외부의 SQL 생각

    실제로 매우 어려운 업무 시간 동안 계정에 모든 변수 (주말, 공휴일 등)을 복용하기 전에이 작업을 수행 한,이 작업이 제일 완료 외부의 SQL 생각

  11. ==============================

    11.생각의 또 다른 방법은 함수 아래 작업은 일주일의 첫 날은 월요일, 그렇지 않으면 당신은 해당 지역의 주말 일 (6,7)을 포함하여 관련 라인을 변경해야합니다 경우 올바르게

    생각의 또 다른 방법은 함수 아래 작업은 일주일의 첫 날은 월요일, 그렇지 않으면 당신은 해당 지역의 주말 일 (6,7)을 포함하여 관련 라인을 변경해야합니다 경우 올바르게

    create function fn_worktime(@Datetime1 DateTime,@Datetime2 DateTime)
Returns BigInt
as
Begin
    Declare 
            @Date1 Date, 
            @Date2 Date,
            @DateIndex Date,
            @minutes int,
            @lastDayMinutes int,
            @StartTime int , --in minutes
            @FinishTime int ,--in minutes
            @WorkDayLong int --in minutes

    Set @StartTime  =8 * 60 + 30 -- 8:30
    Set @FinishTime =17* 60 + 30 -- 17:30
    Set @WorkDayLong =@FinishTime - @StartTime  

    Set @Date1 = Convert(Date,@DateTime1)
    Set @Date2 = Convert(Date,@DateTime2)
    Set @minutes=DateDiff(minute,@DateTime1,DateAdd(MINUTE,@FinishTime ,convert(DateTime,@Date1)))
    if @minutes<0 OR DatePart(dw,@Date1) in (6,7)  -- you can even check holdays here. '(6 Saturday,7 Sunday) according to SET DATEFIRST 1'
        Set @minutes=0

    Set @DateIndex=DateAdd(day,1,@Date1)
    While @DateIndex<@Date2
    Begin
        if DatePart(dw,@DateIndex) not in (6,7)  -- you can even check holdays here. '(6 Saturday,7 Sunday) according to SET DATEFIRST 1'
            set @minutes=@minutes+@WorkDayLong 
        Set @DateIndex=DateAdd(day,1,@DateIndex)
    End
    if DatePart(dw,@DateIndex) not in (6,7)  -- you can even check holdays here
    Begin
        set @lastDayMinutes=DateDiff(minute,DateAdd(MINUTE ,@StartTime ,convert(DateTime,@Date2)),@DateTime2)
        if @lastDayMinutes>@WorkDayLong 
            set @lastDayMinutes=@WorkDayLong 
        if @Date1<>@Date2   
            set @minutes=@minutes+@lastDayMinutes
        Else
            Set @minutes=@minutes+@lastDayMinutes-@WorkDayLong 

    End
    return @minutes
End
  12. ==============================

    12.이 솔루션에 대해 어떻게 생각하십니까?

    이 솔루션에 대해 어떻게 생각하십니까?

    "동안"루프를 사용하지 않고.

    create function dbo.WorkingHoursBetweenDates ( @StartDate datetime, @EndDate datetime, @StartTime time, @EndTime time )
returns decimal ( 10, 2 )
as
begin

  return
    case
      when @EndTime < @StartTime or @EndDate < @StartDate then
        0
      else
        round
        ( ( dbo.WorkingDaysBetweenDates(@StartDate, @EndDate) -
            ( dbo.WorkingDaysBetweenDates(@StartDate, @StartDate) *
              case
                when cast ( @StartDate as time ) > @EndTime then
                  1
                else
                  datediff
                  ( mi,
                    @StartTime
                    , case
                        when @StartTime > cast ( @StartDate as time ) then
                          @StartTime
                        else
                          cast ( @StartDate as time )
                      end
                  ) /
                  ( datediff ( mi, @StartTime, @EndTime ) + 0.0 )
              end
            ) -
            ( dbo.WorkingDaysBetweenDates(@EndDate, @EndDate) *
              case
                when cast ( @EndDate as time ) < @StartTime then
                  1
                else
                  datediff
                  ( mi,
                    case
                      when @EndTime < cast ( @EndDate as time ) then
                        @EndTime
                      else
                        cast ( @EndDate as time )
                    end,
                    @EndTime
                  ) /
                  ( datediff ( mi, @StartTime, @EndTime ) + 0.0 )
              end
            )
          ) *
          ( datediff ( mi, @StartTime, @EndTime ) / 60.0 ), 2
        )
    end

end
------

create function dbo.WorkingDaysBetweenDates ( @StartDate date, @EndDate date )
returns int
as
begin

  return 
    ( 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 ( 1 )
      from
        dbo.Tb_Holidays
      where
        HDate between @StartDate and @EndDate
        and datename(dw, HDate) not in ( 'Sunday', 'Saturday' )
    )

end
  13. ==============================

    13.여기에 대체 솔루션은 기능을 사용하지 않고 있습니다. 이것은 숫자 테이블의 존재에 의존합니다, 일의 적어도 최대 수는있는 거 추적 걸릴 수있는 작업으로 채워집니다.

    여기에 대체 솔루션은 기능을 사용하지 않고 있습니다. 이것은 숫자 테이블의 존재에 의존합니다, 일의 적어도 최대 수는있는 거 추적 걸릴 수있는 작업으로 채워집니다.

    이 계정에 공휴일을지지 않습니다. 당신은 개통을 설정하고 일을해야 @OpeningHours 테이블 변수에 자정에 시간을 닫는 주말을 작동하지 않는 경우.

    나는 '현실 세계'데이터의 8500 행에 대해이 테스트하고 성능이 좋은 것으로 나타났습니다.

    DECLARE @OpeningHours TABLE ([DayOfWeek] INTEGER, OpeningTime TIME(0), ClosingTime TIME(0));

INSERT
    @OpeningHours ([DayOfWeek], OpeningTime, ClosingTime)
VALUES
    (1, '10:00', '16:00') -- Sun
    , (2, '06:30', '23:00') -- Mon
    , (3, '06:30', '23:00') -- Tue
    , (4, '06:30', '23:00') -- Wed
    , (5, '06:30', '23:00') -- Thu
    , (6, '06:30', '23:00') -- Fri
    , (7, '08:00', '20:00'); -- Sat

DECLARE @Tasks TABLE ([Description] VARCHAR(50), CreatedDateTime DATETIME, CompletedDateTime DATETIME);

INSERT
    @Tasks ([Description], CreatedDateTime, CompletedDateTime)
VALUES
    ('Make tea', '20170404 10:00', '20170404 10:12')
    , ('Make coffee', '20170404 23:35', '20170405 06:32')
    , ('Write complex SQL query', '20170406 00:00', '20170406 23:32')
    , ('Rewrite complex SQL query', '20170406 23:50', '20170410 10:50');

SELECT
    WorkingMinutesToRespond =
        SUM(CASE WHEN CAST(Tasks.CreatedDateTime AS DATE) = CAST(Tasks.CompletedDateTime AS DATE) THEN
        CASE WHEN CAST(Tasks.CreatedDateTime AS TIME) < OpeningHours.OpeningTime THEN
            -- Task created before opening time
            DATEDIFF(MINUTE, OpeningHours.OpeningTime, CAST(Tasks.CompletedDateTime AS TIME))
        ELSE
            DATEDIFF(MINUTE, Tasks.CreatedDateTime, Tasks.CompletedDateTime)
        END
    ELSE
        CASE WHEN Tasks.CoveredDate = CAST(Tasks.CreatedDateTime AS DATE) THEN 
            -- This is the day the task was created
            CASE WHEN CAST(Tasks.CreatedDateTime AS TIME(0)) > OpeningHours.ClosingTime THEN
                0 -- after working hours
            ELSE
                -- during or before working hours
                CASE WHEN CAST(Tasks.CreatedDateTime AS TIME(0)) < OpeningHours.OpeningTime THEN
                    -- before opening time; take the whole day into account
                    DATEDIFF(MINUTE, OpeningHours.OpeningTime, OpeningHours.ClosingTime)
                ELSE
                    -- during opening hours; take part of the day into account
                    DATEDIFF(MINUTE, CAST(Tasks.CreatedDateTime AS TIME), OpeningHours.ClosingTime)
                END
            END
        ELSE
            -- This is the day the task was completed
            CASE WHEN Tasks.CoveredDate = CAST(Tasks.CompletedDateTime AS DATE) THEN 
                CASE WHEN CAST(Tasks.CompletedDateTime AS TIME(0)) < OpeningHours.OpeningTime THEN
                    0 -- before working hours (unlikely to occur)
                ELSE
                    -- during or after working hours
                    CASE WHEN CAST(Tasks.CompletedDateTime AS TIME(0)) > OpeningHours.ClosingTime THEN
                        -- after closing time (also unlikely); take the whole day into account
                        DATEDIFF(MINUTE, OpeningHours.OpeningTime, OpeningHours.ClosingTime)
                    ELSE
                        -- during opening hours; take part of the day into account
                        DATEDIFF(MINUTE, OpeningHours.OpeningTime, CAST(Tasks.CompletedDateTime AS TIME(0)))
                    END
                END
        ELSE
            DATEDIFF(MINUTE, OpeningHours.OpeningTime, OpeningHours.ClosingTime)
        END 
        END
    END)
    , Tasks.Description
    , Tasks.CreatedDateTime
    , Tasks.CompletedDateTime
FROM
    (
        SELECT
        Tasks.Description
        , Tasks.CreatedDateTime
        , Tasks.CompletedDateTime
        , CoveredDate = CAST(DATEADD(DAY, Numbers.Number, Tasks.CreatedDateTime) AS DATE)
    FROM
        @Tasks Tasks
        INNER JOIN (SELECT * FROM Numbers WHERE Number >= 0) Numbers ON DATEDIFF(DAY, Tasks.CreatedDateTime, Tasks.CompletedDateTime) >= Numbers.Number
) Tasks
INNER JOIN @OpeningHours OpeningHours ON DATEPART(WEEKDAY, Tasks.CoveredDate) = OpeningHours.[DayOfWeek]
GROUP BY
    Tasks.Description
    , Tasks.CreatedDateTime
    , Tasks.CompletedDateTime
ORDER BY
    Tasks.CompletedDateTime;

    14. from https://stackoverflow.com/questions/5274208/calculate-business-hours-between-two-dates by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL 서버는 가장 최근의 값을 사용하여 별개의 행을 선택  (0) 2020.04.15
[SQL] 어떻게 피벗 행과 열로 (사용자 정의 회전)  (0) 2020.04.15
[SQL] 합니까 MySQL은 MSSQL처럼 @@ ROWCOUNT에 해당하는가?  (0) 2020.04.14
[SQL] 어떻게 SQL 서버의 저장 프로 시저 매 시간마다 실행?  (0) 2020.04.14
[SQL] 데이터베이스에서 가장 가까운 숫자 값을 찾기  (0) 2020.04.14
,

티스토리툴바