请教一个分组统计的问题
班组ID 员工 白班或夜班 合格品数量 总产品数量rq
1张三白班901002013/1/1
1张三夜班801002013/1/1
1李四夜班801002013/1/1
1王五白班961002013/1/2
1张三夜班831002013/1/2
1李四夜班831002013/1/2
2sam白班1862002013/1/1
2sam夜班1602002013/1/1
2john夜班1602002013/1/1
2mike白班1762002013/1/2
2sam夜班1662002013/1/2
2john夜班1662002013/1/2
..............................................

我想每月统计出每个人合格率并排序，每个月从月初统计到该月最大日期，该如何做呢？
[解决办法]
Select * From(
Select [员工],datename(yyyy,[rq
]),datename(mm,[rq
]),sum([合格品数量])*100/sum([合格品数量]) as [合格率] From [表]
Group by [员工],datename(yyyy,[rq
]),datename(mm,[rq])) as tb ORder by [合格率] DESC
[解决办法]

CREATE TABLE t_Job
(
   班组ID  INT,
   员工 VARCHAR(10),
   白班或夜班  VARCHAR(10),
   合格品数量 int,
   总产品数量 int,
   rq date
)
insert into t_Job
select 1,'张三','白班',90,100,'2013/1/1'
union all
select 1,'张三','夜班',80,100,'2013/1/1'
union all
select 1,'李四','夜班',90,100,'2013/1/1'
union all
select 1,'王五','白班',96,100,'2013/1/2'
union all
select 1,'张三','夜班',83,100,'2013/1/2'
union all
select 1,'李四','夜班',83,100,'2013/1/1'
union all
select 2,'sam','白班',186,200,'2013/1/1'
union all
select 2,'sam','夜班',160,200,'2013/1/1'
union all
select 2,'john','夜班',160,200,'2013/1/1'
union all
select 2,'mike','白班',176,200,'2013/1/2'
union all
select 2,'sam','夜班',166,200,'2013/1/2'
union all
select 2,'john','夜班',166,200,'2013/1/2'


select * from t_Job;
select tm.* from 
(
select 员工,cast(SUM(合格品数量) as decimal)/SUM(总产品数量) 合格率, DATEPART(m,rq) ToMonth from t_Job group by [员工], DATEPART(m,rq)) tm
order by tm.合格率 desc
/*
员工         合格率                                     ToMonth
---------- --------------------------------------- -----------
王五         0.96000000000                           1
mike       0.88000000000                           1
李四         0.86500000000                           1 
 
sam        0.85333333333                           1
张三         0.84333333333                           1
john       0.81500000000                           1

(6 行受影响)
*/

---author:viola
---version:sql server 2008 r2
--统计每个人每个月的合格率
IF object_id('cheJian') is not null drop TABLE cheJian
create table cheJian
( 
classID int,
name nvarchar(20),
NightOrDay  nvarchar(20),
OkNumber int,
Total int,
rq date
) 

delete FROM cheJian

insert INTO cheJian VALUES(1,N'张三',N'白班',90,100,'2013/1/1')
insert INTO cheJian VALUES(1,N'张三',N'夜班',80,100,'2013/1/1')
insert INTO cheJian VALUES(1,N'李四',N'夜班',80,100,'2013/1/1')
insert INTO cheJian VALUES(1,N'王五',N'白班',96,100,'2013/1/2')
insert INTO cheJian VALUES(1,N'张三',N'夜班',83,100,'2013/1/2')
insert INTO cheJian VALUES(1,N'李四',N'夜班',83,100,'2013/1/2')
insert INTO cheJian VALUES(2,'sam',N'白班',186,200,'2013/1/1')
insert INTO cheJian VALUES(2,'sam',N'夜班',160,200,'2013/1/1')
insert INTO cheJian VALUES(2,'john',N'夜班',160,200,'2013/1/1')
insert INTO cheJian VALUES(2,'mike',N'白班',176,200,'2013/1/2')
insert INTO cheJian VALUES(2,'sam',N'夜班',166,200,'2013/1/2')
insert INTO cheJian VALUES(2,'john',N'夜班',166,200,'2013/1/2')

select name,convert(decimal(4,2),sum(OkNumber*1.0/Total)) as '合格率','月份'=MONTH(rq) from cheJian group by name,MONTH(rq)