计算两个日期之间的工作时间

最新推荐文章于 2024-05-21 14:23:18 发布

原创最新推荐文章于 2024-05-21 14:23:18 发布 · 713 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#工作 #date #function #object #table

Ms SQL Server 专栏收录该内容

115 篇文章

订阅专栏

本文介绍了一个SQL脚本，用于创建工作时间表并定义一个函数来计算任意两个日期间的工作时间。该脚本首先检查是否存在名为'tb_worktime'的表，并在存在的情况下删除它。然后创建一个新的'tb_worktime'表，包含工作开始时间、结束时间和工作时长等字段。此外，还定义了一个名为'f_WorkTime'的函数，该函数接受开始和结束日期作为参数，并返回这两个日期之间的总工作时间。

if exists (select * from dbo.sysobjects where id = object_id(N'[tb_worktime]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb_worktime]
GO

--定义工作时间表
CREATE TABLE tb_worktime(
    ID       int identity(1,1) PRIMARY KEY,            --序号
    time_start smalldatetime,                            --工作的开始时间
    time_end  smalldatetime,                           --工作的结束时间
    worktime  AS DATEDIFF(Minute,time_start,time_end)  --工作时数(分钟)
)
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkTime]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkTime]
GO

--计算两个日期之间的工作时间
CREATE FUNCTION f_WorkTime(
@date_begin datetime,  --计算的开始时间
@date_end datetime     --计算的结束时间
)RETURNS int
AS
BEGIN
    DECLARE @worktime int
    IF DATEDIFF(Day,@date_begin,@date_end)=0
        SELECT @worktime=SUM(DATEDIFF(Minute,
            CASE WHEN CONVERT(VARCHAR,@date_begin,108)>time_start
                THEN CONVERT(VARCHAR,@date_begin,108)
                ELSE time_start END,
            CASE WHEN CONVERT(VARCHAR,@date_end,108)<time_end
                THEN CONVERT(VARCHAR,@date_end,108)
                ELSE time_end END))
        FROM tb_worktime
        WHERE time_end>CONVERT(VARCHAR,@date_begin,108)
            AND time_start<CONVERT(VARCHAR,@date_end,108)
    ELSE
        SET @worktime
            =(SELECT SUM(CASE
                    WHEN CONVERT(VARCHAR,@date_begin,108)>time_start
                    THEN DATEDIFF(Minute,CONVERT(VARCHAR,@date_begin,108),time_end)
                    ELSE worktime END)
                FROM tb_worktime
                WHERE time_end>CONVERT(VARCHAR,@date_begin,108))
            +(SELECT SUM(CASE
                    WHEN CONVERT(VARCHAR,@date_end,108)<time_end
                    THEN DATEDIFF(Minute,time_start,CONVERT(VARCHAR,@date_end,108))
                    ELSE worktime END)
                FROM tb_worktime
                WHERE time_start<CONVERT(VARCHAR,@date_end,108))
            +CASE
                WHEN DATEDIFF(Day,@date_begin,@date_end)>1
                THEN (DATEDIFF(Day,@date_begin,@date_end)-1)
                    *(SELECT SUM(worktime) FROM tb_worktime)
                ELSE 0 END
    RETURN(@worktime)
END