处理别人数据库中数据,统计出勤年月报表,吃力不讨好的工作

--年月报表
create proc YMViewReport
@bm varchar(2),                                                            ----部门编号
@year varchar(4),                                                          ----要查询的年 
@month varchar(2)                                                        ----要查询的月
as
declare @tablename varchar(20)
select @tablename='kqmx_'+@year+@month
declare @sql varchar(6000)
select @sql=''
declare @sign varchar(2)
select @sign='*'
/**/
if(@month='==')---表示按年查询         
begin
 declare @allTable varchar(5000)
 select @allTable=dbo.CreSqlSectence(@Year)                                       
 select @sql='select 编号=A.bh,部门=B.bms,姓名=B.xm,出勤=isnull(sum(A.cqgs),0),迟到=isnull(sum(A.cd1),0),'+
  '早退=isnull(sum(A.zt),0),旷工=isnull(sum(A.kg),0),公差=isnull(sum(A.qj08),0),'+
  '事假=isnull(sum(A.qj01),0),病假=isnull(sum(A.qj02),0),婚假=isnull(sum(A.qj03),0),'+
  '丧假=isnull(sum(A.qj04),0),产假=isnull(sum(A.qj05),0),工伤假=isnull(sum(A.qj06),0),'+
  '公休假=isnull(sum(A.qj07),0) from ('+@allTable+') as A right join V_ConnDocBmZw B '+
  'on A.bh=B.bh where A.lx0='+char(39)+@sign+char(39)
 if(@bm<>'00')
 begin
  select @sql=@sql+' and B.bm='+char(39)+@bm+char(39)
 end
 select @sql=@sql+' group by B.bms,A.bh,B.xm order by B.bms,B.xm'
 --print @sql
 exec(@sql)
end
else
begin
 if exists(select * from sysobjects where name=@tablename and type='u')
 begin
 select @sql='select 编号=A.bh,部门=B.bms,姓名=B.xm,出勤=isnull(sum(A.cqgs),0),迟到=isnull(sum(A.cd1),0),'+
  '早退=isnull(sum(A.zt),0),旷工=isnull(sum(A.kg),0),公差=isnull(sum(A.qj08),0),'+
  '事假=isnull(sum(A.qj01),0),病假=isnull(sum(A.qj02),0),婚假=isnull(sum(A.qj03),0),'+
  '丧假=isnull(sum(A.qj04),0),产假=isnull(sum(A.qj05),0),工伤假=isnull(sum(A.qj06),0),'+
  '公休假=isnull(sum(A.qj07),0) from '+@tablename+' A right join V_ConnDocBmZw B '+
  'on A.bh=B.bh where A.lx0='+char(39)+@sign+char(39)
 if(@bm<>'00')
 begin
  select @sql=@sql+' and B.bm='+char(39)+@bm+char(39)
 end
 select @sql=@sql+' group by B.bms,B.xm,A.bh order by B.bms,B.xm'
 --print @sql
 exec(@sql)
 end
end
go



create function dbo.CreSqlSectence(@Year varchar(4))                                          -----@Year为参数年份
returns  varchar(5000)
as
begin
 declare @OutSqlSenten varchar(5000)
 select @OutSqlSenten=''

 declare @JanuaryTn varchar(20)
 select @JanuaryTn='kqmx_'+@year+'01'                                                              -----产生1月的查询语句
 select @OutSqlSenten=dbo.BackSql(@JanuaryTn)

 declare @FebruaryTn varchar(20)
 select @FebruaryTn='kqmx_'+@year+'02'
 select @OutSqlSenten=@OutSqlSenten+dbo.BackSql(@FebruaryTn)                  -----产生2月的查询语句

 declare @MarchTn varchar(20)
 select @MarchTn='kqmx_'+@year+'03'
 select @OutSqlSenten=@OutSqlSenten+dbo.BackSql(@MarchTn)                     -----同上

 declare @AprilTn varchar(20)
 select @AprilTn='kqmx_'+@year+'04'
 select @OutSqlSenten=@OutSqlSenten+dbo.BackSql(@AprilTn)

 declare @MayTn varchar(20)
 select @MayTn='kqmx_'+@year+'05'
 select @OutSqlSenten=@OutSqlSenten+dbo.BackSql(@MayTn)

 declare @JuneTn varchar(20)
 select @JuneTn='kqmx_'+@year+'06'
 select @OutSqlSenten=@OutSqlSenten+dbo.BackSql(@JuneTn)

 declare @JulyTn varchar(20)
 select @JulyTn='kqmx_'+@year+'07'
 select @OutSqlSenten=@OutSqlSenten+dbo.BackSql(@JulyTn)

 declare @AugustTn varchar(20)
 select @AugustTn='kqmx_'+@year+'08'
 select @OutSqlSenten=@OutSqlSenten+dbo.BackSql(@AugustTn)

 declare @SeptemberTn varchar(20)
 select @SeptemberTn='kqmx_'+@year+'09'
 select @OutSqlSenten=@OutSqlSenten+dbo.BackSql(@SeptemberTn)

 declare @OctoberTn varchar(20)
 select @OctoberTn='kqmx_'+@year+'10'
 select @OutSqlSenten=@OutSqlSenten+dbo.BackSql(@OctoberTn)

 declare @NovemberTn varchar(20)
 select @NovemberTn='kqmx_'+@year+'11'
 select @OutSqlSenten=@OutSqlSenten+dbo.BackSql(@NovemberTn)

 declare @DecemberTn varchar(20)
 select @DecemberTn='kqmx_'+@year+'12'
 select @OutSqlSenten=@OutSqlSenten+dbo.BackSql(@DecemberTn)
 
 select @OutSqlSenten=left(@OutSqlSenten,len(@OutSqlSenten)-10)

 return @OutSqlSenten                                                                                              ---产生该年查询语句
end
go

create function dbo.BackSql(@TName varchar(20))
returns varchar(500)
as
begin
 declare @PerSql varchar(500)
 select @PerSql=''
 if exists(select * from sysobjects where name=@TName and type='u')    -----查询是否存在@TName名字的表 
 begin
 select @PerSql='select bh,cqgs,cd1,zt,kg,qj08,qj01,'+
 'qj02,qj03,qj04,qj05,qj06,qj07,lx0 from '+@TName+' union all '       ------(bh,cqgs,cd1,zt,kg,qj08,qj01,'等为要查询字段)
 end
 return @PerSql                                                                                 -------产生1张月表的查询语句   
end
go


考勤表是按月生成的,且该年不一定每个月都有这张表.
function dbo.BackSql  生成查询语句
function dbo.CreSqlSectence  将查询语句联合起来
proc YMViewReport    最后在里面完成统计