2.5.4 计算工作时间的函数.sql

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

转载最新推荐文章于 2024-05-21 14:23:18 发布 · 590 阅读

文章标签：

#工作 #date #function #object #table

数据库 Sql 专栏收录该内容

19 篇文章

订阅专栏

本文介绍了一个SQL脚本，用于创建工作时间表，并定义了计算任意两个日期间总工作时间的函数。通过使用CASE WHEN语句和DATE_DIFF函数精确计算工作分钟数。

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