select 编号,月份,满勤天数,
sum(case 类型 when '病假' then 天数 else 0 end) 病假天数,
sum(case 类型 when '缺勤' then 天数 else 0 end) 缺勤天数,
sum(case 类型 when '事假' then 天数 else 0 end) 事假天数
from t
group by 编号,月份,满勤天数
sum(case 类型 when '病假' then 天数 else 0 end) 病假天数,
sum(case 类型 when '缺勤' then 天数 else 0 end) 缺勤天数,
sum(case 类型 when '事假' then 天数 else 0 end) 事假天数
from t
group by 编号,月份,满勤天数
declare @sql varchar(8000)
select @sql=''
select @sql=@sql+',['+类型+']=sum(case 类型 when '''+类型+''' then 天数 else 0 end)' from t
exec('select 编号,月份,满勤天数'+@sql+' from t group by 编号,月份,满勤天数')
create table #t(编号 varchar(10),月份 varchar(10),满勤天数 numeric(10,1),天数 numeric(10,1),类型 varchar(10))
insert into #t(编号,月份,满勤天数,天数,类型)
select '001','2004-05',18.0,2.0,'病假' union all
select '001','2004-05',18.0,1.0,'缺勤' union all
select '001','2004-05',18.0,2.0,'事假' union all
select '002','2004-05',18.0,NULL,NULL union all
select '003','2004-05',18.0,NULL,NULL union all
select '005','2004-05',18.0,NULL,NULLdeclare @sql varchar(8000)
select @sql=''
select @sql=@sql+',['+类型+'天数]=sum(case 类型 when '''+类型+''' then 天数 else 0 end)' from #t where 类型 is not null
exec('select 编号,月份,满勤天数'+@sql+' from #t group by 编号,月份,满勤天数')
drop table #t
CREATE view DBo.人员考勤
as
却提示说declare附近有语法错误。
完整的如下:
CREATE view DBo.人员考勤
asdeclare @sql varchar(8000)
select @sql=''create table t(人员编号 varchar(7),月份 varchar(7),满勤天数 numeric(10,1),天数 numeric(10,1),类型 varchar(10))
insert into t
select personcode,assesstime,满勤天数,Cast(eventtime as float)/平均 天数, eventtype from (
select a.*,a.plantime/满勤天数 平均,b.EventTime,B.EventType from (
select sum(plantime) as plantime,personcode,assesstime, cast(sum(plantime) as float)/avg(cast(PlanTime as float)) 满勤天数 from (
select sum(datediff(minute, cast('2004-06-01 '+PlanBeginTime as datetime),cast('2004-06-01 '+PlanEndTime as datetime)) ) as PlanTime,
personcode,substring(assessTime,1,7) as assesstime from Rs_AssessInfo where deletedflag='0'
group by personcode,assessTime)a group by personcode,assessTime )a
left join (
select sum(EventTime) as EventTime,personcode,assessTime ,eventtype from (
select sum(isnull(datediff(minute,cast('2004-06-01 '+BeginTime as datetime), cast('2004-06-01 '+Endtime as datetime)),0)) as EventTime,
personcode,substring(assessTime,1,7) as assessTime ,eventtype from Rs_EventInfo where deletedflag='0'
group by personcode,assesstime ,eventtype) a group by personcode,assesstime ,eventtype
) b on a.personcode=b.personcode and a.assesstime=b.assesstime )a
--select * from tselect @sql=@sql+',['+类型+'天数]=sum(case 类型 when '''+类型+''' then 天数 else 0 end)' from t where 类型 is not null
exec('select 人员编号,月份,满勤天数'+@sql+' from t group by 人员编号,月份,满勤天数')
drop table t