bonjour,
j'aimerais calculer la différence entre deux datetime en tenant compte des horaires de travail et les jours fériers,
j'ai trouvez un script qui me convient mais malheureusement ce script tien compte les horaire de travail de lundi à samedi de 07:00 à 15:00 or je veut le modifier de tel sorte que l'horaire de travail pour samedi soit de 07:00 à13:00, aussi ce script exclue les dimanche, mais je veut aussi qu'il exclue aussi les jours fériers.
quelqu'un peut m'aidé svp?
USE [TYPE]
GO
/****** Object: UserDefinedFunction [dbo].[WorkTime] Script Date: 01/04/2021 16:54:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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 = '07:00'
DECLARE @WorkFinish TIME
SET @WorkFinish = '15: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)!=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
