It is very important to calculate work efficiency only.
Write a function for general work time calculation. Create Function Fun_getworkminute
(
@ Startdate Datetime ,
@ Enddate Datetime
)
Returns Int
As
Begin
Declare @ Dayminute Int , @ S_minute Int , @ S_minute2 Int
Set @ Dayminute = 7 * 60 -- Work 7 hours a day
Set @ S_minute = 11 * 60 + 30 -- Get off work at AM
Set @ S_minute2 = 14 * 60 -- Work in the afternoon
Declare @ Minute Int , @ Day Int , @ Totalday Int
Set @ Minute = 0
If @ Startdate Is Not Null And @ Enddate Is Not Null
Begin
Set @ Totalday = Datediff ( Day , @ Startdate , @ Enddate )
Set @ Day = @ Totalday / 7 -- -Week
If @ Day > 0
Set @ Minute = @ Minute + @ Day * 5 * @ Dayminute
Declare @ Sm Int , @ SM2 Int
Set @ Sm = Datepart (Hour, @ Startdate ) * 60 + Datepart (Minute, @ Startdate )
Set @ SM2 = Datepart (Hour, @ Enddate ) * 60 + Datepart (Minute, @ Enddate )
Set @ Day = @ Totalday - @ Day * 7
If @ Day > 0
Set @ Minute = @ Minute + @ Day * @ Dayminute
-- The same period of time in the previous afternoon is similar to that in non-working hours.
If ( @ Sm > = @ S_minute2 And @ SM2 > = @ S_minute2 ) Or ( @ Sm <= @ S_minute And @ SM2 <= @ S_minute )
Set @ Minute = @ Minute - @ Sm + @ SM2
Else If ( @ Sm > = @ S_minute2 ) -- From morning to afternoon
Set @ Minute = @ Minute - @ Sm + @ SM2 + @ S_minute2 - @ S_minute
Else -- From the afternoon to the morning of the next day
Set @ Minute = @ Minute - @ Sm + @ SM2 - @ S_minute2 + @ S_minute
End
Return @ Minute
End
Modules that are not processed during May 11 and May 11 require workday Management
