Thursday, May 20, 2010

MS SQL: Procedures time execution

SELECT context, operation, count (total_length)
,avg(total_length) as avg_total
,min(total_length90) as total_length90
,max(total_length) as max_total
,avg(task_length) as avg_task
,min(task_length90) as task_length90
,max(task_length) as max_task
,(avg(total_length) - avg(task_length)) as diff_avg



FROM
(select operation, context, total_length, task_length,
case when total_length_dev = (min(total_length_dev) over(partition by operation, context)) then total_length else null end as total_length90,
case when total_task_dev = (min(total_task_dev) over(partition by operation, context)) then task_length else null end as task_length90
from
(select operation, context, total_length, task_length,
abs(90 -
(
cast((row_number() over (partition by operation, context order by total_length)) * 100 as float) /
(count(*) over (partition by operation, context))
)) as total_length_dev,
abs(90 -
(
cast((row_number() over (partition by operation, context order by task_length)) * 100 as float) /
(count(*) over (partition by operation, context))
)) as total_task_dev
from [wo_perf_log]
--where (end_time between '2010-02-25 09:12:30' and '2010-02-25 10:28:00')
)
t
) t
group
by
operation, context
order by total_length90 desc

No comments:

Post a Comment