请zjcxc(邹建) 接分

[雪狼1234567]

能不能给我讲一下你给我的语句的关键几句，
create proc p_qry
@年月 varchar(6) --要统计的年月
as
declare @dt1 datetime,@dt2 datetime
,@s varchar(8000),@i int,@tj varchar(1000)

select @dt1=@年月+'01',@dt2=dateadd(month,1,@dt1)-1
,@i=day(@dt2),@s=''
,@tj='a.checkDate between '''+convert(varchar(10),@dt1,120)
+''' and '''+convert(varchar(10),@dt2,120)+''''
while @i>0
select @s=',['+cast(@i as varchar)+'号_上午]=sum(case when CheckName=''上午上班'' and dt='
+cast(@i as varchar)+' then aa else 0 end),['
+cast(@i as varchar)+'号_上午]=sum(case when CheckName=''下午上班'' and dt='
+cast(@i as varchar)+' then aa else 0 end)'+@s
,@i=@i-1
exec('select UserID'+@s+'
from(
select UserID,dt=day(checkDate),CheckName
,aa=case sum(aa) when 0 then 4 else sum(aa) end
from(
select a.UserID,a.checkDate,b.CheckName
,aa=case CheckType
when ''I'' then case when datediff(minute,max(checktime),max(b.BeginTime))<0 then 1 else 0 end
else case when datediff(minute,min(checktime),max(b.EndTime))>0 then 2 else 0 end
end
from [user] a join 表 b on
a.checktime between dateadd(minute,-30,b.BeginTime) and dateadd(minute,30,b.BeginTime)
or a.checktime between dateadd(minute,-30,b.EndTime) and dateadd(minute,30,b.EndTime)
where '+@tj+'
group by a.UserID,a.checkDate,a.CheckType,b.CheckName
)a group by UserID,day(checkDate),CheckName
)a group by UserID')
go

[zjcxc]

谢谢楼主.

[zjcxc]

你看那里不明白再讲吧,这样不好讲.