如何用SQL只统计工作日的数据?

点击关注“SQL数据库开发”,

设为“置顶或星标”,第一时间送达干货

今天微信群里有位读者提了个问题:如何用SQL统计一个月里工作日的数据?

我觉得这个问题问的挺好的,因为我们工作中其实很多时候确实只需要统计大家工作日的数据,比如考勤,有的甚至还有排除一些法定节假日(这个问题大家可以思考一下如何解决)。

下面我们针对读者提出的这个问题,用SQL来进行求解。

题目

求解员工“张三”工作日上了多少天班?

测试数据

CREATE TABLE Tmp0317
(
姓名 VARCHAR(20),
上班时间 DATETIME,
下班时间 DATETIME
)

INSERT INTO Tmp0317 VALUE ('张三','2021-03-01 08:05:03','2021-03-01 18:25:26')
INSERT INTO Tmp0317 VALUE ('张三','2021-03-03 08:12:12','2021-03-01 18:01:16')
INSERT INTO Tmp0317 VALUE ('张三','2021-03-04 08:11:24','2021-03-01 18:09:25')
INSERT INTO Tmp0317 VALUE ('张三','2021-03-05 08:15:08','2021-03-01 18:14:43')
INSERT INTO Tmp0317 VALUE ('张三','2021-03-09 08:20:26','2021-03-01 18:23:48')
INSERT INTO Tmp0317 VALUE ('张三','2021-03-10 08:23:16','2021-03-01 18:19:04')
INSERT INTO Tmp0317 VALUE ('张三','2021-03-11 08:19:13','2021-03-01 18:26:29')
INSERT INTO Tmp0317 VALUE ('张三','2021-03-12 08:17:42','2021-03-01 18:11:12')
INSERT INTO Tmp0317 VALUE ('张三','2021-03-13 08:15:37','2021-03-01 18:10:05')

分析

要求解工作日的天数,只需要排除掉周末即可,这里我们暂不考虑忘打卡的情况。我们可以借助SQL Server里面的系统表spt_values来进行求解

具体解法

SELECT 
SUM(
CASE WHEN DATEPART(WEEKDAY,DATEADD(DD,NUMBER,'2021-03-01' )) IN (1,7) 
THEN 0  ELSE 1 END
) AS WORKDAY
FROM MASTER..SPT_VALUES
JOIN Tmp0317 ON DATEADD(DAY, NUMBER, CONVERT(DATE, '2021-03-01'))=CONVERT(DATE,上班时间)
WHERE TYPE='P' 
AND NUMBER BETWEEN 0 AND DATEDIFF(DAY, '2021-03-01', DATEADD(MONTH, 1, '2021-03-01'))-1

(提示:可以左右滑动代码)

结果为:

我们可以对照日历表看下,“张三”在这几天的工作日打开记录:

其中红色框表示工作日,绿色框表示周末,张三总共9条记录,13日周六这天应该是回公司加班了,但是我们不算正常工作日的考勤记录,所以结果是8.

代码解析

上面的代码估计很多读者看的有点懵,这里我们将代码先拆解开,看下每个函数里面的结果什么,大家就知道了。

首先是spt_values这个系统表,我们在之前的文章里有提到过具体的用法,还不会的可以翻一下以前的文章:

《巧用spt_values解决SQL中的连续日期问题

其次我们看下关联条件:

JOIN Tmp0317 ON 
DATEADD(DAY, NUMBER, CONVERT(DATE, '2021-03-01'))=CONVERT(DATE,上班时间)

这里我们单独看等号两本的结果:

SELECT 
DATEADD(DAY, NUMBER, CONVERT(DATE, '2021-03-01'))
FROM MASTER..SPT_VALUES 
WHERE TYPE='P' 
AND NUMBER BETWEEN 0 AND DATEDIFF(DAY, '2021-03-01', DATEADD(MONTH, 1, '2021-03-01'))-1

注意:这里必须加上后面的WHERE条件部分,其中后面的

DATEDIFF(DAY, '2021-03-01', DATEADD(MONTH, 1, '2021-03-01'))-1

的结果是30,这里因为我们的NUMBER是从0开始,所以后面要减去1,即从0-30,表示3月共31天

查询出的结果如下:

后面还有16-31日的记录未截取,大家可以去自己电脑上试验一下。

然后再看等号右边的结果:

SELECT CONVERT(DATE,上班时间) 
FROM Tmp0317

结果如下:

这样,我们就可以通过关联条件来获取到我们需要的上班日期了,但是这并不是工作日的上班日期,我们最后还要做一个判断,那就是SELECT后面的CASE WHEN条件了。

CASE WHEN里面的代码我们也单独执行一下:

SELECT 

DATEADD(DD,NUMBER,'2021-03-01' ),
DATEPART(WEEKDAY,DATEADD(DD,NUMBER,'2021-03-01' )) AS WORKDAY

FROM MASTER..SPT_VALUES
JOIN Tmp0317 ON DATEADD(DAY, NUMBER, CONVERT(DATETIME, '2021-03-01'))=CONVERT(DATE,上班时间)
WHERE TYPE='P' AND NUMBER BETWEEN 0 AND DATEDIFF(DAY, '2021-03-01', DATEADD(MONTH, 1, '2021-03-01'))-1

我们将DATEPART里面的嵌套函数DATEADD也单独拎出来,看下执行结果:

这里的DATEPART的功能主要是用来返回这一天是这个星期的第几天,我们的系统日历是按照美国的历法,每周的第一天是星期日,所以2021-03-01的星期一是本周的第二天,以此类推,我们得到每一天对应在本周的第几天。

知道这个结果后,我们可以得知,每个周的第2-6天是对应我们的工作日,那么我们可以取这个结果IN (2,3,4,5,6) 也可以 NOT IN (1,7)。

这里我们用CASE WHEN取的反义词,当它IN (1,7)时我们返回0,表示不统计,其他结果返回1,表示统计。

即:

SELECT 

DATEADD(DD,NUMBER,'2021-03-01' ),
DATEPART(WEEKDAY,DATEADD(DD,NUMBER,'2021-03-01' )),
CASE WHEN DATEPART(WEEKDAY,DATEADD(DD,NUMBER,'2021-03-01' )) IN (1,7) THEN 0  ELSE 1 END AS WORKDAY

FROM MASTER..SPT_VALUES
JOIN Tmp0317 ON DATEADD(DAY, NUMBER, CONVERT(DATETIME, '2021-03-01'))=CONVERT(DATE,上班时间)
WHERE TYPE='P' AND NUMBER BETWEEN 0 AND DATEDIFF(DAY, '2021-03-01', DATEADD(MONTH, 1, '2021-03-01'))-1

结果为:

对WORKDAY列进行SUM求和就得到了我们的结果8

Q:这里能不使用spt_values吗?

A:可以的,只需要构建一张临时表,表结构也只需要一列,就是一列自增长的连续整数即可

以上就是这个问题的解答过程,感兴趣的读者可以动手拆解一下,同时对于如何排除法定节假日的问题也可以思考一下,欢迎留言讨论。


最后给大家分享我写的SQL两件套:《SQL基础知识第二版》和《SQL高级知识第二版》的PDF电子版。里面有各个语法的解释、大量的实例讲解和批注等等,非常通俗易懂,方便大家跟着一起来实操。有需要的读者可以下载学习,在下面的公众号「数据前线」(非本号)后台回复关键字:SQL,就行数据前线
后台回复关键字:1024,获取一份精心整理的技术干货
后台回复关键字:进群,带你进入高手如云的交流群

记得帮忙点「」和「在看」↓

谢谢啦

  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值