最近在统计计算的时候，有一个需求是计算关闭时间-开始时间的小时数，但是如果中间有周末或者法定节假日不能包括在内，只计算工作日的小时数
在网上查询了很多资料后发现，有2种做法 第1种是使用存储过程的方式，另1种方法是需要一张法定节假日的表，然后关联一下，进行计算和查询。在这里我使用的是第二种方式,网上可以下载的到 例如2020年法定节假日和周末数据： https://download.csdn.net/download/u014508939/12650095 这是我上传的资源 下面我可以把数据提供出来：
如果导入失败或者错误的话 改下库名和位置就行，我这里的名称是dbo的这个，所以用的话 改成自己的
/*
Navicat Premium Data Transfer
Source Server : 关的sqlserver
Source Server Type : SQL Server
Source Server Version : 14003048
Source Host : 118.89.222.159:1433
Source Catalog : test
Source Schema : dbo
Target Server Type : SQL Server
Target Server Version : 14003048
File Encoding : 65001
Date: 22/07/2020 11:06:38
*/
-- ----------------------------
-- Table structure for sheet2
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sheet2]') AND type IN ('U'))
DROP TABLE [dbo].[sheet2]
GO
CREATE TABLE [dbo].[sheet2] (
[ID] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Hoilday] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CalcData] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO
ALTER TABLE [dbo].[sheet2] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of sheet2
-- ----------------------------
INSERT INTO [dbo].[sheet2] VALUES (N'1', N'2020-01-01', N'2020-01-02')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'2', N'2020-01-04', N'2020-01-06')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'3', N'2020-01-05', N'2020-01-06')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'4', N'2020-01-11', N'2020-01-13')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'5', N'2020-01-12', N'2020-01-13')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'6', N'2020-01-18', N'2020-01-19')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'7', N'2020-01-24', N'2020-01-26')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'8', N'2020-01-25', N'2020-01-26')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'9', N'2020-01-26', N'2020-01-28')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'10', N'2020-01-27', N'2020-01-28')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'11', N'2020-01-28', N'2020-01-30')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'12', N'2020-01-29', N'2020-01-30')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'13', N'2020-01-30', N'2020-01-31')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'14', N'2020-02-02', N'2020-02-03')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'15', N'2020-02-08', N'2020-02-10')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'16', N'2020-02-09', N'2020-02-10')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'17', N'2020-02-15', N'2020-02-17')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'18', N'2020-02-16', N'2020-02-17')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'19', N'2020-02-22', N'2020-02-24')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'20', N'2020-02-23', N'2020-02-24')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'21', N'2020-02-29', N'2020-03-02')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'22', N'2020-03-01', N'2020-03-02')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'23', N'2020-03-07', N'2020-03-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'24', N'2020-03-08', N'2020-03-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'25', N'2020-03-14', N'2020-03-16')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'26', N'2020-03-15', N'2020-03-16')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'27', N'2020-03-21', N'2020-03-23')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'28', N'2020-03-22', N'2020-03-23')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'29', N'2020-03-28', N'2020-03-30')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'30', N'2020-03-29', N'2020-03-30')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'31', N'2020-04-04', N'2020-04-07')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'32', N'2020-04-05', N'2020-04-07')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'33', N'2020-04-06', N'2020-04-07')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'34', N'2020-04-11', N'2020-04-13')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'35', N'2020-04-12', N'2020-04-13')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'36', N'2020-04-18', N'2020-04-20')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'37', N'2020-04-19', N'2020-04-20')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'38', N'2020-04-25', N'2020-04-26')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'39', N'2020-05-01', N'2020-05-06')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'40', N'2020-05-02', N'2020-05-06')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'41', N'2020-05-03', N'2020-05-06')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'42', N'2020-05-04', N'2020-05-06')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'43', N'2020-05-05', N'2020-05-06')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'44', N'2020-05-10', N'2020-05-11')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'45', N'2020-05-16', N'2020-05-18')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'46', N'2020-05-17', N'2020-05-18')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'47', N'2020-05-23', N'2020-05-25')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'48', N'2020-05-24', N'2020-05-25')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'49', N'2020-05-30', N'2020-06-01')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'50', N'2020-05-31', N'2020-06-01')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'51', N'2020-06-06', N'2020-06-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'52', N'2020-06-07', N'2020-06-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'53', N'2020-06-13', N'2020-06-15')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'54', N'2020-06-14', N'2020-06-15')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'55', N'2020-06-20', N'2020-06-22')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'56', N'2020-06-21', N'2020-06-22')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'57', N'2020-06-25', N'2020-06-28')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'58', N'2020-06-26', N'2020-06-28')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'59', N'2020-06-27', N'2020-06-28')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'60', N'2020-07-04', N'2020-07-06')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'61', N'2020-07-05', N'2020-07-06')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'62', N'2020-07-11', N'2020-07-13')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'63', N'2020-07-12', N'2020-07-13')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'64', N'2020-07-18', N'2020-07-20')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'65', N'2020-07-19', N'2020-07-20')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'66', N'2020-07-25', N'2020-07-27')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'67', N'2020-07-26', N'2020-07-27')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'68', N'2020-08-01', N'2020-08-03')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'69', N'2020-08-02', N'2020-08-03')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'70', N'2020-08-08', N'2020-08-10')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'71', N'2020-08-09', N'2020-08-10')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'72', N'2020-08-15', N'2020-08-17')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'73', N'2020-08-16', N'2020-08-17')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'74', N'2020-08-22', N'2020-08-24')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'75', N'2020-08-23', N'2020-08-24')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'76', N'2020-08-29', N'2020-08-31')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'77', N'2020-08-30', N'2020-08-31')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'78', N'2020-09-05', N'2020-09-07')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'79', N'2020-09-06', N'2020-09-07')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'80', N'2020-09-12', N'2020-09-14')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'81', N'2020-09-13', N'2020-09-14')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'82', N'2020-09-19', N'2020-09-21')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'83', N'2020-09-20', N'2020-09-21')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'84', N'2020-09-26', N'2020-09-27')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'85', N'2020-10-01', N'2020-10-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'86', N'2020-10-02', N'2020-10-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'87', N'2020-10-03', N'2020-10-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'88', N'2020-10-04', N'2020-10-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'89', N'2020-10-05', N'2020-10-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'90', N'2020-10-06', N'2020-10-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'91', N'2020-10-07', N'2020-10-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'92', N'2020-10-08', N'2020-10-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'93', N'2020-10-11', N'2020-10-12')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'94', N'2020-10-17', N'2020-10-19')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'95', N'2020-10-18', N'2020-10-19')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'96', N'2020-10-24', N'2020-10-26')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'97', N'2020-10-25', N'2020-10-26')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'98', N'2020-10-31', N'2020-11-02')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'99', N'2020-11-01', N'2020-11-02')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'100', N'2020-11-07', N'2020-11-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'101', N'2020-11-08', N'2020-11-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'102', N'2020-11-14', N'2020-11-16')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'103', N'2020-11-15', N'2020-11-16')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'104', N'2020-11-21', N'2020-11-23')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'105', N'2020-11-22', N'2020-11-23')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'106', N'2020-11-28', N'2020-11-30')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'107', N'2020-11-29', N'2020-11-30')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'108', N'2020-12-05', N'2020-12-07')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'109', N'2020-12-06', N'2020-12-07')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'110', N'2020-12-12', N'2020-12-14')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'111', N'2020-12-13', N'2020-12-14')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'112', N'2020-12-19', N'2020-12-21')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'113', N'2020-12-20', N'2020-12-21')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'114', N'2020-12-26', N'2020-12-28')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'115', N'2020-12-27', N'2020-12-28')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'116', N'2019-01-01', N'2019-01-02')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'117', N'2019-01-05', N'2019-01-07')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'118', N'2019-01-06', N'2019-01-07')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'119', N'2019-01-12', N'2019-01-14')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'120', N'2019-01-13', N'2019-01-14')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'121', N'2019-01-19', N'2019-01-21')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'122', N'2019-01-20', N'2019-01-21')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'123', N'2019-01-26', N'2019-01-28')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'124', N'2019-01-27', N'2019-01-28')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'125', N'2019-02-04', N'2019-02-11')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'126', N'2019-02-05', N'2019-02-11')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'127', N'2019-02-06', N'2019-02-11')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'128', N'2019-02-07', N'2019-02-11')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'129', N'2019-02-08', N'2019-02-11')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'130', N'2019-02-09', N'2019-02-11')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'131', N'2019-02-10', N'2019-02-11')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'132', N'2019-02-16', N'2019-02-18')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'133', N'2019-02-17', N'2019-02-18')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'134', N'2019-02-23', N'2019-02-25')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'135', N'2019-02-24', N'2019-02-25')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'136', N'2019-03-02', N'2019-03-04')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'137', N'2019-03-03', N'2019-03-04')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'138', N'2019-03-09', N'2019-03-11')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'139', N'2019-03-10', N'2019-03-11')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'140', N'2019-03-16', N'2019-03-18')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'141', N'2019-03-17', N'2019-03-18')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'142', N'2019-03-23', N'2019-03-25')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'143', N'2019-03-24', N'2019-03-25')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'144', N'2019-03-30', N'2019-04-01')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'145', N'2019-03-31', N'2019-04-01')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'146', N'2019-04-05', N'2019-04-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'147', N'2019-04-06', N'2019-04-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'148', N'2019-04-07', N'2019-04-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'149', N'2019-04-13', N'2019-04-15')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'150', N'2019-04-14', N'2019-04-15')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'151', N'2019-04-20', N'2019-04-22')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'152', N'2019-04-21', N'2019-04-22')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'153', N'2019-04-27', N'2019-04-29')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'154', N'2019-04-28', N'2019-04-29')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'155', N'2019-05-01', N'2019-05-02')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'156', N'2019-05-04', N'2019-05-06')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'157', N'2019-05-05', N'2019-05-06')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'158', N'2019-05-11', N'2019-05-13')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'159', N'2019-05-12', N'2019-05-13')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'160', N'2019-05-18', N'2019-05-20')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'161', N'2019-05-19', N'2019-05-20')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'162', N'2019-05-25', N'2019-05-27')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'163', N'2019-05-26', N'2019-05-27')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'164', N'2019-06-01', N'2019-06-03')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'165', N'2019-06-02', N'2019-06-03')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'166', N'2019-06-07', N'2019-06-10')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'167', N'2019-06-08', N'2019-06-10')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'168', N'2019-06-09', N'2019-06-10')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'169', N'2019-06-15', N'2019-06-17')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'170', N'2019-06-16', N'2019-06-17')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'171', N'2019-06-22', N'2019-06-24')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'172', N'2019-06-23', N'2019-06-24')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'173', N'2019-06-29', N'2019-07-01')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'174', N'2019-06-30', N'2019-07-01')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'175', N'2019-07-06', N'2019-07-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'176', N'2019-07-07', N'2019-07-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'177', N'2019-07-13', N'2019-07-15')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'178', N'2019-07-14', N'2019-07-15')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'179', N'2019-07-20', N'2019-07-22')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'180', N'2019-07-21', N'2019-07-22')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'181', N'2019-07-27', N'2019-07-29')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'182', N'2019-07-28', N'2019-07-29')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'183', N'2019-08-03', N'2019-08-05')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'184', N'2019-08-04', N'2019-08-05')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'185', N'2019-08-10', N'2019-08-12')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'186', N'2019-08-11', N'2019-08-12')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'187', N'2019-08-17', N'2019-08-19')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'188', N'2019-08-18', N'2019-08-19')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'189', N'2019-08-24', N'2019-08-26')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'190', N'2019-08-25', N'2019-08-26')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'191', N'2019-08-31', N'2019-09-02')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'192', N'2019-09-01', N'2019-09-02')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'193', N'2019-09-07', N'2019-09-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'194', N'2019-09-08', N'2019-09-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'195', N'2019-09-13', N'2019-09-16')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'196', N'2019-09-14', N'2019-09-16')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'197', N'2019-09-15', N'2019-09-16')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'198', N'2019-09-21', N'2019-09-23')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'199', N'2019-09-22', N'2019-09-23')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'200', N'2019-09-28', N'2019-09-29')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'201', N'2019-10-01', N'2019-10-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'202', N'2019-10-02', N'2019-10-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'203', N'2019-10-03', N'2019-10-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'204', N'2019-10-04', N'2019-10-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'205', N'2019-10-05', N'2019-10-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'206', N'2019-10-06', N'2019-10-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'207', N'2019-10-07', N'2019-10-08')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'208', N'2019-10-13', N'2019-10-14')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'209', N'2019-10-19', N'2019-10-21')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'210', N'2019-10-20', N'2019-10-21')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'211', N'2019-10-26', N'2019-10-28')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'212', N'2019-10-27', N'2019-10-28')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'213', N'2019-11-02', N'2019-11-04')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'214', N'2019-11-03', N'2019-11-04')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'215', N'2019-11-09', N'2019-11-11')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'216', N'2019-11-10', N'2019-11-11')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'217', N'2019-11-16', N'2019-11-18')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'218', N'2019-11-17', N'2019-11-18')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'219', N'2019-11-23', N'2019-11-25')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'220', N'2019-11-24', N'2019-11-25')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'221', N'2019-11-30', N'2019-12-02')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'222', N'2019-12-01', N'2019-12-02')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'223', N'2019-12-07', N'2019-12-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'224', N'2019-12-08', N'2019-12-09')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'225', N'2019-12-14', N'2019-12-16')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'226', N'2019-12-15', N'2019-12-16')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'227', N'2019-12-21', N'2019-12-23')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'228', N'2019-12-22', N'2019-12-23')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'229', N'2019-12-28', N'2019-12-30')
GO
INSERT INTO [dbo].[sheet2] VALUES (N'230', N'2019-12-29', N'2019-12-30')
GO
以上是周末和法定节假日的表结构和数据 ,现在我们测试，来一张目标表 里面就一个开始时间,结束时间 然后计算2个时间差。结构和数据如下:
/*
Navicat Premium Data Transfer
Source Server : 关的sqlserver
Source Server Type : SQL Server
Source Server Version : 14003048
Source Host : 118.89.222.159:1433
Source Catalog : test
Source Schema : dbo
Target Server Type : SQL Server
Target Server Version : 14003048
File Encoding : 65001
Date: 22/07/2020 13:32:42
*/
-- ----------------------------
-- Table structure for target_table
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[target_table]') AND type IN ('U'))
DROP TABLE [dbo].[target_table]
GO
CREATE TABLE [dbo].[target_table] (
[id] bigint NOT NULL,
[opened] datetime NULL,
[Resolved] datetime NULL
)
GO
ALTER TABLE [dbo].[target_table] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of target_table
-- ----------------------------
INSERT INTO [dbo].[target_table] VALUES (N'1', N'2020-07-21 12:00:00.000', N'2020-07-22 12:00:00.000')
GO
INSERT INTO [dbo].[target_table] VALUES (N'2', N'2020-06-23 12:00:00.000', N'2020-06-28 12:00:00.000')
GO
INSERT INTO [dbo].[target_table] VALUES (N'3', N'2020-07-01 12:00:00.000', N'2020-07-08 12:00:00.000')
GO
-- ----------------------------
-- Primary Key structure for table target_table
-- ----------------------------
ALTER TABLE [dbo].[target_table] ADD CONSTRAINT [PK__target_t__3213E83FD928CB52] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
最后 就是执行的sql语句：复制执行就可以了 如下：
SELECT
zz.opened,-- 开始时间
zz.opened_new, --冗余的开始时间，内容和opened一样
zz.Resolved, -- 结束时间
zz.resolved_new, -- 结束时间 冗余的结束时间，内容和resolevd一样
day_num,--间隔天数
Duration -- 开始时间和结束时间的小时差(只计算工作日，不计算法定节假日和周末时间)
FROM
(
SELECT
yy.opened,
yy.opened_new,
yy.Resolved,
yy.resolved_new,
(
yy.DiffDate- ( SELECT COUNT ( * ) AS dd FROM sheet2 WHERE Hoilday >= yy.opened_new AND Hoilday <= yy.Resolved_new ) * 24
) AS Duration,
DATEDIFF( d, yy.opened_new, yy.resolved_new ) AS day_num
FROM
(
SELECT
xx.opened,
xx.opened_new,
xx.Resolved,
xx.resolved_new,
DATEDIFF( HOUR, xx.opened_new, xx.resolved_new ) AS DiffDate
FROM
(
SELECT
mainData.opened,
CASE
WHEN mainData.calcdata IS NULL THEN
mainData.opened ELSE mainData.calcdata
END AS opened_new,
mainData.Resolved,
CASE
WHEN endDate.calcdata IS NULL THEN
mainData.Resolved ELSE endDate.calcdata
END AS resolved_new
FROM
(
SELECT
mainDate.opened,
mainDate.Resolved,
startDate.calcdata
FROM
target_table AS mainDate
LEFT JOIN sheet2 AS startDate ON (
DATEPART( yyyy, mainDate.opened ) = DATEPART( yyyy, startDate.hoilday ))
AND (
DATEPART( m, mainDate.opened ) = DATEPART( m, startDate.hoilday ))
AND (
DATEPART( d, mainDate.opened ) = DATEPART( d, startDate.hoilday ))) AS mainData
LEFT JOIN sheet2 AS endDate ON (
DATEPART( yyyy, mainData.Resolved ) = DATEPART( yyyy, endDate.hoilday ))
AND (
DATEPART( m, mainData.Resolved ) = DATEPART( m, endDate.hoilday ))
AND (
DATEPART( d, mainData.Resolved ) = DATEPART( d, endDate.hoilday ))
) AS xx
) AS yy
) AS zz
结果如下：
第一行数据是正常的1天24小时计算 结果为24
第二行6月23日是端午节的前2天,结束时间是周日，一共5天 不计算节假日，结果为48小时
第三行正常的一周时间 不计算周末 7天得到的小时数为5*24=120小时
如果各位的计算中也需要扣除午12点-下午1点休息的时间 我也给大家准备了SQL：
SELECT
zz.opened,-- 开始时间
zz.opened_new, --冗余的开始时间，内容和opened一样
zz.Resolved, -- 结束时间
zz.resolved_new, -- 结束时间
day_num,--间隔天数--冗余的结束时间，内容和resolevd一样
CASE
WHEN zz.day_num> 0
AND DATEPART( hh, zz.Resolved ) >= 13 THEN
zz.Duration- ( 1+day_num )
WHEN zz.day_num> 0
AND DATEPART( hh, zz.Resolved ) < 13 THEN zz.Duration- day_num WHEN zz.day_num= 0 AND DATEPART( hh, zz.Resolved ) >= 13 THEN
zz.Duration- 1 ELSE zz.Duration
END AS Duration -- 开始时间和结束时间的小时差(只计算工作日，不计算法定节假日和周末时间)
FROM
(
SELECT
yy.opened,
yy.opened_new,
yy.Resolved,
yy.resolved_new,
(
yy.DiffDate- ( SELECT COUNT ( * ) AS dd FROM sheet2 WHERE Hoilday >= yy.opened_new AND Hoilday <= yy.Resolved_new ) * 24
) AS Duration,
DATEDIFF( d, yy.opened_new, yy.resolved_new ) AS day_num
FROM
(
SELECT
xx.opened,
xx.opened_new,
xx.Resolved,
xx.resolved_new,
DATEDIFF( HOUR, xx.opened_new, xx.resolved_new ) AS DiffDate
FROM
(
SELECT
mainData.opened,
CASE
WHEN mainData.calcdata IS NULL THEN
mainData.opened ELSE mainData.calcdata
END AS opened_new,
mainData.Resolved,
CASE
WHEN endDate.calcdata IS NULL THEN
mainData.Resolved ELSE endDate.calcdata
END AS resolved_new
FROM
(
SELECT
mainDate.opened,
mainDate.Resolved,
startDate.calcdata
FROM
target_table AS mainDate
LEFT JOIN sheet2 AS startDate ON (
DATEPART( yyyy, mainDate.opened ) = DATEPART( yyyy, startDate.hoilday ))
AND (
DATEPART( m, mainDate.opened ) = DATEPART( m, startDate.hoilday ))
AND (
DATEPART( d, mainDate.opened ) = DATEPART( d, startDate.hoilday ))) AS mainData
LEFT JOIN sheet2 AS endDate ON (
DATEPART( yyyy, mainData.Resolved ) = DATEPART( yyyy, endDate.hoilday ))
AND (
DATEPART( m, mainData.Resolved ) = DATEPART( m, endDate.hoilday ))
AND (
DATEPART( d, mainData.Resolved ) = DATEPART( d, endDate.hoilday ))
) AS xx
) AS yy
) AS zz
结果如下：
只需加一个case when,其他的都不需要动(加粗的部分)
后面我会根据这个语句改一版mysql的 到时也记录下来 这个方法有一个弊端，那就是2020年一过 数据就不准确了，需要重新导入2021年的法定节假日数据了 所幸的是 这个数据网上有下载的 百度搜索 XX年周末法定节假日数据下载 就有很多了 数据需要维护，不过还行 1年维护一次 比维护更新程序的频率低多了 也还行 能用！
PS：注意 在计算中间休息的1小时我之前使用的是2个日期相减得到的day_num 这样会出现问题 而应该是得到的floor(duration/24)as day_num 来得到day_num 因为前者会把节假日的天数计算进来 后者的话是拿小时总数去除 所以应该使用后者
根据Sqlserver的语句 重新写了一个mysql版的 要用的同学，链接在这里：https://blog.csdn.net/u014508939/article/details/107518077
1743
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
