按科室得到2个日期之间的有效分钟数,跳过节假日和上下班时间的函数

sea0302 2014-12-08 10:19:30
--需要得到2个不同日期相减的函数,跳过节假日和上下班之外的时间,即计算出的分钟为有效工作日时间
--比如分样组上下班时间(8:30-18:15) 开始日期:2014-1-3 14:15:38.000,结束日期:2014-1-4 14:33:38.000,有效工作时间为:240分钟
-- 1月4日为假期,不计算
-- 开始日期:2014-1-3 14:15:38.000,结束日期:2014-1-6 14:33:38.000, (1月4日,1月5日为假日,跳过),有效工作时间:4*60+6*60+3=603分钟


IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_HolidaiesSchedule_YearValue]') AND type = 'D')

BEGIN

ALTER TABLE [dbo].[T_Biz_HolidaiesSchedule] DROP CONSTRAINT [DF_HolidaiesSchedule_YearValue]

END



GO



IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_HolidaiesSchedule_SetDate]') AND type = 'D')

BEGIN

ALTER TABLE [dbo].[T_Biz_HolidaiesSchedule] DROP CONSTRAINT [DF_HolidaiesSchedule_SetDate]

END



GO



IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_HolidaiesSchedule_CreatedDate]') AND type = 'D')

BEGIN

ALTER TABLE [dbo].[T_Biz_HolidaiesSchedule] DROP CONSTRAINT [DF_HolidaiesSchedule_CreatedDate]

END



GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_Biz_HolidaiesSchedule]') AND type in (N'U'))

DROP TABLE [dbo].[T_Biz_HolidaiesSchedule]

GO

CREATE TABLE [dbo].[T_Biz_HolidaiesSchedule] (

	[ID] [int] NOT NULL IDENTITY (1, 1),

	[YearValue] [int] NOT NULL CONSTRAINT [DF_HolidaiesSchedule_YearValue] DEFAULT ((0)),

	[SetDate] [datetime] NOT NULL CONSTRAINT [DF_HolidaiesSchedule_SetDate] DEFAULT (getdate()),

	[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_HolidaiesSchedule_CreatedDate] DEFAULT (getdate()));

GO

ALTER TABLE [dbo].[T_Biz_HolidaiesSchedule] ADD CONSTRAINT [PK_HolidaiesSchedule] PRIMARY KEY CLUSTERED ([ID]) 

GO

INSERT INTO [dbo].[T_Biz_HolidaiesSchedule] ([YearValue], [SetDate], [CreatedDate])

	VALUES (2014, CAST(0x0000a2a900000000 AS datetime), CAST(0x0000a260011ebf2d AS datetime))



GO

INSERT INTO [dbo].[T_Biz_HolidaiesSchedule] ([YearValue], [SetDate], [CreatedDate])

	VALUES (2014, CAST(0x0000a2aa00000000 AS datetime), CAST(0x0000a260011ebf2d AS datetime))



GO

INSERT INTO [dbo].[T_Biz_HolidaiesSchedule] ([YearValue], [SetDate], [CreatedDate])

	VALUES (2014, CAST(0x0000a2b000000000 AS datetime), CAST(0x0000a260011ebf2d AS datetime))



GO

INSERT INTO [dbo].[T_Biz_HolidaiesSchedule] ([YearValue], [SetDate], [CreatedDate])

	VALUES (2014, CAST(0x0000a2b100000000 AS datetime), CAST(0x0000a260011ebf2d AS datetime))



GO

INSERT INTO [dbo].[T_Biz_HolidaiesSchedule] ([YearValue], [SetDate], [CreatedDate])

	VALUES (2014, CAST(0x0000a2b700000000 AS datetime), CAST(0x0000a260011ebf2d AS datetime))



GO

SELECT * FROM [T_Biz_HolidaiesSchedule]

GO

--节假日表T_Biz_HolidaiesSchedule, SetDate为节假日

/*--------------------------------------------------------------------------------------------

ID	YearValue	  SetDate	                   CreatedDate

1	  2014	  2014-01-04 00:00:00.000	2013-10-23 17:24:00.790

2	  2014	  2014-01-05 00:00:00.000	2013-10-23 17:24:00.790

3	  2014	  2014-01-11 00:00:00.000	2013-10-23 17:24:00.790

4	  2014	  2014-01-12 00:00:00.000	2013-10-23 17:24:00.790

5	  2014	  2014-01-18 00:00:00.000	2013-10-23 17:24:00.790

----------------------------------------------------------------------------------------------*/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_T_SYS_Department_ID]') AND type = 'D')

BEGIN

ALTER TABLE [dbo].[T_Biz_Department] DROP CONSTRAINT [DF_T_SYS_Department_ID]

END



GO



IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_T_SYS_Department_CreateTime]') AND type = 'D')

BEGIN

ALTER TABLE [dbo].[T_Biz_Department] DROP CONSTRAINT [DF_T_SYS_Department_CreateTime]

END



GO



IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_T_SYS_Department_LastUpdateTime]') AND type = 'D')

BEGIN

ALTER TABLE [dbo].[T_Biz_Department] DROP CONSTRAINT [DF_T_SYS_Department_LastUpdateTime]

END



GO





/****** Object:  Table [dbo].[T_Biz_Department]    Script Date: 12/08/2014 22:05:48 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_Biz_Department]') AND type in (N'U'))

DROP TABLE [dbo].[T_Biz_Department]

GO

CREATE TABLE [dbo].[T_Biz_Department] (

	[ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_T_SYS_Department_ID] DEFAULT (newid()),

	[CreateTime] [datetime] NULL CONSTRAINT [DF_T_SYS_Department_CreateTime] DEFAULT (getdate()),

	[LastUpdateTime] [datetime] NULL CONSTRAINT [DF_T_SYS_Department_LastUpdateTime] DEFAULT (getdate()),

	[DisplayOrder] [int] NULL,

	[OrgID] [uniqueidentifier] NULL,

	[Code] [nvarchar](32) NULL,

	[Name] [nvarchar](16) NULL,

	[Type] [nvarchar](8) NULL,

	[Address] [nvarchar](64) NULL,

	[Phone] [nvarchar](32) NULL,

	[Fax] [nvarchar](32) NULL,

	[PostCode] [nvarchar](16) NULL,

	[Email] [nvarchar](32) NULL,

	[PersonInCharge] [nvarchar](32) NULL,

	[Remarks] [nvarchar](4000) NULL,

	[State] [int] NULL,

	[CheckInTime] [time] NULL,

	[CheckOutTime] [time] NULL);

GO



INSERT INTO [dbo].[T_Biz_Department] ([ID], [CreateTime], [LastUpdateTime], [DisplayOrder], [OrgID], [Code], [Name], [Type], [Address], [Phone], [Fax], [PostCode], [Email], [PersonInCharge], [Remarks], [State], [CheckInTime], [CheckOutTime])

	VALUES (CAST ('e8130e6b-10df-4451-98bc-15d237d1be6d' AS uniqueidentifier), NULL, NULL, 1, CAST ('00000000-0000-0000-0000-000000000000' AS uniqueidentifier), N'303218956', N'分样组', N'检测实验室', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, CAST(0x070074053f47 AS time), CAST(0x07006a40f898 AS time))



GO

INSERT INTO [dbo].[T_Biz_Department] ([ID], [CreateTime], [LastUpdateTime], [DisplayOrder], [OrgID], [Code], [Name], [Type], [Address], [Phone], [Fax], [PostCode], [Email], [PersonInCharge], [Remarks], [State], [CheckInTime], [CheckOutTime])

	VALUES (CAST ('b9e06f82-6df6-4104-8aaa-857c63b27d04' AS uniqueidentifier), NULL, NULL, 4, CAST ('00000000-0000-0000-0000-000000000000' AS uniqueidentifier), N'1927528022', N'定性定量组', N'检测实验室', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, CAST(0x0700a8e76f4b AS time), CAST(0x070050cfdf96 AS time))



GO

INSERT INTO [dbo].[T_Biz_Department] ([ID], [CreateTime], [LastUpdateTime], [DisplayOrder], [OrgID], [Code], [Name], [Type], [Address], [Phone], [Fax], [PostCode], [Email], [PersonInCharge], [Remarks], [State], [CheckInTime], [CheckOutTime])

	VALUES (CAST ('a12ecbdc-70fe-4e0c-ae1b-955ef4373f0b' AS uniqueidentifier), NULL, NULL, 2, CAST ('00000000-0000-0000-0000-000000000000' AS uniqueidentifier), N'1404515189', N'生态组', N'检测实验室', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, CAST(0x0700a8e76f4b AS time), CAST(0x070050cfdf96 AS time))



GO

INSERT INTO [dbo].[T_Biz_Department] ([ID], [CreateTime], [LastUpdateTime], [DisplayOrder], [OrgID], [Code], [Name], [Type], [Address], [Phone], [Fax], [PostCode], [Email], [PersonInCharge], [Remarks], [State], [CheckInTime], [CheckOutTime])

	VALUES (CAST ('4d8033ed-10ac-4cd2-9d15-a2158b85e8b6' AS uniqueidentifier), NULL, NULL, -1, CAST ('00000000-0000-0000-0000-000000000000' AS uniqueidentifier), N'1851091055', N'客户服务部', N'检测实验室', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, CAST(0x0700a8e76f4b AS time), CAST(0x070050cfdf96 AS time))



GO

INSERT INTO [dbo].[T_Biz_Department] ([ID], [CreateTime], [LastUpdateTime], [DisplayOrder], [OrgID], [Code], [Name], [Type], [Address], [Phone], [Fax], [PostCode], [Email], [PersonInCharge], [Remarks], [State], [CheckInTime], [CheckOutTime])

	VALUES (CAST ('1c57cb24-45aa-40d6-86f0-f9950401bda8' AS uniqueidentifier), NULL, NULL, 3, CAST ('00000000-0000-0000-0000-000000000000' AS uniqueidentifier), N'1820365250', N'物理组', N'检测实验室', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, CAST(0x0700a8e76f4b AS time), CAST(0x070050cfdf96 AS time))



GO

SELECT name,CheckInTime,CheckOutTime FROM [T_Biz_Department]

GO

--上下班表T_Biz_Department

--Name 为各检验科室,CheckinTime为上班时间,CheckOutTime为下班时间

/*----------------------------------------------------------------------------

  name	     CheckInTime	       CheckOutTime

 分样组	    08:30:00.0000000	 18:15:00.0000000

定性定量组	09:00:00.0000000	 18:00:00.0000000

  生态组	   09:00:00.0000000	 18:00:00.0000000

客户服务部	09:00:00.0000000	 18:00:00.0000000

  物理组	   09:00:00.0000000	 18:00:00.0000000

*/----------------------------------------------------------------------------

--需要得到2个不同日期相减的函数,跳过节假日和上下班时间,即计算出的分钟为有效工作日时间
...全文
286 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
sea0302 2014-12-09
  • 打赏
  • 举报
 回复
引用 3 楼 Tiger_Zhao 的回复:
SQL SERVER中计算两个时间点在工作日内所占的秒数 同一个老师的作业？自己改成函数吧。
谢谢老师
Tiger_Zhao 2014-12-09
  • 打赏
  • 举报
 回复
SQL SERVER中计算两个时间点在工作日内所占的秒数
同一个老师的作业？自己改成函数吧。
sea0302 2014-12-09
  • 打赏
  • 举报
 回复
引用 1 楼 fredrickhu 的回复:
为什么要用函数呢？直接写不可以吗？
写成函数在存储过程里面使用方便点,语句也少很多,而且在这个函数里面我还嵌套了一个函数,如果用语句如何写呢?
--小F-- 2014-12-09
  • 打赏
  • 举报
 回复
为什么要用函数呢？直接写不可以吗？
大佬们，鹏新旭的一面主要会问什么问题啊，很急，明天面试了.
#面经# 总共十来分钟，面试官和蔼无八股拷打，base珠海1.自我介绍2.问项目老套路了3.根据项目提出问题看你遇到过没，怎么解决4.问了我专利，随便说了说就过。一个场景题求最短路问题，使用了bfs。第一题，模拟，成功...
java学习笔记
20230714223905834.png)] //再1~100内随机一个数，提示用户猜测，猜大则提示过大，猜小则提示过小，知道猜中结束游戏 package randomDemo; import java.util.Random; import java.util.Scanner; public class game_...
题解 | #返回2020年1月的所有订单的订单号和订单日期#
研究方向，研究意义2.项目难点，项目技术，使用了MQTT协议，介绍MQTT，如何实现订阅/发布3.内存泄漏是什么，两个启动的进程A,B，A进程的内存泄漏。是在学会宣讲会的时候可以直接面试，面试官比较关注我们的研究生...
数学建模——排队论笔记
所以实际上排队系统中的顾客数永远不会超过m个，即与模型M/M/1/m/m意义相同 要注意的是这里仍然存在有效到达率的概念，根据上述的理解： λe=λ(m-Ls) 其中m-Ls表示还能允许的强度，λ表示每个人的强度，相乘得到...
商飞上飞院飞管系统部-电控工程师(通信系统设计方向)
目前深信服、招银、中物院已开奖，深信服和中物院快到截止时间了，帮忙选选，急急如律令。美团两次二面挂了，第一次面试官全程不听讲话打哈欠，场景题，算法题，智力题全做出来第二天直接挂，昨晚第二次二面全程聊天...
疑难问题

22,298

社区成员

121,731

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章