雇员表出勤表中,只有一个字段为上班总工时,需要计算出正常上班工时,缺勤工时和加班工时。
代码
CREATE
FUNCTION
[
dbo
]
.
[
udf_WorkingHours
]
(
)
RETURNS @WorkingHours TABLE ( [ WorkTicketId ] INT , [ OperatorDate ] DATETIME , [ NormalWorkTime ] DECIMAL ( 18 , 6 ), [ AbsentationTime ] DECIMAL ( 18 , 6 ), [ OverTime ] DECIMAL ( 18 , 6 ))
AS
BEGIN
INSERT INTO @WorkingHours
SELECT DISTINCT [ WorkTicketId ] , [ OperatorDate ] ,
-- 正常工时
( CASE WHEN ( ISNULL ( [ Presence ] , 0 ) < 8 ) THEN [ Presence ] WHEN ( ISNULL ( [ Presence ] , 0 ) >= 8 ) THEN 8 END ),
-- 缺勤工时
( CASE WHEN ( ISNULL ( [ Presence ] , 0 ) < 8 ) THEN ( 8 - [ Presence ] ) WHEN ( ISNULL ( [ Presence ] , 0 ) >= 8 ) THEN 0 END ),
-- 加班工时
( CASE WHEN ( ISNULL ( [ Presence ] , 0 ) <= 8 ) THEN 0 WHEN ( ISNULL ( [ Presence ] , 0 ) > 8 ) THEN ( [ Presence ] - 8 ) END )
FROM [ dbo ] . [ udf_WorkTicketRecorded ] ()
RETURN
END
(
)
RETURNS @WorkingHours TABLE ( [ WorkTicketId ] INT , [ OperatorDate ] DATETIME , [ NormalWorkTime ] DECIMAL ( 18 , 6 ), [ AbsentationTime ] DECIMAL ( 18 , 6 ), [ OverTime ] DECIMAL ( 18 , 6 ))
AS
BEGIN
INSERT INTO @WorkingHours
SELECT DISTINCT [ WorkTicketId ] , [ OperatorDate ] ,
-- 正常工时
( CASE WHEN ( ISNULL ( [ Presence ] , 0 ) < 8 ) THEN [ Presence ] WHEN ( ISNULL ( [ Presence ] , 0 ) >= 8 ) THEN 8 END ),
-- 缺勤工时
( CASE WHEN ( ISNULL ( [ Presence ] , 0 ) < 8 ) THEN ( 8 - [ Presence ] ) WHEN ( ISNULL ( [ Presence ] , 0 ) >= 8 ) THEN 0 END ),
-- 加班工时
( CASE WHEN ( ISNULL ( [ Presence ] , 0 ) <= 8 ) THEN 0 WHEN ( ISNULL ( [ Presence ] , 0 ) > 8 ) THEN ( [ Presence ] - 8 ) END )
FROM [ dbo ] . [ udf_WorkTicketRecorded ] ()
RETURN
END