SQL for workday computing

 

Create function Time_Span_of_minutes (

Start_day DATETIME,

End_day DATETIME)

RETURNS FLOAT

BEGIN

-- Returns the interval between two periods calculated by minute. It is calculated by the method of approaching each day. The value is calculated by 8 hours and 480 minutes per day. It is not counted on weekends and does not take legal holidays into account.

-- If the start date is on the weekend, the current day is counted.

DECLARE minutes FLOAT;

DECLARE next_day DATETIME;

SET minutes = 0.0;

SET next_day = start_day;

IF (TIMESTAMPDIFF (DAY, start_day, end_day) <1.0) THEN

SET minutes = minutes + TIMESTAMPDIFF (MINUTE, start_day, end_day );

ELSE

While timestampdiff (DAY, next_day, end_day)> = 1.0 DO

IF (DAYOFWEEK (next_day) = 7) OR (DAYOFWEEK (next_day) = 1) THEN

SET next_day = next_day + INTERVAL 1 DAY;

ELSE

SET next_day = next_day + INTERVAL 1 DAY;

SET minutes = minutes + 480.0;

End if;

End while;

SET minutes = minutes + TIMESTAMPDIFF (MINUTE, next_day, end_day );

IF (DAYOFWEEK (start_day) = 7) OR (DAYOFWEEK (start_day) = 1) THEN

SET minutes = minutes + TIMESTAMPDIFF (MINUTE, start_day, CONVERT (CONCAT (SUBSTRING (start_day FROM 1 FOR 10), '17:30:00 '), DATETIME ));

End if;

End if;

RETURN minutes;

END;

 

Create function Time_Span_of_minutes (

Start_day DATETIME,

End_day DATETIME)

RETURNS FLOAT

BEGIN

-- Returns the interval between two periods calculated by minute. It is calculated by the method of approaching each day. The value is calculated by 8 hours and 480 minutes per day. It is not counted on weekends and does not take legal holidays into account.

-- If the start date is on the weekend, the current day is counted.

DECLARE minutes FLOAT;

DECLARE next_day DATETIME;

SET minutes = 0.0;

SET next_day = start_day;

IF (TIMESTAMPDIFF (DAY, start_day, end_day) <1.0) THEN

SET minutes = minutes + TIMESTAMPDIFF (MINUTE, start_day, end_day );

ELSE

While timestampdiff (DAY, next_day, end_day)> = 1.0 DO

IF (DAYOFWEEK (next_day) = 7) OR (DAYOFWEEK (next_day) = 1) THEN

SET next_day = next_day + INTERVAL 1 DAY;

ELSE

SET next_day = next_day + INTERVAL 1 DAY;

SET minutes = minutes + 480.0;

End if;

End while;

SET minutes = minutes + TIMESTAMPDIFF (MINUTE, next_day, end_day );

IF (DAYOFWEEK (start_day) = 7) OR (DAYOFWEEK (start_day) = 1) THEN

SET minutes = minutes + TIMESTAMPDIFF (MINUTE, start_day, CONVERT (CONCAT (SUBSTRING (start_day FROM 1 FOR 10), '17:30:00 '), DATETIME ));

End if;

End if;

RETURN minutes;

END;

No noon break

 

From Jasper keyboard dance steps