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

[BarryW]

zjcxc(邹建) 有在以上地址中幫我回了
http://community.csdn.net/Expert/topic/3502/3502544.xml?temp=.7190821

但是我要的是"未打卡的存儲過程 ",而剛才我經测试发现zjcxc(邹建)写的是打过卡的存储的过程,我要把沒有打卡的标示到存储过程的报表里,并在里面再加一个工号的变量;
再求贴
谢谢了!



zjcxc(邹建) ( ) 信誉：428
示例

--示例数据
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
--*/

[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
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
---------- --------- ------- ------- ------- ------- ------
A002 1,2,3,4 1,2,3,4 1,2,3,4 1,2,3,4 1,2,3,4
A003 4 1,2,3,4 1,2,3,4 1,2,3,4 1,2,3,4 1,2,3,4
A005 1,2,3,4 1,2,3,4 1,2,3,4 1,2,3,4 1,2,3,4 1,2,3,4

--*/

[datland]

这里有代码可以下载: http://www.datcn.com/luntan/list.asp?boardid=10
功能介绍:
純動態系統﹐界面﹑欄位﹑報表均可自定義﹐內置delphi7.0解釋器﹐支持運行期間二次開發。
考勤﹐工資部分的計算方法﹐計算項目均可完全由客戶自行定義﹗

[liushengni]

ding

[davorsuker39]

学习！！！支持！！

[davorsuker39]

再顶一下

[cainiao000]

帮你顶