cvstrac 中统计一段时间各人员的工作量(代码行)
| DateStart: 2008-07-10 DateEnd: 2008-07-10 执行 |
|
| 用户 | 提交次数 | 涉及文件数 | 新增文件数 | 删除文件数 | 修改文件次数 | 插入行数 | 删除行数 |
| wangp | 1 | 1 | 0 | 0 | 1 | 181 | 200 |
| yangdong | 1 | 4 | 0 | 0 | 4 | 47 | 31 |
sql脚本如下
select m.muser as '用户',
min(n.ncn) as '提交次数',
count(m.mfilename) as '涉及文件数',
sum(m.addf) as '新增文件数',
sum(m.delf) as '删除文件数',
sum(m.modf) as '修改文件次数',
sum(m.mnins) as '插入行数',
sum(m.mndel) as '删除行数'
from
(
select a.user as muser,
b.filename as mfilename,
sum(b.nins) as mnins,
sum(b.ndel) as mndel,
sum(case chngtype when 1 then 1 else 0 end) as addf,
sum(case chngtype when 2 then 1 else 0 end) as delf,
sum(case chngtype when 0 then 1 else 0 end) as modf
from chng a,filechng b
where a.cn= b.cn
and a.date >=julianday(aux('DateStart',date('now')))*86400 -julianday('1970-01-01')*86400
and a.date <=julianday(aux('DateEnd' ,date('now')))*86400 -julianday('1970-01-01')*86400 +86400
group by a.user,b.filename
) as m,
(
select user as nuser,
count(cn) as ncn
from chng c
where date >=julianday(aux('DateStart',date('now')))*86400-julianday('1970-01-01')*86400
and date <=julianday(aux('DateEnd' ,date('now')))*86400-julianday('1970-01-01')*86400 +86400
group by c.user
) as n
where m.muser =n.nuser
group by m.muser
order by m.muser
[
本帖最后由 dalang 于 2008-7-10 17:44 编辑 ]
