求一个考勤未打卡的存儲過程

[BarryW]

有一个数据库的表(TF_KQ)的內容如下:
YG_NO(为工号) TRS_DD(为打卡日期);下为例子数据;
YG_NO TRS_DD
------------------------------------------------------------------
A002 2004-10-08 07:48:00.000
A002 2004-10-08 11:31:00.000
A002 2004-10-08 12:04:00.000
A002 2004-10-08 12:22:00.000
A002 2004-10-08 12:45:00.000
A002 2004-10-08 17:33:00.000
A003 2004-10-08 08:24:00.000
A003 2004-10-08 11:44:00.000
A003 2004-10-08 12:19:00.000
A005 2004-10-15 08:26:00.000
A005 2004-10-15 13:19:00.000
A005 2004-10-18 08:11:00.000
A005 2004-10-20 08:21:00.000
..........
规定是:
1,上午上班刷卡有效时间为：早上7：00起刷卡有效,到11:点钟为上班;
2、中午两次刷卡时间间隔为30分钟有效，如中间只有1次刷卡记录的，12：00以前
算上午下班刷卡，12：01分后刷卡算下午上班刷卡。但规定是从11:00以后为中午下班时间;
而从12:01-17点起就是下午上班时间算
3、下午下班刷卡有效时间为：下午19：00前刷卡有效。開始刷卡有效时间为17:00

要做一个如下报表的存储过程(按日期):

2004年10月09日~14日未打卡记录(式样)
1-上午上班,2-上午下班,3-下午上班,4-下午下班

------------------------------------------------------------------
|员工编号| 09 | 10 | 11 | 12 | 13 | 14 |
------------------------------------------------------------------
| A002 | 3 | | 2、3、4 | | | |
------------------------------------------------------------------
| A006 | | 2、3、4 | | | 2、3、4| |
------------------------------------------------------------------
| A025 | |1、2、3 | | | | |
------------------------------------------------------------------
| C045 | | | | 1、4 | | |
------------------------------------------------------------------
| A003 | | | 4 | 2、3 | | 4 |
------------------------------------------------------------------
| A004 | | 2 | | | | |
------------------------------------------------------------------
| A008 | | | 1 | | | |
------------------------------------------------------------------
.......

这个应如何写?
TKS!

[victorycyz]

分开来看，这里边包括： 行列转换、日期函数、汇总与拼接字串。这样的话，楼主你先说你哪个环节不会做？

[Andy__Huang]

你的表這樣設計給程序的編寫帶來很大的問題﹐一個考勤系統里的時間是個大問題﹐這樣設計一個每天都有几條記錄﹐如果上百人一天的數據就就几千條﹐一個月下來就有几萬條數據了﹐到時候按考勤計算工資又是個問題。

所以我覺得你在表的設計上要有改善﹗

[zhn0410]

占个位学习，并帮你顶一下。

[victorycyz]

hdhai9451(※★開拓者...准備去長安☆※) :

这个表作为打卡的原始记录，应该没有问题的。

[Andy__Huang]

victorycyz(中海) ( ) 信誉：114

我覺得這樣設計表有很大的問題﹐時間也不好控制﹐如果有時候加班﹐如果有時候調班更改上班時間﹐到時候給計算工資時﹐那你不是又重新修改存儲過程嗎﹖

[BarryW]

不算加班;只算正常的班級

這個報表"打卡的原始记录"
看只會看几天沒有打卡,只是一个查询报表与"給計算工資"关系不太大;

[zjcxc]

--示例

--示例数据
create table TF_KQ(YG_NO varchar(10),TRS_DD datetime)
insert TF_KQ select 'A002','2004-10-09 07:48:00.000'
union all select 'A002','2004-10-09 11:31:00.000'
union all select 'A002','2004-10-09 12:04:00.000'
union all select 'A002','2004-10-09 12:22:00.000'
union all select 'A002','2004-10-09 12:45:00.000'
union all select 'A002','2004-10-09 17:33:00.000'
union all select 'A003','2004-10-09 08:24:00.000'
union all select 'A003','2004-10-09 11:44:00.000'
union all select 'A003','2004-10-09 12:19:00.000'
union all select 'A005','2004-10-15 08:26:00.000'
union all select 'A005','2004-10-15 13:19:00.000'
union all select 'A005','2004-10-18 08:11:00.000'
union all select 'A005','2004-10-20 08:21:00.000'
go

--处理的存储过程
create proc p_qry
@dt_begin datetime,
@dt_end datetime
as
set nocount on

--规范日期
select @dt_begin=convert(char(10),@dt_begin,120)
,@dt_end=convert(char(10),@dt_end+1,120)

--查询
select distinct a.YG_NO,日期=convert(char(10),a.TRS_DD,103)
,班次=b.班次
into #t from TF_KQ a,(
select 班次=cast('1' as varchar(20)),开始时间='07:00',结束时间='11:00'
union all select '2','11:01','12:00'
union all select '3','12:01','17:00'
union all select '4','17:01','19:00'
)b where a.TRS_DD>=@dt_begin and a.TRS_DD<@dt_end
and convert(char(5),a.TRS_DD,108) between b.开始时间 and b.结束时间
order by YG_NO,日期,班次

declare @YG_NO varchar(10),@日期 varchar(10),@r varchar(50)
update #t set @r=case when @YG_NO=YG_NO and @日期=日期 then @r+','+班次 else 班次 end
,班次=@r,@YG_NO=YG_NO,@日期=日期

declare @s varchar(8000)
set @s=''
while @dt_begin<@dt_end
select @s=@s+',['+convert(char(2),@dt_begin,3)
+']=max(case 日期 when '''
+convert(char(10),@dt_begin,103)
+''' then 班次 else '''' end)'
,@dt_begin=@dt_begin+1
exec('select YG_NO'+@s+' from #t group by YG_NO')
go

--调用存储过程实现查询
exec p_qry '2004-10-9','2004-10-14'
go

--删除测试
drop table TF_KQ
drop proc p_qry

/*--测试结果
YG_NO 09 10 11 12 13 14
---------- --------- ------- ------- ------- ------- ------
A002 1,2,3,4
A003 1,2,3
--*/

[BarryW]

zjcxc,利害,佩服!
谢谢!