用SQL只统计工作日数据的方法步骤
本篇内容主要讲解“用SQL只统计工作日数据的方法步骤”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“用SQL只统计工作日数据的方法步骤”吧!
题目求解员工“张三”工作日上了多少天班?
测试数据CREATETABLETmp0317(姓名VARCHAR(20),上班时间DATETIME,下班时间DATETIME)INSERTINTOTmp0317VALUE('张三','2021-03-0108:05:03','2021-03-0118:25:26')INSERTINTOTmp0317VALUE('张三','2021-03-0308:12:12','2021-03-0118:01:16')INSERTINTOTmp0317VALUE('张三','2021-03-0408:11:24','2021-03-0118:09:25')INSERTINTOTmp0317VALUE('张三','2021-03-0508:15:08','2021-03-0118:14:43')INSERTINTOTmp0317VALUE('张三','2021-03-0908:20:26','2021-03-0118:23:48')INSERTINTOTmp0317VALUE('张三','2021-03-1008:23:16','2021-03-0118:19:04')INSERTINTOTmp0317VALUE('张三','2021-03-1108:19:13','2021-03-0118:26:29')INSERTINTOTmp0317VALUE('张三','2021-03-1208:17:42','2021-03-0118:11:12')INSERTINTOTmp0317VALUE('张三','2021-03-1308:15:37','2021-03-0118:10:05')
分析要求解工作日的天数,只需要排除掉周末即可,这里我们暂不考虑忘打卡的情况。我们可以借助SQL Server里面的系统表spt_values来进行求解
具体解法SELECTSUM(CASEWHENDATEPART(WEEKDAY,DATEADD(DD,NUMBER,'2021-03-01'))IN(1,7)THEN0ELSE1END)ASWORKDAYFROMMASTER..SPT_VALUESJOINTmp0317ONDATEADD(DAY,NUMBER,CONVERT(DATE,'2021-03-01'))=CONVERT(DATE,上班时间)WHERETYPE='P'ANDNUMBERBETWEEN0ANDDATEDIFF(DAY,'2021-03-01',DATEADD(MONTH,1,'2021-03-01'))-1
(提示:可以左右滑动代码)
结果为:
我们可以对照日历表看下,“张三”在这几天的工作日打开记录:
其中红色框表示工作日,绿色框表示周末,张三总共9条记录,13日周六这天应该是回公司加班了,但是我们不算正常工作日的考勤记录,所以结果是8.
代码解析上面的代码估计很多读者看的有点懵,这里我们将代码先拆解开,看下每个函数里面的结果什么,大家就知道了。
首先是spt_values这个系统表,我们在之前的文章里有提到过具体的用法。
其次我们看下关联条件:
JOINTmp0317ONDATEADD(DAY,NUMBER,CONVERT(DATE,'2021-03-01'))=CONVERT(DATE,上班时间)
这里我们单独看等号两本的结果:
SELECTDATEADD(DAY,NUMBER,CONVERT(DATE,'2021-03-01'))FROMMASTER..SPT_VALUESWHERETYPE='P'ANDNUMBERBETWEEN0ANDDATEDIFF(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日的记录未截取,大家可以去自己电脑上试验一下。
然后再看等号右边的结果:
SELECTCONVERT(DATE,上班时间)FROMTmp0317
结果如下:
这样,我们就可以通过关联条件来获取到我们需要的上班日期了,但是这并不是工作日的上班日期,我们最后还要做一个判断,那就是SELECT后面的CASE WHEN条件了。
CASE WHEN里面的代码我们也单独执行一下:
SELECTDATEADD(DD,NUMBER,'2021-03-01'),DATEPART(WEEKDAY,DATEADD(DD,NUMBER,'2021-03-01'))ASWORKDAYFROMMASTER..SPT_VALUESJOINTmp0317ONDATEADD(DAY,NUMBER,CONVERT(DATETIME,'2021-03-01'))=CONVERT(DATE,上班时间)WHERETYPE='P'ANDNUMBERBETWEEN0ANDDATEDIFF(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,表示统计。
即:
SELECTDATEADD(DD,NUMBER,'2021-03-01'),DATEPART(WEEKDAY,DATEADD(DD,NUMBER,'2021-03-01')),CASEWHENDATEPART(WEEKDAY,DATEADD(DD,NUMBER,'2021-03-01'))IN(1,7)THEN0ELSE1ENDASWORKDAYFROMMASTER..SPT_VALUESJOINTmp0317ONDATEADD(DAY,NUMBER,CONVERT(DATETIME,'2021-03-01'))=CONVERT(DATE,上班时间)WHERETYPE='P'ANDNUMBERBETWEEN0ANDDATEDIFF(DAY,'2021-03-01',DATEADD(MONTH,1,'2021-03-01'))-1
结果为:
对WORKDAY列进行SUM求和就得到了我们的结果8
Q:这里能不使用spt_values吗?
A:可以的,只需要构建一张临时表,表结构也只需要一列,就是一列自增长的连续整数即可
到此,相信大家对“用SQL只统计工作日数据的方法步骤”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。