Calculare numar ore de lucru

Configurare noua (How To)

Situatie

Mai jos aveti o functie ce calculeaza si returneaza numarul de business hours intr-un interval de timp pe care functia il primeste ca parametru.

Solutie

–select dbo.BD_GetBusinessHours_prc ( ‘2021-02-08 08:00:00.000’, ‘20210209 17:00:00.000’)

create function dbo.BD_GetBusinessHours_prc

(
@StartDate datetime,
@EndDate datetime
)
returns decimal(9,2)
as
begin

declare @HoursBetween decimal(9,2)
declare @minutesBetween decimal(9,2)
declare @BusinessHours decimal(9,2)
declare @Cnt int
declare @EvalDate datetime
declare @strip as decimal(9,2)

select @HoursBetween = 0
select @BusinessHours = 0
select @Cnt=0

select @minutesBetween = datediff(mi,@StartDate,@EndDate)
select @HoursBetween = @minutesBetween/60
select @strip = right(@HoursBetween,3)

while @Cnt < @HoursBetween
begin
select @EvalDate = dateadd(hh,@Cnt,@StartDate)

if (((datepart(dw,@EvalDate) <> 1) and (datepart(dw,@EvalDate) <> 7)) and(datepart(hh,@EvalDate) not in (0,1,2,3,4,5,6,7,12,17,18,19,20,21,22,23)))

BEGIN
select @BusinessHours = @BusinessHours + 1
END
select @Cnt = @Cnt + 1
end
select @BusinessHours = @BusinessHours + @strip
return @BusinessHours
end

Tip solutie

Permanent
Etichetare:

Voteaza

(8 din 19 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?