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

[BarryW]

http://community.csdn.net/Expert/topic/3502/3502544.xml?temp=.5544092
http://community.csdn.net/Expert/topic/3503/3503295.xml?temp=.6247827

有一个数据库的表(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 | | | |
------------------------------------------------------------------
.......

以下为邹建回复:

回复人： zjcxc(邹建) ( ) 信誉：465
--示例数据
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
declare @i int

--规范日期
select @dt_begin=convert(char(10),@dt_begin,120)
,@dt_end=convert(char(10),@dt_end+1,120)
,@i=datediff(day,@dt_begin,@dt_end)*4

--生成查询临时表
set rowcount @i
select id=identity(int,0,1),dt=cast('' as char(10))
into #tm from syscolumns a,syscolumns b
update #tm set dt=convert(varchar,@dt_begin+id,103)
set rowcount 0

--查询
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.结束时间

select a.YG_NO,日期=b.dt,班次=cast(c.班次 as varchar(50))
into #tc
from(select YG_NO from TF_KQ group by YG_NO)a,#tm b
,(select 班次='1' union all select '2' union all select '3' union all select '4')c
where not exists(select * from #t where YG_NO=a.YG_NO and 日期=b.dt and 班次=c.班次)
order by YG_NO,日期,班次

declare @YG_NO varchar(10),@日期 varchar(10),@r varchar(50)
update #tc 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 #tc 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
---------- --------- ------- ------- ------- ------- ------
A0021,2,3,41,2,3,41,2,3,41,2,3,41,2,3,4
A00341,2,3,41,2,3,41,2,3,41,2,3,41,2,3,4
A0051,2,3,41,2,3,41,2,3,41,2,3,41,2,3,41,2,3,4

--*/



但是客户提出在上面
"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'"
的2和3之间有如下条件要设定:
1.上午下班从十一点二十五分至十三点35之间;
2.当在这之间如是只打了一次的以十二点为界,十二
前打的就算是上午下班打的,就上午下班不出现异常;点之后打的就算下午上班打的,就下午上班不出
现异常;
3.如在这之间打了两次的间隔大于30分钟的;前一次打的就上上午下班打的,后一次就为下午上班打的,不出现异常;

4.如在这之间打了两次的间隔小于30分钟的;就算一次,算第一次打的有效,并以十二点为界,十二点之前打的就算是上午下班打的,十二点之后打的就算下午上班打的;

其它的条件都是一样;
大虾们来帮帮我,又急了,谢谢了!

[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-10 08:24:00.000'
union all select 'A003','2004-10-10 12:14:00.000'
union all select 'A003','2004-10-10 12:59: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
declare @i int

--规范日期
select @dt_begin=convert(char(10),@dt_begin,120)
,@dt_end=convert(char(10),@dt_end+1,120)
,@i=datediff(day,@dt_begin,@dt_end)*4

--生成查询临时表
set rowcount @i
select id=identity(int,0,1),dt=cast('' as char(10))
into #tm from syscolumns a,syscolumns b
update #tm set dt=convert(varchar,@dt_begin+id,103)
set rowcount 0

--查询
select distinct * into #t from
(
select distinct a.YG_NO,日期=convert(char(10),a.TRS_DD,103)
,班次=b.班次
from TF_KQ a,(
select 班次=cast('1' as varchar(20)),开始时间='07:00',结束时间='08:35'
union all select '2','11:25','12:00'
union all select '3','12:01','13:35'
union all select '4','17:25','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.结束时间
union all
select distinct a.YG_NO,日期=convert(char(10),a.TRS_DD,103)
,班次
from TF_KQ a ,(select '2' 班次 union all select '3') b
where a.TRS_DD>=@dt_begin and a.TRS_DD<@dt_end
and convert(char(5),a.TRS_DD,108) between '11:25' and '13:35'
and exists(select 1 from TF_KQ where YG_NO = a.YG_NO and convert(char(5),TRS_DD,108) between '11:25' and '13:35'
and datediff(minute,TRS_DD,a.TRS_DD) > 30 )
) aa

select a.YG_NO,日期=b.dt,班次=cast(c.班次 as varchar(50))
into #tc
from(select YG_NO from TF_KQ group by YG_NO)a,#tm b
,(select 班次='1' union all select '2' union all select '3' union all select '4')c
where not exists(select * from #t where YG_NO=a.YG_NO and 日期=b.dt and 班次=c.班次)
order by YG_NO,日期,班次

declare @YG_NO varchar(10),@日期 varchar(10),@r varchar(50)
update #tc 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 #tc 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
---------- --------- ------- ------- ------- ------- ------
A0021,2,3,41,2,3,41,2,3,41,2,3,41,2,3,4
A00341,2,3,41,2,3,41,2,3,41,2,3,41,2,3,4
A0051,2,3,41,2,3,41,2,3,41,2,3,41,2,3,41,2,3,4

--*/

[BarryW]

我想结了此,没有人来,我都不好结了

[xluzhong]

?????

[子陌红尘]

呵呵，过来蹭分

[talantlee]

頭昏