declare @pid int,@cid int,@i int,@dt datetime,@y int
set @pid=2
set @cid=8

declare @tbym table(forder int IDENTITY ,fname varchar(100),fdate datetime)
declare @tbymm table(fy int,fm int,fyy int,fmm int,fc int)
set @dt='2000-1-1'
set @y=
year('2006-7-1')
--year(getdate())
set @dt=dateadd(yyyy,@y-2000,@dt) --当年的元旦

set @i=1
while @i<13
begin
insert @tbym (fname,fdate) values (@i, dateadd(mm,@i,@dt))
set @i=@i+1
end

set @i=@y-1
while @i>=@y-5
begin
insert @tbym (fname,fdate) values (@i, dateadd(yyyy,@i-2000,'2001-1-1'))
set @i=@i-1
end

--select * from @tbym
--order by forder




insert @tbymm
select year(fd) fy,month(fd) fm,year(writedate) fyy,month(writedate) fmm,count(*) fc
from (
select ms.sn,m.maintaindate fd,writedate
from T_maintain_sn ms
left join T_maintain m on m.id=ms.mid
left join serial p on p.sn=ms.sn
left join pr003 s on p.number=s.number
where p.pn=@pid and
(@cid=-1 or @cid=client)
) a
group by year(fd),month(fd),year(writedate),month(writedate)


select a.forder,a.fname,isnull(a.fc,0) fac,isnull(b.fc,0) fmc ,fyc0,fyc1,fyc2,fyc3,fyc4,fyc5,fyce
from (
select forder,fname,count(*) fc
from (
select l.*,a.fc from @tbym l
left join
(
select p.client fc,s.writedate fd
from serial p
left join pr003 s on p.number=s.number
where p.pn=@pid and
(@cid=-1 or @cid=client)
) a
on l.fdate>a.fd
) a
group by forder,fname
--order by forder
) a
left join
(
select forder,fname,b.fc ,isnull(m0.fc,0) fyc0,isnull(m1.fc,0) fyc1,isnull(m2.fc,0) fyc2,isnull(m3.fc,0) fyc3,isnull(m4.fc,0) fyc4,isnull(m5.fc,0) fyc5,isnull(me.fc,0) fyce
from @tbym a
left join (select fy,fm,sum(fc) fc from @tbymm group by fy,fm) b on year(a.fdate)=b.fy and a.forder=b.fm
left join (select fy,fm,sum(fc) fc from @tbymm where fyy=@y-0 group by fy,fm) m0 on year(a.fdate)=m0.fy and a.forder=m0.fm
left join (select fy,fm,sum(fc) fc from @tbymm where fyy=@y-1 group by fy,fm) m1 on year(a.fdate)=m1.fy and a.forder=m1.fm
left join (select fy,fm,sum(fc) fc from @tbymm where fyy=@y-2 group by fy,fm) m2 on year(a.fdate)=m2.fy and a.forder=m2.fm
left join (select fy,fm,sum(fc) fc from @tbymm where fyy=@y-3 group by fy,fm) m3 on year(a.fdate)=m3.fy and a.forder=m3.fm
left join (select fy,fm,sum(fc) fc from @tbymm where fyy=@y-4 group by fy,fm) m4 on year(a.fdate)=m4.fy and a.forder=m4.fm
left join (select fy,fm,sum(fc) fc from @tbymm where fyy=@y-5 group by fy,fm) m5 on year(a.fdate)=m5.fy and a.forder=m5.fm
left join (select fy,fm,sum(fc) fc from @tbymm where fyy<@y-5 group by fy,fm) me on year(a.fdate)=me.fy and a.forder=me.fm
where a.forder<=12
union all
select forder,fname,b.fc ,isnull(m0.fc,0) fyc0,isnull(m1.fc,0) fyc1,isnull(m2.fc,0) fyc2,isnull(m3.fc,0) fyc3,isnull(m4.fc,0) fyc4,isnull(m5.fc,0) fyc5,isnull(me.fc,0) fyce
from @tbym a
left join (select fy,sum(fc) fc from @tbymm group by fy) b on year(a.fdate)=b.fy
left join (select fy,sum(fc) fc from @tbymm where fyy=@y-0 group by fy) m0 on year(a.fdate)=m0.fy
left join (select fy,sum(fc) fc from @tbymm where fyy=@y-1 group by fy) m1 on year(a.fdate)=m1.fy
left join (select fy,sum(fc) fc from @tbymm where fyy=@y-2 group by fy) m2 on year(a.fdate)=m2.fy
left join (select fy,sum(fc) fc from @tbymm where fyy=@y-3 group by fy) m3 on year(a.fdate)=m3.fy
left join (select fy,sum(fc) fc from @tbymm where fyy=@y-4 group by fy) m4 on year(a.fdate)=m4.fy
left join (select fy,sum(fc) fc from @tbymm where fyy=@y-5 group by fy) m5 on year(a.fdate)=m5.fy
left join (select fy,sum(fc) fc from @tbymm where fyy<@y-5 group by fy) me on year(a.fdate)=me.fy
where a.forder>12
) b
on a.forder=b.forder
order by a.forder