求大神帮忙实现个考勤报表的代码

[qingbbbb]

公司用的考勤机用的是SQL数据库2008的，但是计算的数据不是很理想，希望大神帮忙用SQL帮忙计算下，基本就是3张表
分别是人员表，考勤记录表，和请假供出表
考勤记录表为A
1 USERID Atte_Time
2 10890895 2018-01-10 06:30:00.000
3 10890895 2018-01-10 16:43:46.000
4 10890895 2018-01-10 20:00:14.000
6 10890895 2018-01-11 06:00:49.000
7 10890895 2018-01-11 07:00:34.000
8 10890895 2018-01-12 19:00:34.000
9 10890895 2018-01-14 06:18:14.000
10 10890895 2018-01-14 16:18:14.000
人员表 表名为B
1 USERID name
2 10890895 张三
3 222222 李四
请假公出表C
1 USERID SDATE (开始时间) enddate
2 10890895 2018-01-12 12:30:00.000 2018-01-13 17:00:00.000
假设上班时间为每天上班，时间为早上08：00，到17：00
现在查询10到14号的考勤数据，希望得出一张考勤的数据表
时间 人员 上班时间 下班时间 最早考勤时间 最晚考勤时间 出勤时间 请假时间
2018-01-10 张三 08：00 17：00 06:30 20:00 13.30
2018-01-11 张三 08：00 17：00 06:00 19:00 13.00
2018-01-12 张三 08：00 17：00 07:00 （7点到请假12点30） 5.30 3.30
2018-01-13 张三 08：00 17：00 0 9
2018-01-13 张三 08：00 17：00 06:18 16:18 10
2018-01-10 李四 08：00 17：00 0
2018-01-11 李四 08：00 17：00 0
2018-01-12 李四 08：00 17：00 0
2018-01-13 李四 08：00 17：00 0 、
2018-01-13 李四 08：00 17：00 0
出勤时间为最晚考勤时间跟最早考勤时间的差，如果在工作时间内部，请假，减轻请假时间
如张三，12号的数据，12点半到5点请假，出勤时间为7点到12点半时间
李四，没有记录，现在空白记录，求大神帮忙下，这2个人的考勤数据如果用SQL实现。特别对于连续请假几天的，如何计算每天的考勤时间，有点想不明白

[Q315054403]

这个还不是三五句能表达完整的 可能涉及班次、加班、补卡之类的 几年前开发的三十多个班次，比较繁琐

[Hello World,]

你得有个排班表，不然怎么知道连续请了几天？

[qingbbbb]

引用 1 楼 apollokk 的回复:

你得有个排班表，不然怎么知道连续请了几天？

上班时间全年无休的，如果放假，会在另外一张假期表里面实现。 DECLARE @beginTime DATETIME,@endTime DATETIME SET @beginTime='2018-01-10' SET @endTime='2018-01-14' SELECT DATEADD(DAY,sv.number,@beginTime) AS rq FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND DATEDIFF(DAY,@beginTime,@endTime) 能不能用上述代码代替排班表

[听雨停了]

use Tempdb
go
--> --> 听雨停了-->生成测试数据
 
if not object_id(N'Tempdb..#tabA') is null
	drop table #tabA
Go
Create table #tabA([1] int,[USERID] int,[Atte_Time] Datetime)
Insert #tabA
select 2,10890895,'2018-01-10 06:30:00.000' union all
select 3,10890895,'2018-01-10 16:43:46.000' union all
select 4,10890895,'2018-01-10 20:00:14.000' union all
select 6,10890895,'2018-01-11 06:00:49.000' union all
select 7,10890895,'2018-01-12 07:00:34.000' union all
select 8,10890895,'2018-01-11 19:00:34.000' union all
select 9,10890895,'2018-01-14 06:18:14.000' union all
select 10,10890895,'2018-01-14 16:18:14.000'
Go
--Select * from #tabA
if not object_id(N'Tempdb..#tabB') is null
	drop table #tabB
Go
Create table #tabB([1] int,[USERID] int,[name] nvarchar(22))
Insert #tabB
select 2,10890895,N'张三' union all
select 3,222222,N'李四'
Go
--Select * from #tabB
if not object_id(N'Tempdb..#tabC') is null
	drop table #tabC
Go
Create table #tabC([1] int,[USERID] int,[SDATE] Datetime,[enddate] Datetime)
Insert #tabC
select 2,10890895,'2018-01-12 12:30:00.000','2018-01-13 17:00:00.000'
Go
--Select * from #tabC
--测试数据结束



DECLARE @begindt DATE,
		@enddt	 DATE

SET @begindt='2018-01-10'
SET @enddt='2018-01-14'

IF OBJECT_ID('tempdb..#tabD') IS NOT NULL
	DROP TABLE #tabD
SELECT dateadd(day,number,@begindt) AS dt 
INTO #tabD
FROM [master]..spt_values	
WHERE TYPE='p' AND number<=DATEDIFF(DAY,@begindt,@enddt)


;WITH cte AS (
SELECT * FROM #tabB,#tabD	
)
,cte2 AS (	
SELECT	DISTINCT b.*,
		MIN([Atte_Time]) OVER(PARTITION BY a.userid,CAST([Atte_Time] AS DATE)) AS mintime ,
		MAX([Atte_Time]) OVER(PARTITION BY a.userid,CAST([Atte_Time] AS DATE)) AS maxtime
FROM #tabA a
RIGHT JOIN cte b ON a.USERID=b.USERID AND cast(a.Atte_Time AS date)=b.dt
)
SELECT a.name,
       a.dt,
       '08:00' AS up_time,
       '17:00' AS down_tiime,
       CONVERT(VARCHAR(100), a.mintime, 24) AS mindt,
       CONVERT(VARCHAR(100), a.maxtime, 24) AS maxdt,
	   CASE WHEN ISNULL(c.SDATE,'')<>'' AND ISNULL(a.mintime,'')<>'' 
				THEN CAST(DATEDIFF(minute,a.mintime,c.SDATE) AS float)/60	--当天打卡了，也请假了
			WHEN ISNULL(c.SDATE,'')<>'' AND ISNULL(a.mintime,'')=''
				THEN  0		--当天没打卡，但请假了
			WHEN ISNULL(c.SDATE,'')='' AND ISNULL(a.mintime,'')=''
				THEN  0		--当天没打卡，也没请假
			WHEN ISNULL(c.SDATE,'')='' AND ISNULL(a.mintime,'')<>''
				THEN  CAST(DATEDIFF(minute,a.mintime,a.maxtime) AS float)/60	--当天打卡了，但没请假
       END  AS work_hrs,
       CASE WHEN ISNULL(a.mintime,'')='' AND ISNULL(c.SDATE,'')='' 
			THEN NULL	--当天没打卡，也没请假
			WHEN ISNULL(a.mintime,'')='' AND ISNULL(c.SDATE,'')<>'' 
			THEN 9		--当天没打卡，但是有请假
			WHEN ISNULL(a.mintime,'')<>'' AND ISNULL(c.SDATE,'')<>'' 
			THEN 9-CAST(DATEDIFF(minute,a.mintime,c.SDATE) AS float)/60		--当天打卡了，也请假了
	   END	AS leave_hrs
FROM   cte2 a
       LEFT JOIN #tabC c
            ON  a.USERID = c.USERID
                AND CAST(a.dt AS DATE) BETWEEN CAST(c.SDATE AS DATE) AND CAST(c.enddate AS DATE)

[qingbbbb]

没有数据的，要跟每天的时间如何拼起来，想不出来了

[听雨停了]

引用 2 楼 qingbbbb的回复:

[quote=引用 1 楼 apollokk 的回复:] 你得有个排班表，不然怎么知道连续请了几天？

上班时间全年无休的，如果放假，会在另外一张假期表里面实现。 DECLARE @beginTime DATETIME,@endTime DATETIME SET @beginTime='2018-01-10' SET @endTime='2018-01-14' SELECT DATEADD(DAY,sv.number,@beginTime) AS rq FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND DATEDIFF(DAY,@beginTime,@endTime) 能不能用上述代码代替排班表[/quote] 都知道写这个没道理写不出这报表啊

[听雨停了]

引用 5 楼 qingbbbb 的回复:

没有数据的，要跟每天的时间如何拼起来，想不出来了

上面写了解决方案给你，你看看有没有用

[ChinaITOldMan]

It can't be so easy, there should be a work schedule for the employee every employee, we can provide an idea to you how to encode it.