请各位大侠写个计算员工试用天数的SQL语句，谢谢！

chair128 2017-09-07 06:36:14
请各位大侠写个计算员工试用天数的SQL语句，谢谢！

说明：1、从入职日期算起，试用期为10天出勤日。
2、当天出勤(含周六日)满8小时即算1天试用期，没出勤不计试用天数。

问：SQL怎么写，计算各人员当月上了多少天试用期的班，如：分别统计各人员8月有多少试用天数？9月份有多少试用天数？

（只有20分了，谢谢各位帮忙！）
数据如下：

日期 星期 工号 姓名 入职日期 上班时间 下班时间 签到时间 签退时间
2017-8-25 5 1962 梅青青 2017-8-25 20:00 5:00 19:35 8:00
2017-8-26 6 1962 梅青青 2017-8-25 20:00 5:00 19:40 8:01
2017-8-27 7 1962 梅青青 2017-8-25 20:00 5:00 19:41 8:00
2017-8-28 1 1962 梅青青 2017-8-25 20:00 5:00 19:47 8:00
2017-8-29 2 1962 梅青青 2017-8-25 20:00 5:00 19:47 8:01
2017-8-30 3 1962 梅青青 2017-8-25 20:00 5:00 19:42 8:01
2017-8-31 4 1962 梅青青 2017-8-25 20:00 5:00 19:43 8:00
2017-9-1 5 1962 梅青青 2017-8-25 20:00 5:00 19:47 8:02
2017-9-2 6 1962 梅青青 2017-8-25 20:00 5:00 19:44 8:01
2017-9-3 7 1962 梅青青 2017-8-25 20:00 5:00 19:42 8:00
2017-9-4 1 1962 梅青青 2017-8-25 20:00 5:00 19:42 8:00
2017-9-5 2 1962 梅青青 2017-8-25 20:00 5:00 19:45 8:00
2017-8-25 5 1906 王海 2017-8-25 8:30 17:30
2017-8-26 6 1906 王海 2017-8-25 8:30 17:30 8:19 18:06
2017-8-27 7 1906 王海 2017-8-25 8:30 17:30
2017-8-28 1 1906 王海 2017-8-25 8:30 17:30 8:17 23:15
2017-8-29 2 1906 王海 2017-8-25 8:30 17:30 8:15 21:05
2017-8-30 3 1906 王海 2017-8-25 8:30 17:30 7:57 20:04
2017-8-31 4 1906 王海 2017-8-25 8:30 17:30 8:17 19:53
2017-9-1 5 1906 王海 2017-8-25 8:30 17:30 8:22 17:32
2017-9-2 6 1906 王海 2017-8-25 8:30 17:30
2017-9-3 7 1906 王海 2017-8-25 8:30 17:30
2017-9-4 1 1906 王海 2017-8-25 8:30 17:30 8:21 20:01
2017-9-5 2 1906 王海 2017-8-25 8:30 17:30 8:20 19:02
2017-8-25 5 1913 游一秀 2017-8-25 8:30 17:30
2017-8-26 6 1913 游一秀 2017-8-25 8:30 17:30 7:48 20:00
2017-8-27 7 1913 游一秀 2017-8-25 8:30 17:30 7:50 20:00
2017-8-28 1 1913 游一秀 2017-8-25 8:30 17:30 7:47 20:23
2017-8-29 2 1913 游一秀 2017-8-25 8:30 17:30 7:49 20:01
2017-8-30 3 1913 游一秀 2017-8-25 8:30 17:30 7:51 20:02
2017-8-31 4 1913 游一秀 2017-8-25 8:30 17:30 7:48 20:01
2017-9-1 5 1913 游一秀 2017-8-25 8:30 17:30 7:53 20:03
2017-9-2 6 1913 游一秀 2017-8-25 8:30 17:30 7:50 20:00
2017-9-3 7 1913 游一秀 2017-8-25 8:30 17:30
2017-9-4 1 1913 游一秀 2017-8-25 8:30 17:30 7:44 20:01
2017-9-5 2 1913 游一秀 2017-8-25 8:30 17:30 7:47 20:00

...全文
1450 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
chair128 2017-09-10
  • 打赏
  • 举报
 回复
谢谢！问题已解决！
道素 2017-09-09
  • 打赏
  • 举报
 回复
首先要把试用期前后的数据分开，两种查询同样结果 

/*SQL Server 2012 +*/
select year(sdate) as yr,month(sdate) as m,sno,sname,sum(d) as days,正式 from (
     select * ,case when sum(c.d)over(partition by sno order by sdate) >10 then 1 else 0 end 正式
     from #tmp_1 as t
     cross apply(values(case when (datediff(hour,checkin,checkout)+24)%24>=8 then 1 else 0 end))c(d)
 ) as t 
  group by sno,sname,year(sdate),month(sdate),正式
/*低于2012*/  
select year(sdate) as yr,month(sdate) as m,sno,sname,sum(d) as days,正式 from ( 
     select * ,case when o.worked>10 then 1 else 0 end 正式
     from #tmp_1 as t
     cross apply(values(case when (datediff(hour,checkin,checkout)+24)%24>=8 then 1 else 0 end))c(d)
     outer apply(select sum(case when (datediff(hour,tt.checkin,tt.checkout)+24)%24>=8 then 1 else 0 end) from #tmp_1 as tt where tt.sno=t.sno and datediff(d,tt.sdate,t.sdate)>=0) o(worked)
 ) as t 
  group by sno,sname,year(sdate),month(sdate),正式 


+------+---+------+-------+------+----+
| yr   | m | sno  | sname | days | 正式 |
+------+---+------+-------+------+----+
| 2017 | 8 | 1906 | 王海    | 5    | 0  |
| 2017 | 9 | 1906 | 王海    | 3    | 0  |
| 2017 | 8 | 1913 | 游一秀   | 6    | 0  |
| 2017 | 9 | 1913 | 游一秀   | 3    | 0  |
| 2017 | 8 | 1962 | 梅青青   | 7    | 0  |
| 2017 | 9 | 1962 | 梅青青   | 3    | 0  |
| 2017 | 9 | 1962 | 梅青青   | 2    | 1  |
+------+---+------+-------+------+----+
chair128 2017-09-09
  • 打赏
  • 举报
 回复
先谢谢大家的帮助。其实我上面第1点说了，1、从入职日期算起，试用期为10天出勤日。 可能没说清楚，意思是：超过10天出勤的，以后的就转正了，不计算试用期了，想得到的结果如下： 年 月 工号 姓名 天数 试用状态 1 2017 8 1906 王海 5 试用期 2 2017 9 1906 王海 3 试用期 3 2017 8 1913 游一秀 6 试用期 4 2017 9 1913 游一秀 3 试用期 5 2017 8 1962 梅青青 7 试用期 6 2017 9 1962 梅青青 3 试用期 7 2017 9 1962 梅青青 2 试用后
道素 2017-09-08
  • 打赏
  • 举报
 回复 

select year(sdate) as yr,month(sdate) as m,sno,sname,sum(case when (datediff(hour,checkin,checkout)+24)%24>=8 then 1 else 0 end)
 from #tmp_1
 group by sno,sname,year(sdate),month(sdate)


 	yr	m	sno	sname	(No column name)
1	2017	8	1906	王海	5
2	2017	9	1906	王海	3
3	2017	8	1913	游一秀	6
4	2017	9	1913	游一秀	3
5	2017	8	1962	梅青青	7
6	2017	9	1962	梅青青	5
二月十六 2017-09-07
  • 打赏
  • 举报
 回复 
--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([日期] Date,[星期] int,[工号] BIGINT,[姓名] nvarchar(23),[入职日期] Date,[上班时间] TIME,[下班时间] TIME,[签到时间] TIME,[签退时间] TIME)

Insert #T

select '2017-8-25',5,'1962',N'梅青青','2017-8-25','20:00','5:00','19:35','8:00' union all

select '2017-8-26',6,'1962',N'梅青青','2017-8-25','20:00','5:00','19:40','8:01' union all

select '2017-8-27',7,'1962',N'梅青青','2017-8-25','20:00','5:00','19:41','8:00' union all

select '2017-8-28',1,'1962',N'梅青青','2017-8-25','20:00','5:00','19:47','8:00' union all

select '2017-8-29',2,'1962',N'梅青青','2017-8-25','20:00','5:00','19:47','8:01' union all

select '2017-8-30',3,'1962',N'梅青青','2017-8-25','20:00','5:00','19:42','8:01' union all

select '2017-8-31',4,'1962',N'梅青青','2017-8-25','20:00','5:00','19:43','8:00' union all

select '2017-9-1',5,'1962',N'梅青青','2017-8-25','20:00','5:00','19:47','8:02' union all

select '2017-9-2',6,'1962',N'梅青青','2017-8-25','20:00','5:00','19:44','8:01' union all

select '2017-9-3',7,'1962',N'梅青青','2017-8-25','20:00','5:00','19:42','8:00' union all

select '2017-9-4',1,'1962',N'梅青青','2017-8-25','20:00','5:00','19:42','8:00' union all

select '2017-9-5',2,'1962',N'梅青青','2017-8-25','20:00','5:00','19:45','8:00' union all

select '2017-8-25',5,'1906',N'王海','2017-8-25','8:30','17:30',null,null union all

select '2017-8-26',6,'1906',N'王海','2017-8-25','8:30','17:30','8:19','18:06' union all

select '2017-8-27',7,'1906',N'王海','2017-8-25','8:30','17:30',null,null union all

select '2017-8-28',1,'1906',N'王海','2017-8-25','8:30','17:30','8:17','23:15' union all

select '2017-8-29',2,'1906',N'王海','2017-8-25','8:30','17:30','8:15','21:05' union all

select '2017-8-30',3,'1906',N'王海','2017-8-25','8:30','17:30','7:57','20:04' union all

select '2017-8-31',4,'1906',N'王海','2017-8-25','8:30','17:30','8:17','19:53' union all

select '2017-9-1',5,'1906',N'王海','2017-8-25','8:30','17:30','8:22','17:32' union all

select '2017-9-2',6,'1906',N'王海','2017-8-25','8:30','17:30',null,null union all

select '2017-9-3',7,'1906',N'王海','2017-8-25','8:30','17:30',null,null union all

select '2017-9-4',1,'1906',N'王海','2017-8-25','8:30','17:30','8:21','20:01' union all

select '2017-9-5',2,'1906',N'王海','2017-8-25','8:30','17:30','8:20','19:02' union all

select '2017-8-25',5,'1913',N'游一秀','2017-8-25','8:30','17:30',null,null union all

select '2017-8-26',6,'1913',N'游一秀','2017-8-25','8:30','17:30','7:48','20:00' union all

select '2017-8-27',7,'1913',N'游一秀','2017-8-25','8:30','17:30','7:50','20:00' union all

select '2017-8-28',1,'1913',N'游一秀','2017-8-25','8:30','17:30','7:47','20:23' union all

select '2017-8-29',2,'1913',N'游一秀','2017-8-25','8:30','17:30','7:49','20:01' union all

select '2017-8-30',3,'1913',N'游一秀','2017-8-25','8:30','17:30','7:51','20:02' union all

select '2017-8-31',4,'1913',N'游一秀','2017-8-25','8:30','17:30','7:48','20:01' union all

select '2017-9-1',5,'1913',N'游一秀','2017-8-25','8:30','17:30','7:53','20:03' union all

select '2017-9-2',6,'1913',N'游一秀','2017-8-25','8:30','17:30','7:50','20:00' union all

select '2017-9-3',7,'1913',N'游一秀','2017-8-25','8:30','17:30',null,null union all

select '2017-9-4',1,'1913',N'游一秀','2017-8-25','8:30','17:30','7:44','20:01' union all

select '2017-9-5',2,'1913',N'游一秀','2017-8-25','8:30','17:30','7:47','20:00'

Go

--测试数据结束

SELECT  CONVERT(VARCHAR(7), 日期) AS 日期 ,

        工号 ,

        姓名 ,

        SUM(CASE WHEN 签到时间 < 签退时间

                      AND DATEDIFF(HOUR, 签到时间, 签退时间) >= 8 THEN 1

                 WHEN 签到时间 > 签退时间

                      AND DATEDIFF(HOUR, 签退时间, 签到时间) <= 16 THEN 1

                 ELSE 0

            END) AS 天数

FROM    #T

WHERE   签到时间 IS NOT NULL

        AND 签退时间 IS NOT NULL

        AND 签到时间 < 上班时间

        AND 签退时间 > 下班时间

		GROUP BY CONVERT(VARCHAR(7), 日期) ,

        工号 ,

        姓名



繁花尽流年 2017-09-07
  • 打赏
  • 举报
 回复 
IF OBJECT_ID('tempdb..#tmp_1') IS NOT NULL DROP TABLE #tmp_1
CREATE TABLE #tmp_1 (
	sdate DATETIME,
	weekday INT,
	sno INT,
	sname VARCHAR(20),
	entrydate DATETIME,
	officehours VARCHAR(10),
	offhours VARCHAR(10),
	checkin VARCHAR(10),
	checkout  VARCHAR(10) 
)
INSERT INTO #tmp_1
select '2017/8/25','5','1962','梅青青','2017/8/25','20:00','5:00','19:35','8:00' union all
select '2017/8/26','6','1962','梅青青','2017/8/25','20:00','5:00','19:40','8:01' union all
select '2017/8/27','7','1962','梅青青','2017/8/25','20:00','5:00','19:41','8:00' union all
select '2017/8/28','1','1962','梅青青','2017/8/25','20:00','5:00','19:47','8:00' union all
select '2017/8/29','2','1962','梅青青','2017/8/25','20:00','5:00','19:47','8:01' union all
select '2017/8/30','3','1962','梅青青','2017/8/25','20:00','5:00','19:42','8:01' union all
select '2017/8/31','4','1962','梅青青','2017/8/25','20:00','5:00','19:43','8:00' union all
select '2017/9/1','5','1962','梅青青','2017/8/25','20:00','5:00','19:47','8:02' union all
select '2017/9/2','6','1962','梅青青','2017/8/25','20:00','5:00','19:44','8:01' union all
select '2017/9/3','7','1962','梅青青','2017/8/25','20:00','5:00','19:42','8:00' union all
select '2017/9/4','1','1962','梅青青','2017/8/25','20:00','5:00','19:42','8:00' union all
select '2017/9/5','2','1962','梅青青','2017/8/25','20:00','5:00','19:45','8:00' union all
select '2017/8/25','5','1906','王海','2017/8/25','8:30','17:30','','' union all
select '2017/8/26','6','1906','王海','2017/8/25','8:30','17:30','8:19','18:06' union all
select '2017/8/27','7','1906','王海','2017/8/25','8:30','17:30','','' union all
select '2017/8/28','1','1906','王海','2017/8/25','8:30','17:30','8:17','23:15' union all
select '2017/8/29','2','1906','王海','2017/8/25','8:30','17:30','8:15','21:05' union all
select '2017/8/30','3','1906','王海','2017/8/25','8:30','17:30','7:57','20:04' union all
select '2017/8/31','4','1906','王海','2017/8/25','8:30','17:30','8:17','19:53' union all
select '2017/9/1','5','1906','王海','2017/8/25','8:30','17:30','8:22','17:32' union all
select '2017/9/2','6','1906','王海','2017/8/25','8:30','17:30','','' union all
select '2017/9/3','7','1906','王海','2017/8/25','8:30','17:30','','' union all
select '2017/9/4','1','1906','王海','2017/8/25','8:30','17:30','8:21','20:01' union all
select '2017/9/5','2','1906','王海','2017/8/25','8:30','17:30','8:20','19:02' union all
select '2017/8/25','5','1913','游一秀','2017/8/25','8:30','17:30','','' union all
select '2017/8/26','6','1913','游一秀','2017/8/25','8:30','17:30','7:48','20:00' union all
select '2017/8/27','7','1913','游一秀','2017/8/25','8:30','17:30','7:50','20:00' union all
select '2017/8/28','1','1913','游一秀','2017/8/25','8:30','17:30','7:47','20:23' union all
select '2017/8/29','2','1913','游一秀','2017/8/25','8:30','17:30','7:49','20:01' union all
select '2017/8/30','3','1913','游一秀','2017/8/25','8:30','17:30','7:51','20:02' union all
select '2017/8/31','4','1913','游一秀','2017/8/25','8:30','17:30','7:48','20:01' union all
select '2017/9/1','5','1913','游一秀','2017/8/25','8:30','17:30','7:53','20:03' union all
select '2017/9/2','6','1913','游一秀','2017/8/25','8:30','17:30','7:50','20:00' union all
select '2017/9/3','7','1913','游一秀','2017/8/25','8:30','17:30','','' union all
select '2017/9/4','1','1913','游一秀','2017/8/25','8:30','17:30','7:44','20:01' union all
select '2017/9/5','2','1913','游一秀','2017/8/25','8:30','17:30','7:47','14:00'

;WITH a AS (
	SELECT 
		   CASE WHEN ISNULL(officehours,'')='' THEN NULL ELSE CAST(CONVERT(CHAR(10),sdate,120)+' '+officehours AS DATETIME) END AS officehours
		  ,CASE WHEN ISNULL(offhours,'')='' THEN NULL ELSE CAST(CONVERT(CHAR(10),sdate,120)+' '+offhours AS DATETIME) END AS offhours
		  ,CASE WHEN ISNULL(checkin,'')='' THEN NULL ELSE CAST(CONVERT(CHAR(10),sdate,120)+' '+CASE WHEN officehours>checkin THEN officehours ELSE checkin END AS DATETIME) END AS checkin
		  ,CASE WHEN ISNULL(checkout,'')='' THEN NULL ELSE CAST(CONVERT(CHAR(10),sdate,120)+' '+CASE WHEN offhours<checkout THEN offhours ELSE checkout END AS DATETIME) END AS checkout
		  ,weekday,sno,sname,entrydate
	FROM #tmp_1
)
,b AS (
	SELECT IIF(DATEDIFF(hh,officehours,offhours)>0,DATEDIFF(hh,checkin,checkout),DATEDIFF(hh,checkin,dateadd(dd,1,checkout))) AS workhours,*
	FROM a
)

SELECT sname,CONVERT(CHAR(7),officehours,120) AS year_month,COUNT(*) AS days FROM b
WHERE ISNULL(workhours,0) >8 
GROUP BY sname,CONVERT(CHAR(7),officehours,120)
ORDER BY sname,CONVERT(CHAR(7),officehours,120)
sname year_month days -------------------- ---------- ----------- 梅青青 2017-08 7 梅青青 2017-09 5 王海 2017-08 5 王海 2017-09 3 游一秀 2017-08 6 游一秀 2017-09 3 LZ试试
C# For Unity系列之入门篇
你想当“李逍遥”式的“大侠”吗？ 这里无需计算机基础，无需编程经验，你也不必是计算机专业的在校大学生....只要爱好游戏，怀揣梦想！ 有一定自主学习能力，跟着刘老师从“编程小白”修炼为游戏研发“大虾”吧！！...
sqlserver数据库备份与还原
这个语句可以查询信息。   COPY_ONLY 适用范围： SQL Server 和 SQL 数据库托管实例。指定备份为 “ 仅复制备份 ” ，该备份不影响正常的备份顺序。   仅复制备份是独立于定期计划的常规备份而创建的。   仅...
(十五)SQL Date 函数
SQLDate函数 SQL 日期（Dates） 当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，与数据库中日期列的格式相匹配。 只要您的数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间部分，...
数据库帖子收集
这样的数据列表在存储过程中应该怎么样选择得到？ 请问造成SQL2000服务不能启动的原因有哪些？ sql server中有無類似于if 的函數,即... 谁能跟我说说期初库存怎么样的一个业务逻辑!都要处理哪些内容!需要每月进...
一个给定日期距今天（当前）的工作日数！
网上搜的sql语句，感觉很有用，记下啦   -- 求一个给定日期距今天（当前）的工作日数！CREATE OR REPLACE FUNCTION workdays_today(i_date IN VARCHAR2) -- 输入日期的参数，用varchar2类型，其日期格式为：yyyy-...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章