Код:

IF EXISTS(select * from tempdb..sysobjects where id = object_id('tempdb..#TEMP_PRC'))     DROP TABLE #TEMP_PRC;
IF EXISTS(select * from tempdb..sysobjects where id = object_id('tempdb..#TEMP'))     DROP TABLE #TEMP;
IF EXISTS(select * from tempdb..sysobjects where id = object_id('tempdb..#TEMP_1'))     DROP TABLE #TEMP_1;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

declare @startTime dateTime
declare @endTime dateTime
declare @providerId bigint
--declare @cof float

set @endTime=getdate()
set @startTime=DATEADD(minute,-15,@endTime)
--set @startTime='2013-02-19 17:22:00'
--set @endTime='2013-02-19 21:08:00'

declare @percents table (oid int, percents float)
insert @percents values(173, 3.38)
insert @percents values(144, 2.8)
insert @percents values(200, 2.71)
insert @percents values(362, 4.4)

select  owner_id
        ,count(Case when trx.extended_code<>0 THEN 1 END)*100.0/(count(*))*1.0 as 'PRC',
        count(Case when trx.extended_code<>0 THEN 1 END) as 'counted'
INTO #TEMP_PRC
from jboss.dbo.trx trx WITH (NOLOCK, INDEX(IDX_START_CLASS_RESULT))
left join @percents p on p.oid=owner_id
where 
trx.started_at>@startTime and trx.finished_at<@endTime
and trx.trx_class_id=2
group by owner_id, p.percents
having 
  100.0
  *count(Case when trx.extended_code<>0 THEN 1 END)
  /isnull(nullif(count(*),0),1) > isnull(p.percents,0.1)

--Select * from #TEMP_PRC
select    owner_id
        ,extended_code
        ,sr.description
        ,count(*) as counted
INTO #TEMP
from jboss.dbo.trx trx WITH (NOLOCK, INDEX(IDX_START_CLASS_RESULT))
        inner join jboss.dbo.send_result_trx sr with (nolock) on sr.id=trx.id
where 
trx.started_at>@startTime and trx.finished_at<@endTime
and trx.trx_class_id=2
and owner_id in (Select owner_id From #TEMP_PRC )
group by owner_id, extended_code, sr.description

select owner_id
    ,sum(counted) as count_all
into #TEMP_1
from #TEMP
group by owner_id


insert into #jboss_monitor_temp
Select cp.display_name       ,t1.owner_id       ,extended_code       ,description       ,counted*100.0/count_all as precent       ,counted
FROM #temp_1 t1
inner join #temp t on t1.owner_id=t.owner_id
right outer join jboss_ds.dbo.provider cp with (nolock) ON t1.owner_id = cp.id 
where not extended_code=0
group by display_name, t1.owner_id, extended_code, description, counted*100.0/count_all, counted

insert into [jboss_monitor].[dbo].[jboss_monitor_]
select *
from #jboss_monitor_temp

Код:

USE [jboss]
GO
/****** Object:  StoredProcedure [dbo].[Jboss_monitor_test]    Script Date: 02/22/2013 12:23:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Jboss_monitor_test]
AS

Код:

select  owner_id         ,
count(Case when trx.extended_code<>0 THEN 1 END)*100.0/(count(*))*1.0 as 'PRC',        
 count(Case when trx.extended_code<>0 THEN 1 END) as 'counted' 
INTO #TEMP_PRC 
from jboss.dbo.trx trx WITH (NOLOCK, INDEX(IDX_START_CLASS_RESULT)) 
left join @percents p on p.oid=owner_id 
where  trx.started_at>@startTime and trx.finished_at<@endTime and trx.trx_class_id=2 
group by owner_id, p.percents 
having    100.0   *count(Case when trx.extended_code<>0 THEN 1 END)   /isnull(nullif(count(*),0),1) > isnull(p.percents,0.1)  

--Select * from #TEMP_PRC 

select    owner_id         ,extended_code         ,sr.description         ,count(*) as counted 
INTO #TEMP 
from jboss.dbo.trx trx WITH (NOLOCK, INDEX(IDX_START_CLASS_RESULT))        
 inner join jboss.dbo.send_result_trx sr with (nolock) on sr.id=trx.id 
where  trx.started_at>@startTime and trx.finished_at<@endTime 
and trx.trx_class_id=2 
and owner_id in (Select owner_id From #TEMP_PRC ) 
group by owner_id, extended_code, sr.description  

select owner_id     ,sum(counted) as count_all 
into #TEMP_1 
from #TEMP group by owner_id