求一个复杂点的SQL语句或者存储过程 考勤报表所用 昨晚写到1点多，还是没有头绪，请大家帮帮忙

gongjie416 2016-09-27 12:11:34
现在有两张表，一张划考勤明细表


想得到这样的结果
perid 15号 16号 17号 出勤天数 迟到天数 无打卡天数 缺 打天数
2 迟到 正常 无打卡 2 1 1 1
6 无打卡 缺打 无打卡 1 0 2 1
7 迟到 正常 无打卡 2 1 1



其中addtime 为打卡日期， signtime为签到时间 一天可以多次签到，上班时间为9:00：00
下班为18:00:00
...全文
940 30 打赏 收藏 转发到动态 举报
写回复
用AI写文章
30 条回复
切换为时间正序
请发表友善的回复…
发表回复
一天世界晴 2016-10-01
  • 打赏
  • 举报
 回复 

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
  
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[addtime] Datetime,[perid] int,[signtime] Datetime)
Insert #T
select 1,'2016-04-20 08:57:17.880',2,'2016-04-20 08:57:44.263' union all
select 3,'2016-04-20 08:58:38.930',7,'2016-04-20 08:58:41.447' union all
select 4,'2016-04-20 08:58:42.693',22,'2016-04-20 08:59:11.323' union all
select 6,'2016-04-20 08:59:42.150',107,'2016-04-20 08:59:48.460' union all
select 9,'2016-04-20 09:00:25.043',1,'2016-04-20 09:00:39.357' union all
select 10,'2016-04-20 09:00:30.393',99,'2016-04-20 09:00:33.190' union all
select 64,'2016-04-20 09:57:10.980',1,'2016-04-20 09:57:21.630' union all
select 68,'2016-04-20 09:59:44.427',105,'2016-04-20 10:01:24.300' union all
select 354,'2016-04-20 18:15:07.743',2,'2016-04-20 18:16:10.397' union all
select 355,'2016-04-20 18:16:02.583',99,'2016-04-20 18:16:06.193' union all
select 356,'2016-04-20 18:16:37.543',7,'2016-04-20 18:16:40.897' union all
select 357,'2016-04-20 18:17:02.787',105,'2016-04-20 18:18:06.920' union all
select 358,'2016-04-20 18:17:08.937',107,'2016-04-20 18:18:08.790' union all
select 360,'2016-04-20 18:17:22.233',22,'2016-04-20 18:17:24.850' union all
select 388,'2016-04-20 19:21:17.770',34,'2016-04-20 19:21:33.587' union all
select 456,'2016-04-21 08:53:12.970',2,'2016-04-21 08:53:54.237' union all
select 458,'2016-04-21 08:56:52.740',107,'2016-04-21 08:56:57.783' union all
select 460,'2016-04-21 08:58:39.583',7,'2016-04-21 08:58:44.730' union all
select 462,'2016-04-21 08:59:06.070',99,'2016-04-21 08:59:08.453' union all
select 463,'2016-04-21 08:59:29.480',22,'2016-04-21 08:59:31.050' union all
select 480,'2016-04-21 09:34:30.993',105,'2016-04-21 09:34:34.537' union all
select 490,'2016-04-21 09:42:46.550',111,'2016-04-21 09:42:54.817' union all
select 491,'2016-04-21 09:43:16.053',112,'2016-04-21 09:43:19.010' union all
select 514,'2016-04-21 09:56:33.540',105,'2016-04-21 09:57:09.187' union all
select 517,'2016-04-21 09:57:24.080',105,'2016-04-21 09:57:26.227' union all
select 521,'2016-04-21 10:03:13.820',105,'2016-04-21 10:03:16.523' union all
select 571,'2016-04-21 11:39:04.620',1,'2016-04-21 11:39:37.060' union all
select 670,'2016-04-21 15:53:55.020',34,'2016-04-21 15:54:06.280' union all
select 723,'2016-04-21 19:14:08.137',2,'2016-04-21 19:14:17.187' union all
select 724,'2016-04-21 19:14:32.587',107,'2016-04-21 19:14:42.650' union all
select 725,'2016-04-21 19:14:38.420',7,'2016-04-21 19:14:41.157' union all
select 726,'2016-04-21 19:15:08.170',105,'2016-04-21 19:15:10.827' union all
select 728,'2016-04-21 19:15:34.587',99,'2016-04-21 19:15:37.133' union all
select 778,'2016-04-22 08:12:48.170',112,'2016-04-22 08:13:03.770' union all
select 813,'2016-04-22 08:57:30.573',2,'2016-04-22 08:57:55.283' union all
select 816,'2016-04-22 09:01:31.570',22,'2016-04-22 09:01:40.193' union all
select 817,'2016-04-22 09:01:39.253',7,'2016-04-22 09:02:07.550' union all
select 829,'2016-04-22 09:09:14.570',105,'2016-04-22 09:10:13.327' union all
select 836,'2016-04-22 09:18:39.307',99,'2016-04-22 09:18:43.273' union all
select 1239,'2016-04-22 17:02:29.543',7,'2016-04-22 17:02:32.590' union all
select 1304,'2016-04-22 19:01:30.980',112,'2016-04-22 19:01:40.497' union all
select 1311,'2016-04-22 19:08:01.357',105,'2016-04-22 19:08:05.527' union all
select 1312,'2016-04-22 19:08:42.620',105,'2016-04-22 19:08:47.760' union all
select 1314,'2016-04-22 19:09:40.507',7,'2016-04-22 19:09:44.367' union all
select 1315,'2016-04-22 19:10:45.520',99,'2016-04-22 19:10:50.000' union all
select 1317,'2016-04-22 19:11:21.040',22,'2016-04-22 19:11:26.097' union all
select 1324,'2016-04-22 19:20:05.173',2,'2016-04-22 19:20:32.737' union all
select 1811,'2016-04-24 14:09:07.737',107,'2016-04-24 14:09:15.130' union all
select 1824,'2016-04-24 14:23:03.697',7,'2016-04-24 14:23:39.540' union all
select 1826,'2016-04-24 14:23:26.257',2,'2016-04-24 14:23:33.120' union all
select 1829,'2016-04-24 14:26:30.553',22,'2016-04-24 14:26:41.117' union all
select 1832,'2016-04-24 14:28:10.387',99,'2016-04-24 14:28:12.457' union all
select 1840,'2016-04-24 14:33:14.160',112,'2016-04-24 14:33:25.513' union all
select 1845,'2016-04-24 14:35:38.290',112,'2016-04-24 14:35:42.120' union all
select 1849,'2016-04-24 14:36:53.400',105,'2016-04-24 14:37:01.133' union all
select 2010,'2016-04-24 16:59:54.453',7,'2016-04-24 17:00:19.370' union all
select 2011,'2016-04-24 17:01:03.860',2,'2016-04-24 17:01:14.830' union all
select 2012,'2016-04-24 17:06:04.140',22,'2016-04-24 17:06:25.920' union all
select 2013,'2016-04-24 17:06:30.770',99,'2016-04-24 17:06:35.157' union all
select 2018,'2016-04-24 17:09:19.590',99,'2016-04-24 17:09:26.903' union all
select 2024,'2016-04-24 17:14:19.757',112,'2016-04-24 17:14:23.527' union all
select 2027,'2016-04-24 17:20:55.700',107,'2016-04-24 17:21:00.160' union all
select 2048,'2016-04-24 17:51:01.910',105,'2016-04-24 17:51:44.887' union all
select 2128,'2016-04-25 08:54:52.230',112,'2016-04-25 08:55:45.013' union all
select 2129,'2016-04-25 08:55:55.903',2,'2016-04-25 08:56:13.177' union all
select 2131,'2016-04-25 08:56:43.410',7,'2016-04-25 08:56:48.930' union all
select 2132,'2016-04-25 08:56:54.260',22,'2016-04-25 08:56:55.947' union all
select 2133,'2016-04-25 08:57:04.767',22,'2016-04-25 08:57:13.647' union all
select 2138,'2016-04-25 08:58:48.437',99,'2016-04-25 08:59:58.480' union all
select 2140,'2016-04-25 08:59:58.580',105,'2016-04-25 09:00:19.157' union all
select 2142,'2016-04-25 09:00:33.620',99,'2016-04-25 09:00:40.550' union all
select 2172,'2016-04-25 09:15:52.783',107,'2016-04-25 09:16:52.790' union all
select 2212,'2016-04-25 09:33:26.413',70,'2016-04-25 09:34:36.667' union all
select 2907,'2016-04-25 18:53:35.943',7,'2016-04-25 18:54:02.210' union all
select 2908,'2016-04-25 18:53:43.920',105,'2016-04-25 18:54:08.087' union all
select 2909,'2016-04-25 18:53:45.137',107,'2016-04-25 18:54:16.847' union all
select 2910,'2016-04-25 18:54:05.267',112,'2016-04-25 18:54:12.090' union all
select 2911,'2016-04-25 18:54:29.120',128,'2016-04-25 18:55:53.053' union all
select 2912,'2016-04-25 18:54:36.293',99,'2016-04-25 18:54:39.620' union all
select 2920,'2016-04-25 18:58:47.703',2,'2016-04-25 18:59:18.027' union all
select 2979,'2016-04-26 08:55:55.280',105,'2016-04-26 08:56:31.597' union all
select 2980,'2016-04-26 08:55:57.617',2,'2016-04-26 08:56:05.287' union all
select 2981,'2016-04-26 08:56:26.690',22,'2016-04-26 08:56:32.857' union all
select 2982,'2016-04-26 08:56:37.260',7,'2016-04-26 08:56:40.077' union all
select 2983,'2016-04-26 08:57:06.440',99,'2016-04-26 08:57:08.620' union all
select 2985,'2016-04-26 08:57:14.323',128,'2016-04-26 08:57:25.350' union all
select 2991,'2016-04-26 09:02:41.207',1,'2016-04-26 09:03:00.243' union all
select 2993,'2016-04-26 09:04:08.927',112,'2016-04-26 09:04:16.037' union all
select 3061,'2016-04-26 13:53:55.430',99,'2016-04-26 13:54:42.450' union all
select 3295,'2016-04-26 19:12:12.040',112,'2016-04-26 19:12:19.023' union all
select 3299,'2016-04-26 19:14:09.003',105,'2016-04-26 19:14:56.330' union all
select 3305,'2016-04-26 19:15:44.793',128,'2016-04-26 19:15:48.253' union all
select 3308,'2016-04-26 19:17:56.480',2,'2016-04-26 19:18:17.387' union all
select 3309,'2016-04-26 19:18:02.833',7,'2016-04-26 19:18:06.280' union all
select 3310,'2016-04-26 19:18:48.040',99,'2016-04-26 19:18:51.730' union all
select 3330,'2016-04-26 19:28:03.630',7,'2016-04-26 19:28:30.093' union all
select 3344,'2016-04-26 19:40:34.237',22,'2016-04-26 19:40:40.253' union all
select 3396,'2016-04-27 08:37:53.213',112,'2016-04-27 08:39:04.630' union all
select 3403,'2016-04-27 08:46:36.530',105,'2016-04-27 08:47:15.960' union all
select 3408,'2016-04-27 08:53:44.273',2,'2016-04-27 08:53:53.480'
Go
declare @date date = '2016-04-29'
declare @PivotedColumn varchar(max) = ''
select @PivotedColumn += '[' + datename(day, addtime) + '],' 
from #T 
where month(addtime) = month(@date) and year(addtime) = year(@date)
group by datename(day, addtime) 
order by datename(day, addtime)

if len(@PivotedColumn) = 0
	print 'no data'
else begin

declare @ExecCommand varchar(max) = '
	with NewTable as (
					select [Unique].perid, [Unique].day, [State].state
					from 
						(
						select perid, min(addtime) as addtime, day(addtime) as day
						from #T 
						where month(addtime) = ' + datename(month, @date) + 'and year(addtime) = ' + datename(year, @date) +'
						group by perid, day(addtime)
						) as [Unique] 
						left join 
						(
						select top 100 percent addtime, case when convert(time, addtime) <= ''9:00'' then ''正常''
															when convert(time, addtime) between ''9:00'' and ''18:00'' then ''迟到''
															when convert(time, addtime)  >= ''18:00'' then ''缺打'' 
															end as state
						from #T 
						where month(addtime) = ' + datename(month, @date) + 'and year(addtime) = ' + datename(year, @date) +'
						order by addtime
						) as [State] 
						on [Unique].addtime = [State].addtime
					)
	select t.perid,' + @PivotedColumn + ' 
		[正常] as [出勤天数], 
		[迟到] as [迟到天数], 
		[缺打] as [缺打天数], 
		' + cast(datediff(day, @date, dateadd(month, 1, @date)) as char(2)) + '- [正常] - [迟到] - [缺打] as [未打卡天数]
	from 
		NewTable pivot(min(state) for [day] in(' + substring(@PivotedColumn, 1, len(@PivotedColumn)-1) + ')) t
		inner join 
		NewTable pivot(count(day) for [State] in([正常], [迟到], [缺打])) tt 
		on t.perid = tt.perid
		
'
exec(@ExecCommand)

end
drop table #T
中国风 2016-09-29
  • 打赏
  • 举报
 回复
把日期加在条件里，用以上语句测测 
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[addtime] Datetime,[perid] int,[signtime] Datetime)
Insert #T
select 1,'2016-04-20 08:57:17.880',2,'2016-04-20 08:57:44.263' union all
select 3,'2016-04-20 08:58:38.930',7,'2016-04-20 08:58:41.447' union all
select 4,'2016-04-20 08:58:42.693',22,'2016-04-20 08:59:11.323' union all
select 6,'2016-04-20 08:59:42.150',107,'2016-04-20 08:59:48.460' union all
select 9,'2016-04-20 09:00:25.043',1,'2016-04-20 09:00:39.357' union all
select 10,'2016-04-20 09:00:30.393',99,'2016-04-20 09:00:33.190' union all
select 64,'2016-04-20 09:57:10.980',1,'2016-04-20 09:57:21.630' union all
select 68,'2016-04-20 09:59:44.427',105,'2016-04-20 10:01:24.300' union all
select 354,'2016-04-20 18:15:07.743',2,'2016-04-20 18:16:10.397' union all
select 355,'2016-04-20 18:16:02.583',99,'2016-04-20 18:16:06.193' union all
select 356,'2016-04-20 18:16:37.543',7,'2016-04-20 18:16:40.897' union all
select 357,'2016-04-20 18:17:02.787',105,'2016-04-20 18:18:06.920' union all
select 358,'2016-04-20 18:17:08.937',107,'2016-04-20 18:18:08.790' union all
select 360,'2016-04-20 18:17:22.233',22,'2016-04-20 18:17:24.850' union all
select 388,'2016-04-20 19:21:17.770',34,'2016-04-20 19:21:33.587' union all
select 456,'2016-04-21 08:53:12.970',2,'2016-04-21 08:53:54.237' union all
select 458,'2016-04-21 08:56:52.740',107,'2016-04-21 08:56:57.783' union all
select 460,'2016-04-21 08:58:39.583',7,'2016-04-21 08:58:44.730' union all
select 462,'2016-04-21 08:59:06.070',99,'2016-04-21 08:59:08.453' union all
select 463,'2016-04-21 08:59:29.480',22,'2016-04-21 08:59:31.050' union all
select 480,'2016-04-21 09:34:30.993',105,'2016-04-21 09:34:34.537' union all
select 490,'2016-04-21 09:42:46.550',111,'2016-04-21 09:42:54.817' union all
select 491,'2016-04-21 09:43:16.053',112,'2016-04-21 09:43:19.010' union all
select 514,'2016-04-21 09:56:33.540',105,'2016-04-21 09:57:09.187' union all
select 517,'2016-04-21 09:57:24.080',105,'2016-04-21 09:57:26.227' union all
select 521,'2016-04-21 10:03:13.820',105,'2016-04-21 10:03:16.523' union all
select 571,'2016-04-21 11:39:04.620',1,'2016-04-21 11:39:37.060' union all
select 670,'2016-04-21 15:53:55.020',34,'2016-04-21 15:54:06.280' union all
select 723,'2016-04-21 19:14:08.137',2,'2016-04-21 19:14:17.187' union all
select 724,'2016-04-21 19:14:32.587',107,'2016-04-21 19:14:42.650' union all
select 725,'2016-04-21 19:14:38.420',7,'2016-04-21 19:14:41.157' union all
select 726,'2016-04-21 19:15:08.170',105,'2016-04-21 19:15:10.827' union all
select 728,'2016-04-21 19:15:34.587',99,'2016-04-21 19:15:37.133' union all
select 778,'2016-04-22 08:12:48.170',112,'2016-04-22 08:13:03.770' union all
select 813,'2016-04-22 08:57:30.573',2,'2016-04-22 08:57:55.283' union all
select 816,'2016-04-22 09:01:31.570',22,'2016-04-22 09:01:40.193' union all
select 817,'2016-04-22 09:01:39.253',7,'2016-04-22 09:02:07.550' union all
select 829,'2016-04-22 09:09:14.570',105,'2016-04-22 09:10:13.327' union all
select 836,'2016-04-22 09:18:39.307',99,'2016-04-22 09:18:43.273' union all
select 1239,'2016-04-22 17:02:29.543',7,'2016-04-22 17:02:32.590' union all
select 1304,'2016-04-22 19:01:30.980',112,'2016-04-22 19:01:40.497' union all
select 1311,'2016-04-22 19:08:01.357',105,'2016-04-22 19:08:05.527' union all
select 1312,'2016-04-22 19:08:42.620',105,'2016-04-22 19:08:47.760' union all
select 1314,'2016-04-22 19:09:40.507',7,'2016-04-22 19:09:44.367' union all
select 1315,'2016-04-22 19:10:45.520',99,'2016-04-22 19:10:50.000' union all
select 1317,'2016-04-22 19:11:21.040',22,'2016-04-22 19:11:26.097' union all
select 1324,'2016-04-22 19:20:05.173',2,'2016-04-22 19:20:32.737' union all
select 1811,'2016-04-24 14:09:07.737',107,'2016-04-24 14:09:15.130' union all
select 1824,'2016-04-24 14:23:03.697',7,'2016-04-24 14:23:39.540' union all
select 1826,'2016-04-24 14:23:26.257',2,'2016-04-24 14:23:33.120' union all
select 1829,'2016-04-24 14:26:30.553',22,'2016-04-24 14:26:41.117' union all
select 1832,'2016-04-24 14:28:10.387',99,'2016-04-24 14:28:12.457' union all
select 1840,'2016-04-24 14:33:14.160',112,'2016-04-24 14:33:25.513' union all
select 1845,'2016-04-24 14:35:38.290',112,'2016-04-24 14:35:42.120' union all
select 1849,'2016-04-24 14:36:53.400',105,'2016-04-24 14:37:01.133' union all
select 2010,'2016-04-24 16:59:54.453',7,'2016-04-24 17:00:19.370' union all
select 2011,'2016-04-24 17:01:03.860',2,'2016-04-24 17:01:14.830' union all
select 2012,'2016-04-24 17:06:04.140',22,'2016-04-24 17:06:25.920' union all
select 2013,'2016-04-24 17:06:30.770',99,'2016-04-24 17:06:35.157' union all
select 2018,'2016-04-24 17:09:19.590',99,'2016-04-24 17:09:26.903' union all
select 2024,'2016-04-24 17:14:19.757',112,'2016-04-24 17:14:23.527' union all
select 2027,'2016-04-24 17:20:55.700',107,'2016-04-24 17:21:00.160' union all
select 2048,'2016-04-24 17:51:01.910',105,'2016-04-24 17:51:44.887' union all
select 2128,'2016-04-25 08:54:52.230',112,'2016-04-25 08:55:45.013' union all
select 2129,'2016-04-25 08:55:55.903',2,'2016-04-25 08:56:13.177' union all
select 2131,'2016-04-25 08:56:43.410',7,'2016-04-25 08:56:48.930' union all
select 2132,'2016-04-25 08:56:54.260',22,'2016-04-25 08:56:55.947' union all
select 2133,'2016-04-25 08:57:04.767',22,'2016-04-25 08:57:13.647' union all
select 2138,'2016-04-25 08:58:48.437',99,'2016-04-25 08:59:58.480' union all
select 2140,'2016-04-25 08:59:58.580',105,'2016-04-25 09:00:19.157' union all
select 2142,'2016-04-25 09:00:33.620',99,'2016-04-25 09:00:40.550' union all
select 2172,'2016-04-25 09:15:52.783',107,'2016-04-25 09:16:52.790' union all
select 2212,'2016-04-25 09:33:26.413',70,'2016-04-25 09:34:36.667' union all
select 2907,'2016-04-25 18:53:35.943',7,'2016-04-25 18:54:02.210' union all
select 2908,'2016-04-25 18:53:43.920',105,'2016-04-25 18:54:08.087' union all
select 2909,'2016-04-25 18:53:45.137',107,'2016-04-25 18:54:16.847' union all
select 2910,'2016-04-25 18:54:05.267',112,'2016-04-25 18:54:12.090' union all
select 2911,'2016-04-25 18:54:29.120',128,'2016-04-25 18:55:53.053' union all
select 2912,'2016-04-25 18:54:36.293',99,'2016-04-25 18:54:39.620' union all
select 2920,'2016-04-25 18:58:47.703',2,'2016-04-25 18:59:18.027' union all
select 2979,'2016-04-26 08:55:55.280',105,'2016-04-26 08:56:31.597' union all
select 2980,'2016-04-26 08:55:57.617',2,'2016-04-26 08:56:05.287' union all
select 2981,'2016-04-26 08:56:26.690',22,'2016-04-26 08:56:32.857' union all
select 2982,'2016-04-26 08:56:37.260',7,'2016-04-26 08:56:40.077' union all
select 2983,'2016-04-26 08:57:06.440',99,'2016-04-26 08:57:08.620' union all
select 2985,'2016-04-26 08:57:14.323',128,'2016-04-26 08:57:25.350' union all
select 2991,'2016-04-26 09:02:41.207',1,'2016-04-26 09:03:00.243' union all
select 2993,'2016-04-26 09:04:08.927',112,'2016-04-26 09:04:16.037' union all
select 3061,'2016-04-26 13:53:55.430',99,'2016-04-26 13:54:42.450' union all
select 3295,'2016-04-26 19:12:12.040',112,'2016-04-26 19:12:19.023' union all
select 3299,'2016-04-26 19:14:09.003',105,'2016-04-26 19:14:56.330' union all
select 3305,'2016-04-26 19:15:44.793',128,'2016-04-26 19:15:48.253' union all
select 3308,'2016-04-26 19:17:56.480',2,'2016-04-26 19:18:17.387' union all
select 3309,'2016-04-26 19:18:02.833',7,'2016-04-26 19:18:06.280' union all
select 3310,'2016-04-26 19:18:48.040',99,'2016-04-26 19:18:51.730' union all
select 3330,'2016-04-26 19:28:03.630',7,'2016-04-26 19:28:30.093' union all
select 3344,'2016-04-26 19:40:34.237',22,'2016-04-26 19:40:40.253' union all
select 3396,'2016-04-27 08:37:53.213',112,'2016-04-27 08:39:04.630' union all
select 3403,'2016-04-27 08:46:36.530',105,'2016-04-27 08:47:15.960' union all
select 3408,'2016-04-27 08:53:44.273',2,'2016-04-27 08:53:53.480'
Go
DECLARE @StartDate DATE='2016-10-01'--每月1号由程序控制传参

DECLARE @Sql NVARCHAR(max)='',@EndDate DATE=DATEADD(mm,1,@StartDate),@DayQty TINYINT
SET @DayQty=DATEDIFF(dd,@StartDate,@EndDate)
WHILE @StartDate<@EndDate
	SELECT @Sql=@Sql+',['+DATENAME(dd,@StartDate)+'号]=MAX(CASE WHEN DATENAME(dd,DDate)='+DATENAME(dd,@StartDate)+' THEN 显示 ELSE N''无打卡'' END)',@StartDate=DATEADD(dd,1,@StartDate)
	
SET @Sql=';With CTEA
as
(SELECT  [perid] ,
        DDate ,
        CASE WHEN MinTime <= ''09:00''
                  AND MaxTime >= ''18:00'' THEN ''正常''
             ELSE CASE WHEN MinTime <= ''09:00'' THEN ''''
                       WHEN MinTime BETWEEN ''09:00'' AND ''17:59'' THEN N''迟到''
                       WHEN MinTime IS NULL THEN ''缺打''
                  END + CASE WHEN MaxTime >= ''18:00'' THEN ''''
                             WHEN MaxTime IS NULL THEN ''缺打''
                        END
        END AS 显示		
		,(CASE WHEN MinTime IS NULL OR MaxTime IS NULL THEN 1 ELSE 0 END) AS [缺打天数]
		,(CASE WHEN MinTime BETWEEN ''09:00'' AND ''17:59'' THEN 1 ELSE 0 END) AS [迟到天数]
FROM    ( SELECT    [perid] ,
                    CAST(addtime AS DATE) AS DDate ,
                    CASE WHEN CONVERT(VARCHAR(5), MIN(addtime), 8) < ''18:00''
                         THEN CONVERT(VARCHAR(5), MIN(addtime), 8)
                    END AS MinTime ,
                    CASE WHEN CONVERT(VARCHAR(5), MAX(addtime), 8) >= ''18:00''
                         THEN CONVERT(VARCHAR(5), MAX(addtime), 8)
                    END AS MaxTime
          FROM      #T
		  WHERE addtime >= '''+CONVERT(VARCHAR(10),@StartDate,120)+''' AND addtime<'''+CONVERT(VARCHAR(10),@EndDate,120)+'''
          GROUP BY  [perid] ,
                    CAST(addtime AS DATE)
        ) AS T
)
SELECT [perid]
'+@Sql+'
,COUNT(*) AS 出勤天数
,SUM([缺打天数]) AS [缺打天数]
,SUM([迟到天数]) AS [迟到天数]
,'+RTRIM(@DayQty)+'-COUNT(*) AS [无打卡天数]
FROM CTEA
GROUP BY [perid]'
--PRINT @Sql

EXEC(@Sql)
gongjie416 2016-09-29
  • 打赏
  • 举报
 回复

感觉这个时间没用到，我传10月份的，照理说没数据，但是都显示正常
中国风 2016-09-29
  • 打赏
  • 举报
 回复
结果不一样，检查自己的语句是否改反了 你用#24方法直接执行，看看结果
gongjie416 2016-09-29
  • 打赏
  • 举报
 回复

图片好象传不了，抱歉下，这两天刮台风，停电了，上不了
gongjie416 2016-09-29
  • 打赏
  • 举报
 回复
#24 我求出来的结果，跟你的好象是相反的

其中6，7号是没有数据的，但是显示出来的是正常
gongjie416 2016-09-27
  • 打赏
  • 举报
 回复
自己顶一下，有没有比较善长SQL的帮下忙
中国风 2016-09-27
  • 打赏
  • 举报
 回复 
use Tempdb

go

--> --> 中国风(Roy)生成測試數據

 

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

	drop table #T

Go

Create table #T([id] int,[addtime] Datetime,[perid] int,[signtime] Datetime)

Insert #T

select 1,'2016-04-20 08:57:17.880',2,'2016-04-20 08:57:44.263' union all

select 3,'2016-04-20 08:58:38.930',7,'2016-04-20 08:58:41.447' union all

select 4,'2016-04-20 08:58:42.693',22,'2016-04-20 08:59:11.323' union all

select 6,'2016-04-20 08:59:42.150',107,'2016-04-20 08:59:48.460' union all

select 9,'2016-04-20 09:00:25.043',1,'2016-04-20 09:00:39.357' union all

select 10,'2016-04-20 09:00:30.393',99,'2016-04-20 09:00:33.190' union all

select 64,'2016-04-20 09:57:10.980',1,'2016-04-20 09:57:21.630' union all

select 68,'2016-04-20 09:59:44.427',105,'2016-04-20 10:01:24.300' union all

select 354,'2016-04-20 18:15:07.743',2,'2016-04-20 18:16:10.397' union all

select 355,'2016-04-20 18:16:02.583',99,'2016-04-20 18:16:06.193' union all

select 356,'2016-04-20 18:16:37.543',7,'2016-04-20 18:16:40.897' union all

select 357,'2016-04-20 18:17:02.787',105,'2016-04-20 18:18:06.920' union all

select 358,'2016-04-20 18:17:08.937',107,'2016-04-20 18:18:08.790' union all

select 360,'2016-04-20 18:17:22.233',22,'2016-04-20 18:17:24.850' union all

select 388,'2016-04-20 19:21:17.770',34,'2016-04-20 19:21:33.587' union all

select 456,'2016-04-21 08:53:12.970',2,'2016-04-21 08:53:54.237' union all

select 458,'2016-04-21 08:56:52.740',107,'2016-04-21 08:56:57.783' union all

select 460,'2016-04-21 08:58:39.583',7,'2016-04-21 08:58:44.730' union all

select 462,'2016-04-21 08:59:06.070',99,'2016-04-21 08:59:08.453' union all

select 463,'2016-04-21 08:59:29.480',22,'2016-04-21 08:59:31.050' union all

select 480,'2016-04-21 09:34:30.993',105,'2016-04-21 09:34:34.537' union all

select 490,'2016-04-21 09:42:46.550',111,'2016-04-21 09:42:54.817' union all

select 491,'2016-04-21 09:43:16.053',112,'2016-04-21 09:43:19.010' union all

select 514,'2016-04-21 09:56:33.540',105,'2016-04-21 09:57:09.187' union all

select 517,'2016-04-21 09:57:24.080',105,'2016-04-21 09:57:26.227' union all

select 521,'2016-04-21 10:03:13.820',105,'2016-04-21 10:03:16.523' union all

select 571,'2016-04-21 11:39:04.620',1,'2016-04-21 11:39:37.060' union all

select 670,'2016-04-21 15:53:55.020',34,'2016-04-21 15:54:06.280' union all

select 723,'2016-04-21 19:14:08.137',2,'2016-04-21 19:14:17.187' union all

select 724,'2016-04-21 19:14:32.587',107,'2016-04-21 19:14:42.650' union all

select 725,'2016-04-21 19:14:38.420',7,'2016-04-21 19:14:41.157' union all

select 726,'2016-04-21 19:15:08.170',105,'2016-04-21 19:15:10.827' union all

select 728,'2016-04-21 19:15:34.587',99,'2016-04-21 19:15:37.133' union all

select 778,'2016-04-22 08:12:48.170',112,'2016-04-22 08:13:03.770' union all

select 813,'2016-04-22 08:57:30.573',2,'2016-04-22 08:57:55.283' union all

select 816,'2016-04-22 09:01:31.570',22,'2016-04-22 09:01:40.193' union all

select 817,'2016-04-22 09:01:39.253',7,'2016-04-22 09:02:07.550' union all

select 829,'2016-04-22 09:09:14.570',105,'2016-04-22 09:10:13.327' union all

select 836,'2016-04-22 09:18:39.307',99,'2016-04-22 09:18:43.273' union all

select 1239,'2016-04-22 17:02:29.543',7,'2016-04-22 17:02:32.590' union all

select 1304,'2016-04-22 19:01:30.980',112,'2016-04-22 19:01:40.497' union all

select 1311,'2016-04-22 19:08:01.357',105,'2016-04-22 19:08:05.527' union all

select 1312,'2016-04-22 19:08:42.620',105,'2016-04-22 19:08:47.760' union all

select 1314,'2016-04-22 19:09:40.507',7,'2016-04-22 19:09:44.367' union all

select 1315,'2016-04-22 19:10:45.520',99,'2016-04-22 19:10:50.000' union all

select 1317,'2016-04-22 19:11:21.040',22,'2016-04-22 19:11:26.097' union all

select 1324,'2016-04-22 19:20:05.173',2,'2016-04-22 19:20:32.737' union all

select 1811,'2016-04-24 14:09:07.737',107,'2016-04-24 14:09:15.130' union all

select 1824,'2016-04-24 14:23:03.697',7,'2016-04-24 14:23:39.540' union all

select 1826,'2016-04-24 14:23:26.257',2,'2016-04-24 14:23:33.120' union all

select 1829,'2016-04-24 14:26:30.553',22,'2016-04-24 14:26:41.117' union all

select 1832,'2016-04-24 14:28:10.387',99,'2016-04-24 14:28:12.457' union all

select 1840,'2016-04-24 14:33:14.160',112,'2016-04-24 14:33:25.513' union all

select 1845,'2016-04-24 14:35:38.290',112,'2016-04-24 14:35:42.120' union all

select 1849,'2016-04-24 14:36:53.400',105,'2016-04-24 14:37:01.133' union all

select 2010,'2016-04-24 16:59:54.453',7,'2016-04-24 17:00:19.370' union all

select 2011,'2016-04-24 17:01:03.860',2,'2016-04-24 17:01:14.830' union all

select 2012,'2016-04-24 17:06:04.140',22,'2016-04-24 17:06:25.920' union all

select 2013,'2016-04-24 17:06:30.770',99,'2016-04-24 17:06:35.157' union all

select 2018,'2016-04-24 17:09:19.590',99,'2016-04-24 17:09:26.903' union all

select 2024,'2016-04-24 17:14:19.757',112,'2016-04-24 17:14:23.527' union all

select 2027,'2016-04-24 17:20:55.700',107,'2016-04-24 17:21:00.160' union all

select 2048,'2016-04-24 17:51:01.910',105,'2016-04-24 17:51:44.887' union all

select 2128,'2016-04-25 08:54:52.230',112,'2016-04-25 08:55:45.013' union all

select 2129,'2016-04-25 08:55:55.903',2,'2016-04-25 08:56:13.177' union all

select 2131,'2016-04-25 08:56:43.410',7,'2016-04-25 08:56:48.930' union all

select 2132,'2016-04-25 08:56:54.260',22,'2016-04-25 08:56:55.947' union all

select 2133,'2016-04-25 08:57:04.767',22,'2016-04-25 08:57:13.647' union all

select 2138,'2016-04-25 08:58:48.437',99,'2016-04-25 08:59:58.480' union all

select 2140,'2016-04-25 08:59:58.580',105,'2016-04-25 09:00:19.157' union all

select 2142,'2016-04-25 09:00:33.620',99,'2016-04-25 09:00:40.550' union all

select 2172,'2016-04-25 09:15:52.783',107,'2016-04-25 09:16:52.790' union all

select 2212,'2016-04-25 09:33:26.413',70,'2016-04-25 09:34:36.667' union all

select 2907,'2016-04-25 18:53:35.943',7,'2016-04-25 18:54:02.210' union all

select 2908,'2016-04-25 18:53:43.920',105,'2016-04-25 18:54:08.087' union all

select 2909,'2016-04-25 18:53:45.137',107,'2016-04-25 18:54:16.847' union all

select 2910,'2016-04-25 18:54:05.267',112,'2016-04-25 18:54:12.090' union all

select 2911,'2016-04-25 18:54:29.120',128,'2016-04-25 18:55:53.053' union all

select 2912,'2016-04-25 18:54:36.293',99,'2016-04-25 18:54:39.620' union all

select 2920,'2016-04-25 18:58:47.703',2,'2016-04-25 18:59:18.027' union all

select 2979,'2016-04-26 08:55:55.280',105,'2016-04-26 08:56:31.597' union all

select 2980,'2016-04-26 08:55:57.617',2,'2016-04-26 08:56:05.287' union all

select 2981,'2016-04-26 08:56:26.690',22,'2016-04-26 08:56:32.857' union all

select 2982,'2016-04-26 08:56:37.260',7,'2016-04-26 08:56:40.077' union all

select 2983,'2016-04-26 08:57:06.440',99,'2016-04-26 08:57:08.620' union all

select 2985,'2016-04-26 08:57:14.323',128,'2016-04-26 08:57:25.350' union all

select 2991,'2016-04-26 09:02:41.207',1,'2016-04-26 09:03:00.243' union all

select 2993,'2016-04-26 09:04:08.927',112,'2016-04-26 09:04:16.037' union all

select 3061,'2016-04-26 13:53:55.430',99,'2016-04-26 13:54:42.450' union all

select 3295,'2016-04-26 19:12:12.040',112,'2016-04-26 19:12:19.023' union all

select 3299,'2016-04-26 19:14:09.003',105,'2016-04-26 19:14:56.330' union all

select 3305,'2016-04-26 19:15:44.793',128,'2016-04-26 19:15:48.253' union all

select 3308,'2016-04-26 19:17:56.480',2,'2016-04-26 19:18:17.387' union all

select 3309,'2016-04-26 19:18:02.833',7,'2016-04-26 19:18:06.280' union all

select 3310,'2016-04-26 19:18:48.040',99,'2016-04-26 19:18:51.730' union all

select 3330,'2016-04-26 19:28:03.630',7,'2016-04-26 19:28:30.093' union all

select 3344,'2016-04-26 19:40:34.237',22,'2016-04-26 19:40:40.253' union all

select 3396,'2016-04-27 08:37:53.213',112,'2016-04-27 08:39:04.630' union all

select 3403,'2016-04-27 08:46:36.530',105,'2016-04-27 08:47:15.960' union all

select 3408,'2016-04-27 08:53:44.273',2,'2016-04-27 08:53:53.480'

Go

DECLARE @StartDate DATE='2016-04-01'--每月1号由程序控制传参



DECLARE @Sql NVARCHAR(max)='',@EndDate DATE=DATEADD(mm,1,@StartDate),@DayQty TINYINT

SET @DayQty=DATEDIFF(dd,@StartDate,@EndDate)

WHILE @StartDate<@EndDate

	SELECT @Sql=@Sql+',['+DATENAME(dd,@StartDate)+'号]=MAX(CASE WHEN DATENAME(dd,DDate)='+DATENAME(dd,@StartDate)+' THEN 显示 ELSE N''无打卡'' END)',@StartDate=DATEADD(dd,1,@StartDate)

	

SET @Sql=';With CTEA

as

(SELECT  [perid] ,

        DDate ,

        CASE WHEN MinTime <= ''09:00''

                  AND MaxTime >= ''18:00'' THEN ''正常''

             ELSE CASE WHEN MinTime <= ''09:00'' THEN ''''

                       WHEN MinTime BETWEEN ''09:00'' AND ''17:59'' THEN N''迟到''

                       WHEN MinTime IS NULL THEN ''缺打''

                  END + CASE WHEN MaxTime >= ''18:00'' THEN ''''

                             WHEN MaxTime IS NULL THEN ''缺打''

                        END

        END AS 显示		

		,(CASE WHEN MinTime IS NULL OR MaxTime IS NULL THEN 1 ELSE 0 END) AS [缺打天数]

		,(CASE WHEN MinTime BETWEEN ''09:00'' AND ''17:59'' THEN 1 ELSE 0 END) AS [迟到天数]

FROM    ( SELECT    [perid] ,

                    CAST(addtime AS DATE) AS DDate ,

                    CASE WHEN CONVERT(VARCHAR(5), MIN(addtime), 8) < ''18:00''

                         THEN CONVERT(VARCHAR(5), MIN(addtime), 8)

                    END AS MinTime ,

                    CASE WHEN CONVERT(VARCHAR(5), MAX(addtime), 8) >= ''18:00''

                         THEN CONVERT(VARCHAR(5), MAX(addtime), 8)

                    END AS MaxTime

          FROM      #T

          GROUP BY  [perid] ,

                    CAST(addtime AS DATE)

        ) AS T

)

SELECT [perid]

'+@Sql+'

,COUNT(*) AS 出勤天数

,SUM([缺打天数]) AS [缺打天数]

,SUM([迟到天数]) AS [迟到天数]

,'+RTRIM(@DayQty)+'-COUNT(*) AS [无打卡天数]

FROM CTEA

GROUP BY [perid]'

--PRINT @Sql



EXEC(@Sql)

中国风 2016-09-27
  • 打赏
  • 举报
 回复
#17漏COPY了CTE一段，已更正，你用以上方法测测
gongjie416 2016-09-27
  • 打赏
  • 举报
 回复
测试数据：id addtime perid signtime 1 2016-04-20 08:57:17.880 2 2016-04-20 08:57:44.263 3 2016-04-20 08:58:38.930 7 2016-04-20 08:58:41.447 4 2016-04-20 08:58:42.693 22 2016-04-20 08:59:11.323 6 2016-04-20 08:59:42.150 107 2016-04-20 08:59:48.460 9 2016-04-20 09:00:25.043 1 2016-04-20 09:00:39.357 10 2016-04-20 09:00:30.393 99 2016-04-20 09:00:33.190 64 2016-04-20 09:57:10.980 1 2016-04-20 09:57:21.630 68 2016-04-20 09:59:44.427 105 2016-04-20 10:01:24.300 354 2016-04-20 18:15:07.743 2 2016-04-20 18:16:10.397 355 2016-04-20 18:16:02.583 99 2016-04-20 18:16:06.193 356 2016-04-20 18:16:37.543 7 2016-04-20 18:16:40.897 357 2016-04-20 18:17:02.787 105 2016-04-20 18:18:06.920 358 2016-04-20 18:17:08.937 107 2016-04-20 18:18:08.790 360 2016-04-20 18:17:22.233 22 2016-04-20 18:17:24.850 388 2016-04-20 19:21:17.770 34 2016-04-20 19:21:33.587 456 2016-04-21 08:53:12.970 2 2016-04-21 08:53:54.237 458 2016-04-21 08:56:52.740 107 2016-04-21 08:56:57.783 460 2016-04-21 08:58:39.583 7 2016-04-21 08:58:44.730 462 2016-04-21 08:59:06.070 99 2016-04-21 08:59:08.453 463 2016-04-21 08:59:29.480 22 2016-04-21 08:59:31.050 480 2016-04-21 09:34:30.993 105 2016-04-21 09:34:34.537 490 2016-04-21 09:42:46.550 111 2016-04-21 09:42:54.817 491 2016-04-21 09:43:16.053 112 2016-04-21 09:43:19.010 514 2016-04-21 09:56:33.540 105 2016-04-21 09:57:09.187 517 2016-04-21 09:57:24.080 105 2016-04-21 09:57:26.227 521 2016-04-21 10:03:13.820 105 2016-04-21 10:03:16.523 571 2016-04-21 11:39:04.620 1 2016-04-21 11:39:37.060 670 2016-04-21 15:53:55.020 34 2016-04-21 15:54:06.280 723 2016-04-21 19:14:08.137 2 2016-04-21 19:14:17.187 724 2016-04-21 19:14:32.587 107 2016-04-21 19:14:42.650 725 2016-04-21 19:14:38.420 7 2016-04-21 19:14:41.157 726 2016-04-21 19:15:08.170 105 2016-04-21 19:15:10.827 728 2016-04-21 19:15:34.587 99 2016-04-21 19:15:37.133 778 2016-04-22 08:12:48.170 112 2016-04-22 08:13:03.770 813 2016-04-22 08:57:30.573 2 2016-04-22 08:57:55.283 816 2016-04-22 09:01:31.570 22 2016-04-22 09:01:40.193 817 2016-04-22 09:01:39.253 7 2016-04-22 09:02:07.550 829 2016-04-22 09:09:14.570 105 2016-04-22 09:10:13.327 836 2016-04-22 09:18:39.307 99 2016-04-22 09:18:43.273 1239 2016-04-22 17:02:29.543 7 2016-04-22 17:02:32.590 1304 2016-04-22 19:01:30.980 112 2016-04-22 19:01:40.497 1311 2016-04-22 19:08:01.357 105 2016-04-22 19:08:05.527 1312 2016-04-22 19:08:42.620 105 2016-04-22 19:08:47.760 1314 2016-04-22 19:09:40.507 7 2016-04-22 19:09:44.367 1315 2016-04-22 19:10:45.520 99 2016-04-22 19:10:50.000 1317 2016-04-22 19:11:21.040 22 2016-04-22 19:11:26.097 1324 2016-04-22 19:20:05.173 2 2016-04-22 19:20:32.737 1811 2016-04-24 14:09:07.737 107 2016-04-24 14:09:15.130 1824 2016-04-24 14:23:03.697 7 2016-04-24 14:23:39.540 1826 2016-04-24 14:23:26.257 2 2016-04-24 14:23:33.120 1829 2016-04-24 14:26:30.553 22 2016-04-24 14:26:41.117 1832 2016-04-24 14:28:10.387 99 2016-04-24 14:28:12.457 1840 2016-04-24 14:33:14.160 112 2016-04-24 14:33:25.513 1845 2016-04-24 14:35:38.290 112 2016-04-24 14:35:42.120 1849 2016-04-24 14:36:53.400 105 2016-04-24 14:37:01.133 2010 2016-04-24 16:59:54.453 7 2016-04-24 17:00:19.370 2011 2016-04-24 17:01:03.860 2 2016-04-24 17:01:14.830 2012 2016-04-24 17:06:04.140 22 2016-04-24 17:06:25.920 2013 2016-04-24 17:06:30.770 99 2016-04-24 17:06:35.157 2018 2016-04-24 17:09:19.590 99 2016-04-24 17:09:26.903 2024 2016-04-24 17:14:19.757 112 2016-04-24 17:14:23.527 2027 2016-04-24 17:20:55.700 107 2016-04-24 17:21:00.160 2048 2016-04-24 17:51:01.910 105 2016-04-24 17:51:44.887 2128 2016-04-25 08:54:52.230 112 2016-04-25 08:55:45.013 2129 2016-04-25 08:55:55.903 2 2016-04-25 08:56:13.177 2131 2016-04-25 08:56:43.410 7 2016-04-25 08:56:48.930 2132 2016-04-25 08:56:54.260 22 2016-04-25 08:56:55.947 2133 2016-04-25 08:57:04.767 22 2016-04-25 08:57:13.647 2138 2016-04-25 08:58:48.437 99 2016-04-25 08:59:58.480 2140 2016-04-25 08:59:58.580 105 2016-04-25 09:00:19.157 2142 2016-04-25 09:00:33.620 99 2016-04-25 09:00:40.550 2172 2016-04-25 09:15:52.783 107 2016-04-25 09:16:52.790 2212 2016-04-25 09:33:26.413 70 2016-04-25 09:34:36.667 2907 2016-04-25 18:53:35.943 7 2016-04-25 18:54:02.210 2908 2016-04-25 18:53:43.920 105 2016-04-25 18:54:08.087 2909 2016-04-25 18:53:45.137 107 2016-04-25 18:54:16.847 2910 2016-04-25 18:54:05.267 112 2016-04-25 18:54:12.090 2911 2016-04-25 18:54:29.120 128 2016-04-25 18:55:53.053 2912 2016-04-25 18:54:36.293 99 2016-04-25 18:54:39.620 2920 2016-04-25 18:58:47.703 2 2016-04-25 18:59:18.027 2979 2016-04-26 08:55:55.280 105 2016-04-26 08:56:31.597 2980 2016-04-26 08:55:57.617 2 2016-04-26 08:56:05.287 2981 2016-04-26 08:56:26.690 22 2016-04-26 08:56:32.857 2982 2016-04-26 08:56:37.260 7 2016-04-26 08:56:40.077 2983 2016-04-26 08:57:06.440 99 2016-04-26 08:57:08.620 2985 2016-04-26 08:57:14.323 128 2016-04-26 08:57:25.350 2991 2016-04-26 09:02:41.207 1 2016-04-26 09:03:00.243 2993 2016-04-26 09:04:08.927 112 2016-04-26 09:04:16.037 3061 2016-04-26 13:53:55.430 99 2016-04-26 13:54:42.450 3295 2016-04-26 19:12:12.040 112 2016-04-26 19:12:19.023 3299 2016-04-26 19:14:09.003 105 2016-04-26 19:14:56.330 3305 2016-04-26 19:15:44.793 128 2016-04-26 19:15:48.253 3308 2016-04-26 19:17:56.480 2 2016-04-26 19:18:17.387 3309 2016-04-26 19:18:02.833 7 2016-04-26 19:18:06.280 3310 2016-04-26 19:18:48.040 99 2016-04-26 19:18:51.730 3330 2016-04-26 19:28:03.630 7 2016-04-26 19:28:30.093 3344 2016-04-26 19:40:34.237 22 2016-04-26 19:40:40.253 3396 2016-04-27 08:37:53.213 112 2016-04-27 08:39:04.630 3403 2016-04-27 08:46:36.530 105 2016-04-27 08:47:15.960 3408 2016-04-27 08:53:44.273 2 2016-04-27 08:53:53.480
gongjie416 2016-09-27
  • 打赏
  • 举报
 回复
这是数据库的数据，perid为人员id,deptid为部门id可以不管，addtime为打卡日期，signtime为打卡时间
zbdzjx 2016-09-27
  • 打赏
  • 举报
 回复
一个稍麻烦点的方法： 用一个临时表或正式表来存放结果，一共36列，包括：perid、1~31号、出勤天数、迟到天数、无打卡天数、缺打天数。 先通过考勤明细表取出全部的perid，增加到这个表中。 再循环perid，每个perid再循环1号到31号（这个要看当月实际天数），从考勤明细表中取出打卡数据，判断得出结果，写到相应的栏位中。 最后，每个perid再从1号判断到31号（这个要看当月实际天数），计算出勤天数、迟到天数、无打卡天数、缺打天数，写到相应的栏位中。
中国风 2016-09-27
  • 打赏
  • 举报
 回复
还有问题直接贴出自己的模拟测试数据方便给你测测
中国风 2016-09-27
  • 打赏
  • 举报
 回复
引用 16 楼 gongjie416 的回复:
谢谢楼上的朋友了，跟要求不一样，还是谢了，结贴了，应该是没人写得出来了，
写个方法#17你，参照以上方法去实现
中国风 2016-09-27
  • 打赏
  • 举报
 回复 
DECLARE @StartDate DATE='2016-04-01'--每月1号由程序控制传参



DECLARE @Sql NVARCHAR(max)='',@EndDate DATE=DATEADD(mm,1,@StartDate),@DayQty TINYINT

SET @DayQty=DATEDIFF(dd,@StartDate,@EndDate)

WHILE @StartDate<@EndDate

	SELECT @Sql=@Sql+',['+DATENAME(dd,@StartDate)+'号]=MAX(CASE WHEN DATENAME(dd,DDate)='+DATENAME(dd,@StartDate)+' THEN 显示 ELSE N''无打卡'' END)',@StartDate=DATEADD(dd,1,@StartDate)

	

SET @Sql=';With CTEA

as

(SELECT  [perid] ,

        DDate ,

        CASE WHEN MinTime <= ''09:00''

                  AND MaxTime >= ''18:00'' THEN ''正常''

             ELSE CASE WHEN MinTime <= ''09:00'' THEN ''''

                       WHEN MinTime BETWEEN ''09:00'' AND ''17:59'' THEN N''迟到''

                       WHEN MinTime IS NULL THEN ''缺打''

                  END + CASE WHEN MaxTime >= ''18:00'' THEN ''''

                             WHEN MaxTime IS NULL THEN ''缺打''

                        END

        END AS 显示		

		,(CASE WHEN MinTime IS NULL OR MaxTime IS NULL THEN 1 ELSE 0 END) AS [缺打天数]

		,(CASE WHEN MinTime BETWEEN ''09:00'' AND ''17:59'' THEN 1 ELSE 0 END) AS [迟到天数]

FROM    ( SELECT    [perid] ,

                    CAST(addtime AS DATE) AS DDate ,

                    CASE WHEN CONVERT(VARCHAR(5), MIN(addtime), 8) < ''18:00''

                         THEN CONVERT(VARCHAR(5), MIN(addtime), 8)

                    END AS MinTime ,

                    CASE WHEN CONVERT(VARCHAR(5), MAX(addtime), 8) >= ''18:00''

                         THEN CONVERT(VARCHAR(5), MAX(addtime), 8)

                    END AS MaxTime

          FROM      TabName

          GROUP BY  [perid] ,

                    CAST(addtime AS DATE)

        ) AS T

)

SELECT [perid]

'+@Sql+'

,COUNT(*) AS 出勤天数

,SUM([缺打天数]) AS [缺打天数]

,SUM([迟到天数]) AS [迟到天数]

,'+RTRIM(@DayQty)+'-COUNT(*) AS [无打卡天数]

FROM CTEA

GROUP BY [perid]'

PRINT @Sql



EXEC(@Sql)
gongjie416 2016-09-27
  • 打赏
  • 举报
 回复
谢谢楼上的朋友了，跟要求不一样，还是谢了，结贴了，应该是没人写得出来了，
云中客 2016-09-27
  • 打赏
  • 举报
 回复
select perid,days,yy=(case when sdk<=cast(CONVERT(varchar(10), sdk, 120) + ' 09:00:00' as datetime) and xdk>=cast(CONVERT(varchar(10), xdk, 120) + ' 18:00:00' as datetime) then '正常' when sdk is not null and sdk>cast(CONVERT(varchar(10), sdk, 120) + ' 09:00:00' as datetime) then '迟到' when xdk is not null and xdk<cast(CONVERT(varchar(10), xdk, 120) + ' 18:00:00' as datetime) then '早退' end) from (select perid,days=day(addtime),sdk=min(addtime),xdk=max(addtime),qdk=min(signtime),qdk2=max(signtime) from ttt group by perid,day(addtime)) a
中国风 2016-09-27
  • 打赏
  • 举报
 回复
你没理解 规则就是打卡的有效时间 比如 早上没打卡17：00打卡，这是有效那计算为上班迟到还是提前下班早退？通常会在上班或下班时间前后30分钟内计算为有效打卡 没规则，那是计算不出来的
gongjie416 2016-09-27
  • 打赏
  • 举报
 回复
超过9点的都算迟到，天数这一块可以根据传进来的月份，获取，其时差的就是后面的各种状态统计
中国风 2016-09-27
  • 打赏
  • 举报
 回复
要正确显示，应该要动态去处理，每个月的天数不同，显示结果不同
加载更多回复（10）
Python课程设计 课设 手写数字识别卷积神经网络源码+文档说明.zip
高分设计源码，详情请查看资源内容中使用说明 高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明高分设计源码，详情请查看资源内容中使用说明
SpringBoot2.0快速开发框架权限.rar
SpringBoot2.0快速开发框架权限.rarSpringBoot2.0快速开发框架权限.rarSpringBoot2.0快速开发框架权限.rar
大语言模型的微调和推理baichuan7B, chatglm2-6B, Qwen-7B-chat源码.zip
详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；详情请查看资源内容中使用说明；
基于Qt与STM32平台开发的汽车车机系统上位机
基于Qt开发的汽车车机系统上位机 & 常见类型汽车传感器信号模拟发生器 任务和要求： 任务： 根据发动机测控系统信号需求，设计一套发动机信号模拟器人机交互系统，能够根据需要向下位机输出控制信号，使其输出发动机测控系统需要的传感器模拟信号，给发动机测控系统的开发提供方便。 要求： 1.设计应包含上位机与下位机的交互程序及人机交互界面的设计，与下位机设计相结合，使其能够实现全部类型发动机传感器信号的模拟输出及显示。 2.设计中需要采用模块化开发程序。 3.所设计的人机交互界面简洁合理。 4.应考虑所设计系统的实用性。 具体工作内容: 1.根据设计目标，查阅相关设计标准和设计方法资料，对发动机信号模拟器设计中的关键工程原理和工程方法进行提炼，并围绕关键问题进行国内外设计现状调研，开展分析、评价与总结，确定主要研究内容，制定设计技术路线，制定设计计划(周进度)，撰写开题报告，并进行开题答辩，开题报告参考文献应不少于15篇(其中外文文献不少于 4 篇，近五年文献不少于三分之一)。 2.根据设计要求和技术指标，进行满足功能原理需求的多方案拟定，考虑安全、 标准等多因素进行技术性与经济性评价
实验-三、数据库安全性(目的、要求和模板).doc
实验-三、数据库安全性(目的、要求和模板).doc
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章