create procedure History.ScanServerProgress (@ProjectID int)
WITH ENCRYPTION
as begin
set nocount on;
declare @msg nvarchar(2000) = N'Procedure name : History.ScanServerProgress' + char(13)
set @msg += N' Returns scanning server overall progress. ' + char(13)
set @msg += N' 1) Servable Targets. ' + char(13)
set @msg += N' 2) Served targets' + char(13)
set @msg += N' 3) Completed percentage' + char(13) + CHAR(13)
set @msg += N' Sample execution:' + char(13)
set @msg += N' declare @proejctId int = 1' + char(13)
set @msg += N' exec History.ScanServerProgress @projectId ' + char(13) + char(13)
set @msg += N' NOTE : Targets which can be served by multiple servers' + char(13)
set @msg += N' will simply be allocated evenly across the each' + char(13)
set @msg += N' server to give the servable_target figure. ' + char(13)
set @msg += N' This may not be how they get served in practice.' + char(13)
raiserror (@msg, 0, 1) with nowait;
with c_serv as
(
select
srv.Hostname
,srv.ServerID
,ips.LocationID
,sum(ips.Target_count) [servable_targets]
,[served].cnt [complete_targets]
from config.fn_IpRangeSummary(@ProjectID, default) ips -- ProjectID :-> 1
inner join config.t__Location_Server ls with ( nolock ) on ips.LocationID = ls.LocationID
inner join config.t_Server srv with ( nolock ) on ls.ServerID = srv.ServerID
left outer join
(
SELECT
j.ServerID
,Count(j.JobID) cnt
FROM jobs.t_job j with(nolock)
GROUP BY ServerID
)
[served] on ls.ServerID = [served].ServerID
group by
srv.Hostname
,srv.ServerID
,ips.LocationID
,[served].cnt
)
-- Locations with multiple Servers assigned
-- Simply divide targets by Server count.
, c_mult_server_per_loc as
(
select
s.LocationID
,count(s.ServerID) [servers_for_location]
from c_serv s
group by s.LocationID
having count(s.ServerID) > 1
)
, c_res as
(
select
s.Hostname
,s.ServerID
,sum(s.[servable_targets] / coalesce(sloc.servers_for_location, 1)) [servable_targets]
,s.[complete_targets]
from c_serv s
left outer join c_mult_server_per_loc sloc on s.LocationID = sloc.LocationID
group by
s.Hostname
,s.ServerID
,s.[complete_targets]
)
select
@ProjectID [ProjectID]
,rank() over (order by r.ServerID) [ServerID]
,r.Hostname [server_name]
,r.servable_targets
,r.complete_targets
,cast(r.complete_targets * 100.00 / r.servable_targets as numeric ( 6, 2)) [complete_pct]
from c_res r
end
go
| 
