排班表: tb_pbb
description 项目说明,在结果表中的列标题
time 项目的标准时间
min 为提前时间,单位为分钟,如果为 NULL,则表明为加班项
max 为推后时间,单位为分钟,如果项目为加班项,则为加班数的计算基数
*/
--建立考勤表
create table kqm_mstr(kqm_gh varchar(6),kqm_rq int,kqm_sj datetime)
insert into kqm_mstr
select '001006',20030713,'07:48'
union all select '001006',20030713,'08:15'
union all select '001006',20030713,'12:13'
union all select '001006',20030713,'12:14'
union all select '001006',20030713,'13:07'
union all select '001006',20030713,'17:31'
union all select '001006',20030713,'18:32'
union all select '001006',20030713,'22:59'
union all select '001006',20030713,'23:30'
union all select '001007',20030713,'07:55'
union all select '001007',20030713,'12:05'
union all select '001007',20030713,'13:00'
union all select '001007',20030713,'17:30'
insert into tb_pbb
select '上午-上班','08:00',30,15
union all select '上午-下班','12:00',0,30
union all select '下午-上班','13:30',30,15
union all select '下午-下班','17:30',0,30
union all select '加班','18:30',null,30
--生成正常上班项目的SQL语句
select @sql=@sql+',a.['+description+']'
,@sql1=@sql1+char(13)
+' ,max(case description when '''+description
+''' then kqm_sj end) as ['+description+']'
from(
select distinct description from tb_pbb
where min is not null
) a
set @sql1=@sql1+char(13)+'from('
+char(13)+' select kqm_gh ,kqm_rq ,convert(varchar(5),max(kqm_sj),108) as kqm_sj,description'
+char(13)+' from kqm_mstr a,tb_pbb b'
+char(13)+' where a.kqm_sj between dateadd(mi,-b.min,b.time) and dateadd(mi,b.max,b.time)'
+char(13)+' group by kqm_gh ,kqm_rq ,description'
+char(13)+' ) aa group by kqm_gh ,kqm_rq'
--print @sql1
--生成加班项目的SQL语句
select @sql=@sql+',b.['+description+'-上班]'
+',b.['+description+'-下班]'+',b.['+description+'统计数]'
,@sql2=@sql2
+char(13)+' ,max(case description when '''+description
+''' then time1 end) as ['+description+'-上班]'
+char(13)++' ,max(case description when '''+description
+''' then time2 end) as ['+description+'-下班]'
+char(13)++' ,max(case description when '''+description
+''' then time3 end) as ['+description+'统计数]'
from(
select distinct description from tb_pbb
where min is null
) a
set @sql2=@sql2+char(13)+'from('
+char(13)+' select kqm_gh,kqm_rq'
+char(13)+' ,convert(varchar(5),min(kqm_sj),108) as time1'
+char(13)+' ,convert(varchar(5),max(kqm_sj),108) as time2'
+char(13)+' ,datediff(mi,min(kqm_sj),max(kqm_sj))/max(b.max) as time3'
+char(13)+' ,description'
+char(13)+' from kqm_mstr a,tb_pbb b'
+char(13)+' where a.kqm_sj > b.time and b.min is null'
+char(13)+' group by kqm_gh ,kqm_rq ,description'
+char(13)+') bb group by kqm_gh ,kqm_rq'
--print @sql2
--/*
print @sql+' from ('+char(13)+@sql1+char(13)+') a full join ('+char(13)+@sql2
+char(13)+') b on a.kqm_gh=b.kqm_gh and a.kqm_rq=b.kqm_rq'
--*/
exec(@sql+' from ('+@sql1+') a full join ('+@sql2
+') b on a.kqm_gh=b.kqm_gh and a.kqm_rq=b.kqm_rq')