Вопросы Теги

Расчет рабочих часов между двумя датами

В продукте я продолжаю работать, мы имеем некоторые древовидные структуры, сохраненные в SQL Server, и используем технику, упомянутую выше для хранения иерархии узла в записи. т.е. 

tblTreeNode
TreeID = 1
TreeNodeID = 100
ParentTreeNodeID = 99
Hierarchy = ".33.59.99.100."
[...] (actual data payload for node)

Поддержание иерархия является хитрым битом, конечно, и использует триггеры. Но генерация его на вставлении/удалении/перемещении никогда не является рекурсивной, потому что родитель или иерархия ребенка имеют всю информацию, в которой Вы нуждаетесь.

можно получить всех потомков узла таким образом: 

SELECT * FROM tblNode WHERE Hierarchy LIKE '%.100.%'

Вот триггер вставки: 

--Setup the top level if there is any
UPDATE T 
SET T.TreeNodeHierarchy = '.' + CONVERT(nvarchar(10), T.TreeNodeID) + '.'
FROM tblTreeNode AS T
    INNER JOIN inserted i ON T.TreeNodeID = i.TreeNodeID
WHERE (i.ParentTreeNodeID IS NULL) AND (i.TreeNodeHierarchy IS NULL)

WHILE EXISTS (SELECT * FROM tblTreeNode WHERE TreeNodeHierarchy IS NULL)
    BEGIN
        --Update those items that we have enough information to update - parent has text in Hierarchy
        UPDATE CHILD 
        SET CHILD.TreeNodeHierarchy = PARENT.TreeNodeHierarchy + CONVERT(nvarchar(10),CHILD.TreeNodeID) + '.'
        FROM tblTreeNode AS CHILD 
            INNER JOIN tblTreeNode AS PARENT ON CHILD.ParentTreeNodeID = PARENT.TreeNodeID
        WHERE (CHILD.TreeNodeHierarchy IS NULL) AND (PARENT.TreeNodeHierarchy IS NOT NULL)
    END

и вот триггер обновления: 

--Only want to do something if Parent IDs were changed
IF UPDATE(ParentTreeNodeID)
    BEGIN
        --Update the changed items to reflect their new parents
        UPDATE CHILD
        SET CHILD.TreeNodeHierarchy = CASE WHEN PARENT.TreeNodeID IS NULL THEN '.' + CONVERT(nvarchar,CHILD.TreeNodeID) + '.' ELSE PARENT.TreeNodeHierarchy + CONVERT(nvarchar, CHILD.TreeNodeID) + '.' END
        FROM tblTreeNode AS CHILD 
            INNER JOIN inserted AS I ON CHILD.TreeNodeID = I.TreeNodeID
            LEFT JOIN tblTreeNode AS PARENT ON CHILD.ParentTreeNodeID = PARENT.TreeNodeID

        --Now update any sub items of the changed rows if any exist
        IF EXISTS (
                SELECT * 
                FROM tblTreeNode 
                    INNER JOIN deleted ON tblTreeNode.ParentTreeNodeID = deleted.TreeNodeID
            )
            UPDATE CHILD 
            SET CHILD.TreeNodeHierarchy = NEWPARENT.TreeNodeHierarchy + RIGHT(CHILD.TreeNodeHierarchy, LEN(CHILD.TreeNodeHierarchy) - LEN(OLDPARENT.TreeNodeHierarchy))
            FROM tblTreeNode AS CHILD 
                INNER JOIN deleted AS OLDPARENT ON CHILD.TreeNodeHierarchy LIKE (OLDPARENT.TreeNodeHierarchy + '%')
                INNER JOIN tblTreeNode AS NEWPARENT ON OLDPARENT.TreeNodeID = NEWPARENT.TreeNodeID

    END

еще один бит, проверочное ограничение для предотвращения циклической ссылки в древовидных узлах: 

ALTER TABLE [dbo].[tblTreeNode]  WITH NOCHECK ADD  CONSTRAINT [CK_tblTreeNode_TreeNodeHierarchy] CHECK  
((charindex(('.' + convert(nvarchar(10),[TreeNodeID]) + '.'),[TreeNodeHierarchy],(charindex(('.' + convert(nvarchar(10),[TreeNodeID]) + '.'),[TreeNodeHierarchy]) + 1)) = 0))

я также рекомендовал бы триггерам предотвратить больше чем один корневой узел (пустой родитель) на дерево и помешать связанным узлам принадлежать различному TreeIDs (но те немного более тривиальны, чем вышеупомянутое.)

Вы захотите проверить на свой особый случай, чтобы видеть, работает ли это решение приемлемо. Надежда это помогает!

27
задан Baran 11 March 2011 в 18:54
поделиться

5 ответов

Вот альтернативное решение, без использования функции. Обратите внимание, что это основано на существовании таблицы чисел , заполненной как минимум максимальным количеством дней, которые могут занимать отслеживаемые задачи.

Это не принимает во внимание праздничные дни. Если вы не работаете в выходные дни, установка времени открытия и закрытия в полночь в табличной переменной @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;
0
ответ дан 28 November 2019 в 04:22
поделиться

Другой способ мышления : нижеприведенная функция работает правильно, если ваш первый день недели - понедельник, в противном случае вам следует изменить соответствующие строки, включая (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
0
ответ дан 28 November 2019 в 04:22
поделиться

Вот встроенная версия Start / EndDateTime, например, 2015-03-16 09: 52: 24.000 Start / EndTime (businesshours), например, 07:00:00. Она громоздкая, но работает в вашем операторе выбора

также опубликует его в версии Function. 

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
1
ответ дан 28 November 2019 в 04:22
поделиться 
    -- =============================================
-- 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
5
ответ дан 28 November 2019 в 04:22
поделиться

Я знаю, что это сообщение очень старое, но вот функция, которую я недавно написал для вычисления рабочих часов / минут между любыми двумя событиями. Он также учитывает любые праздники, которые должны быть определены в таблице.

Функция возвращает интервал в минутах - вы можете разделить на 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
19
ответ дан 28 November 2019 в 04:22
поделиться
Другие вопросы по тегам:

Похожие вопросы: