------------統計User使用次數-----------------------------------------------------------
select UpdateBy,count,day,convert(decimal(11,2),(count*1.0/(case when day=0 then 1 else day end))) as UseTime,beginDT,EndDT
from
(
select e.UpdateBy,count(*) as count,
datediff(dd,(select min(UpdateDT) from dbo.ExeVersion where UpdateBy=e.UpdateBy),
(select max(UpdateDT) from dbo.ExeVersion where UpdateBy=e.UpdateBy)
) as Day,
-- count(*)/datediff(dd,(select min(UpdateDT) from dbo.ExeVersion where UpdateBy=e.UpdateBy),
-- (select max(UpdateDT) from dbo.ExeVersion where UpdateBy=e.UpdateBy)
-- ) as Usetime,
(select min(UpdateDT) from dbo.ExeVersion where UpdateBy=e.UpdateBy) as beginDT,
(select max(UpdateDT) from dbo.ExeVersion where UpdateBy=e.UpdateBy) as EndDT
from dbo.ExeVersion e
inner join dbo.member m on e.updateby=m.Username
where m.isvalid=1
group by e.UpdateBy
--order by count(*) desc
) xx
order by count desc,UseTime desc
-----------------------------------------------------------------------