T-Sql 2005 Hinzufügen von Stunden zu einem Datetime-Feld mit dem Ergebnis innerhalb von Arbeitsstunden

Question

Ich habe zwei Datetime-Felder, die ich zusammenfügen möchte. Sie haben das folgende Format: '01/01/1900 00:00:00'.

Das Hauptproblem dabei ist, dass ich möchte, dass die Berechnung nur die Arbeitsstunden berücksichtigt. Der Arbeitstag liegt zwischen 08:30 und 17:30 Uhr und umfasst nicht die Wochenenden:

Auch wenn das erste Feld außerhalb des Arbeitstages beginnt oder auf ein Wochenende fällt, sollte das zweite Feld ab dem Beginn des nächsten Arbeitstages hinzugefügt werden.

Zum Beispiel:

`'26/06/2009 15:45:00' + '01/01/1900 09:00:00' = '29/06/1900 15:45:00'

'12/07/2009 14:22:36' + '01/01/1900 18:00:00' = '13/07/1900 08:30:00'

'15/07/2009 08:50:00' + '01/01/1900 04:00:00' = '15/07/2009 12:50:00'`

Im ziemlich sicher, dass dies geht, um eine benutzerdefinierte Funktion zu erstellen, um dies heraus zu arbeiten, aber ich habe keine Ahnung, wie man sogar beginnen diese (ich bin ganz aus meiner Tiefe hier) könnte jemand bieten mir einige Ratschläge, wie dies zu erreichen?

Answer 1

Versuchen Sie dies, eventuell müssen Sie es in eine Funktion einbauen

DECLARE @Date DATETIME,
        @StartOfDay FLOAT,
        @EndOfDay FLOAT,
        @DateAdd DATETIME

SELECT  @Date ='2009-06-26 15:45:00.000',
        @StartOfDay = 8.5,
        @EndOfDay = 17.5,
        @DateAdd = '1900-01-01 09:00:00.000'

--fix up start date
--before start of day, move to start of day
IF ((CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24) < @StartOfDay)
BEGIN
    SET @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date))
END

--after close of day, move to start of next day
IF ((CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24) > @EndOfDay)
BEGIN
    SET @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date)) + 1
END

--move to monday if on weekend
WHILE DATENAME(dw, @Date) IN ('Saturday','Sunday')
BEGIN
    SET @Date = @Date + 1
END

--get the number of hours to add and the total hours per day
DECLARE @HoursPerDay FLOAT
DECLARE @HoursAdd FLOAT
SET @HoursAdd = DATEDIFF(hh, '1900-01-01 00:00:00.000', @DateAdd)
SET @HoursPerDay = @EndOfDay - @StartOfDay

--date the time of geiven day
DECLARE @CurrentHours FLOAT
SET @CurrentHours = CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24

--if we stay in the same day, all is fine
IF (@CurrentHours + @HoursAdd <= @EndOfDay)
BEGIN
    SET @Date = @Date + @DateAdd
END
ELSE
BEGIN
    --remove part of day
    SET @HoursAdd = @HoursAdd - (@EndOfDay - @CurrentHours)
    --,ove to next day
    SET @Date = DATEADD(dd,0, DATEDIFF(dd,0,@Date)) + 1

    --loop day
    WHILE @HoursAdd > 0
    BEGIN
        --add day but keep hours to add same
        IF (DATENAME(dw,@Date) IN ('Saturday','Sunday'))
        BEGIN
            SET @Date = @Date + 1
        END
        ELSE
        BEGIN
            --add a day, and reduce hours to add
            IF (@HoursAdd > @HoursPerDay)
            BEGIN
                SET @Date = @Date + 1
                SET @HoursAdd = @HoursAdd - @HoursPerDay
            END
            ELSE
            BEGIN
                --add the remainder of the day
                SET @Date = DATEADD(mi, (@HoursAdd + @StartOfDay) * 60, DATEDIFF(dd,0,@Date))
                SET @HoursAdd = 0
            END
        END     
    END
END

SELECT @Date

Ich hoffe, das hilft

Answer 2

Können Sie die Funktion dayofweek und einige Inline-Case-Anweisungen verwenden;

http://www.smallsql.de/doc/sql-functions/date-time/dayofweek.html

http://www.tizag.com/sqlTutorial/sqlcase.php

Sie würden also die Berechnung durchführen, wenn die Funktion "dayofweek" nicht "sat." oder "sun." zurückgibt; andernfalls wird eine Null zurückgegeben.

Ich denke, Sie könnten weg, ohne eine benutzerdefinierte Funktion zu schreiben, aber die Sql-Anweisung würde ein bisschen unordentlich aussehen. aber dann wieder die meisten nicht-Basis-Sql-Anweisungen sehen alle ein bisschen unordentlich!

Answer 3

/*
This sample down below updating  previous material an shown what's happened if you want to ADD more than 23 hours to a date:
*/

USE [REP]
GO
/****** Object:  UserDefinedFunction [dbo].[fct_add_hours_depend_workshift_L-V_0830_1830]    Script Date: 11/26/2018 3:22:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*

UTILIZATION

SELECT
GETDATE () as crt_date
,[rep].[dbo].[fct_add_hours_depend_workshift_L-V_0830_1830]
            (
                GETDATE()
                ,number_of_hours_as_integer -- ex: 96
            ) as_expiration_date

ORIGINAL SOURCE:

https://stackoverflow.com/questions/1130721/t-sql-2005-adding-hours-to-a-datetime-field-with-the-result-within-working-hours

*/

ALTER FUNCTION [dbo].[fct_add_hours_depend_workshift_L-V_0830_1830]
(
@Date DATETIME,
@HrsAdd INT
)

RETURNS DATETIME

as

BEGIN

DECLARE @StartOfDay FLOAT,
        @EndOfDay FLOAT

-- workshift declaration de la 8:30 la 18:30
SELECT  @StartOfDay = 8.5,
        @EndOfDay = 18.5

--fix up start date
--before start of day, move to start of day
IF ((CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24) < @StartOfDay)
BEGIN
    SET @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date))
END

--after close of day, move to start of next day
IF ((CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24) > @EndOfDay)
BEGIN
    SET @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date)) + 1
END

--move to monday if on weekend
WHILE DATENAME(dw, @Date) IN ('Saturday','Sunday')
BEGIN
    SET @Date = @Date + 1
END

--get the number of hours to add and the total hours per day
DECLARE @HoursPerDay FLOAT
DECLARE @HoursAdd FLOAT
DECLARE @DateAdd DATETIME

/*
Pentru ca scriptul initial nu-ti permite sa adaugi mai mult de 23 ore am facut o modificare:
- daca trebuie sa adaug mai putin de 23 de ore merg pe clasicul definit de dezvoltator
- daca trebuie sa adaug mai mult de 23 ore fac impartirea la 23 cu rest : ex 96 impartit cu rest la 23 = 4 zile (de workshift L-V 8:30-18:30) si 4 ore (de workshift L-V 8:30-18:30) si voi folosi numai intregul sau daca vrei 96/23 si iau  modulo
*/

-- Do I have to add more than 23 hours?

IF (@HrsAdd > 23)
BEGIN

SET @DateAdd = DATEADD
                        (dd,
                        @HrsAdd/23,
                    CAST('1900-01-01 00:00:00.000' as DATETIME)
                        )
END

ELSE

SET @DateAdd = CAST('1900-01-01 ' + CAST(@HrsAdd as NVARCHAR(2)) + ':00:00.000' as DATETIME)

SET @HoursAdd = DATEDIFF(hh, '1900-01-01 00:00:00.000', @DateAdd)
SET @HoursPerDay = @EndOfDay - @StartOfDay

--date the time of geiven day
DECLARE @CurrentHours FLOAT
SET @CurrentHours = CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24

--if we stay in the same day, all is fine
IF (@CurrentHours + @HoursAdd <= @EndOfDay)
BEGIN
    SET @Date = @Date + @DateAdd
END
ELSE
BEGIN
    --remove part of day
    SET @HoursAdd = @HoursAdd - (@EndOfDay - @CurrentHours)
    --,ove to next day
    SET @Date = DATEADD(dd,0, DATEDIFF(dd,0,@Date)) + 1

    --loop day
    WHILE @HoursAdd > 0
    BEGIN
        --add day but keep hours to add same
        IF (DATENAME(dw,@Date) IN ('Saturday','Sunday'))
        BEGIN
            SET @Date = @Date + 1
        END
        ELSE
        BEGIN
            --add a day, and reduce hours to add
            IF (@HoursAdd > @HoursPerDay)
            BEGIN
                SET @Date = @Date + 1
                SET @HoursAdd = @HoursAdd - @HoursPerDay
            END
            ELSE
            BEGIN
                --add the remainder of the day
                SET @Date = DATEADD(mi, (@HoursAdd + @StartOfDay) * 60, DATEDIFF(dd,0,@Date))
                SET @HoursAdd = 0
            END
        END     
    END
END
RETURN @Date

END