SqlServer计算2个时间的间隔时间(工作日时间)，不包括法定节假日和周末
? ?最近在統(tǒng)計(jì)計(jì)算的時(shí)候，有一個(gè)需求是計(jì)算關(guān)閉時(shí)間-開始時(shí)間的小時(shí)數(shù)，但是如果中間有周末或者法定節(jié)假日不能包括在內(nèi)，只計(jì)算工作日的小時(shí)數(shù)
? ?在網(wǎng)上查詢了很多資料后發(fā)現(xiàn)，有2種做法? 第1種是使用存儲(chǔ)過程的方式，另1種方法是需要一張法定節(jié)假日的表，然后關(guān)聯(lián)一下，進(jìn)行計(jì)算和查詢。在這里我使用的是第二種方式,網(wǎng)上可以下載的到 例如2020年法定節(jié)假日和周末數(shù)據(jù)：?https://download.csdn.net/download/u014508939/12650095? 這是我上傳的資源? 下面我可以把數(shù)據(jù)提供出來：
如果導(dǎo)入失敗或者錯(cuò)誤的話 改下庫名和位置就行，我這里的名稱是dbo的這個(gè)，所以用的話 改成自己的
/*
Navicat Premium Data Transfer
?
Source Server : 關(guān)的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
以上是周末和法定節(jié)假日的表結(jié)構(gòu)和數(shù)據(jù) ,現(xiàn)在我們測(cè)試，來一張目標(biāo)表 里面就一個(gè)開始時(shí)間,結(jié)束時(shí)間 然后計(jì)算2個(gè)時(shí)間差。結(jié)構(gòu)和數(shù)據(jù)如下:
/*
Navicat Premium Data Transfer
?
Source Server : 關(guān)的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
?最后 就是執(zhí)行的sql語句：復(fù)制執(zhí)行就可以了 如下：
SELECT
zz.opened,-- 開始時(shí)間
zz.opened_new, --冗余的開始時(shí)間，內(nèi)容和opened一樣
zz.Resolved, -- 結(jié)束時(shí)間
zz.resolved_new, -- 結(jié)束時(shí)間?冗余的結(jié)束時(shí)間，內(nèi)容和resolevd一樣
day_num,--間隔天數(shù)
Duration -- 開始時(shí)間和結(jié)束時(shí)間的小時(shí)差(只計(jì)算工作日，不計(jì)算法定節(jié)假日和周末時(shí)間)?
?? ?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?
結(jié)果如下：
第一行數(shù)據(jù)是正常的1天24小時(shí)計(jì)算? 結(jié)果為24
第二行6月23日是端午節(jié)的前2天,結(jié)束時(shí)間是周日，一共5天 不計(jì)算節(jié)假日，結(jié)果為48小時(shí)
第三行正常的一周時(shí)間 不計(jì)算周末 7天得到的小時(shí)數(shù)為5*24=120小時(shí)
如果各位的計(jì)算中也需要扣除午12點(diǎn)-下午1點(diǎn)休息的時(shí)間 我也給大家準(zhǔn)備了SQL：
SELECT
zz.opened,-- 開始時(shí)間
zz.opened_new, --冗余的開始時(shí)間，內(nèi)容和opened一樣
zz.Resolved, -- 結(jié)束時(shí)間
zz.resolved_new, -- 結(jié)束時(shí)間?
day_num,--間隔天數(shù)--冗余的結(jié)束時(shí)間，內(nèi)容和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 -- 開始時(shí)間和結(jié)束時(shí)間的小時(shí)差(只計(jì)算工作日，不計(jì)算法定節(jié)假日和周末時(shí)間)?
?? ?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?
?? ??? ?
結(jié)果如下：
只需加一個(gè)case when,其他的都不需要?jiǎng)?加粗的部分)?
后面我會(huì)根據(jù)這個(gè)語句改一版mysql的 到時(shí)也記錄下來? 這個(gè)方法有一個(gè)弊端，那就是2020年一過 數(shù)據(jù)就不準(zhǔn)確了，需要重新導(dǎo)入2021年的法定節(jié)假日數(shù)據(jù)了 所幸的是 這個(gè)數(shù)據(jù)網(wǎng)上有下載的? 百度搜索? XX年周末法定節(jié)假日數(shù)據(jù)下載 就有很多了 數(shù)據(jù)需要維護(hù)，不過還行 1年維護(hù)一次 比維護(hù)更新程序的頻率低多了 也還行 能用！
PS：注意 在計(jì)算中間休息的1小時(shí)我之前使用的是2個(gè)日期相減得到的day_num 這樣會(huì)出現(xiàn)問題 而應(yīng)該是得到的floor(duration/24)as day_num 來得到day_num 因?yàn)榍罢邥?huì)把節(jié)假日的天數(shù)計(jì)算進(jìn)來 后者的話是拿小時(shí)總數(shù)去除 所以應(yīng)該使用后者
?
根據(jù)Sqlserver的語句 重新寫了一個(gè)mysql版的 要用的同學(xué)，鏈接在這里：https://blog.csdn.net/u014508939/article/details/107518077
?
?
總結(jié)
以上是生活随笔為你收集整理的SqlServer计算2个时间的间隔时间(工作日时间)，不包括法定节假日和周末的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 基于蓝牙协议的无线网络化传感器设计
- 下一篇： java基础-day08-面向对象(三)