Create a SQL function to calculate employees' overtime time
Hello, Mr. Han has a question for you. I want to calculate overtime by seconds. The rule is: overtime is 4 hours, overtime is 8 hours, overtime is less than 4 hours, overtime is less than 4 hours, overtime is 0.5 hours. I wrote a custom function to calculate it, but the result was not correct. Trouble Mr. Han for advice.
CREATE FUNCTION worktime
(@ startTime int)
RETURNS nvarchar (100)
AS
BEGIN
Declare @ rst nvarchar
Declare @ day int
Declare @ hours int
Declare @ aa decimal
Set @ rst=''
Set @ day=@startTime/28800-- 8 hours a day, 8 hours, 28800 seconds.
Set @ hours=@startTime% 28800 Compact 3600-less than 8 hours of calculation, how many hours is the extra time
Set @ aa=0.5-there is no need for constants to create variables
If (@ day > 0)-there is no need to judge
Set @ rst=@day
If (@ hours=4)-just need to determine whether it is greater than or equal to 4 hours
Set @ rst=@rst+@aa-@ rst is a character type that cannot be added to @ aa
RETURN @ rst
After the above function is created, the execution result is not as expected select dbo.worktime (28800014300), the result is 1 select dbo.worktime (2880014500), the result is 2.
I changed and streamlined the function.
CREATE FUNCTION worktime
(@ startTime int)
RETURNS nvarchar (100)
AS
BEGIN
Declare @ rst nvarchar
Declare @ day int
Declare @ hours int
Set @ rst=''-- can be initialized for assignment
Set @ day=@startTime/28800-calculate the number of overtime days. Since @ day is of type int, the result will automatically take the integer part.
Set @ hours=@startTime% 28800 Compact 3600-calculate the advance
If (@ hours > = 4)-half-day if overtime exceeds 4 hours
Set @ rst=@day+0.5
Else set @ rst=@day-otherwise, overtime will not be counted.
RETURN @ rst
End
End
