/*
-- Sample execution
declare @projectID int = 1
exec History.ScanServerProgressHourly @projectId
*/
alter procedure History.ScanServerProgressHourly (@projectID int)
WITH ENCRYPTION
as begin
declare
@sqlString1 nvarchar(max) = N'Select [Time], '
,@sqlString2 nvarchar(max) = '
from(
SELECT
dateadd(hour, datediff(hour, 0, StartDate), 0) as [Time]
, Count(*) cnt
, ServerID
FROM jobs.t_job j with(nolock)
inner join jobs.t_JobLocationProjectIPRange jlr with(nolock) on j.JobID = jlr.JobID
where jlr.ProjectID = ' + cast (@ProjectID as nvarchar(5)) +
'GROUP BY dateadd(hour, datediff(hour, 0, StartDate), 0), ServerID
) p
pivot
(
max(cnt)
for serverid in('
,@sqlString3 nvarchar(max) = ')
)piv
Order by Time desc'
;with c_server as
(
select distinct
'[' + cast (s.ServerID as nvarchar(5)) + '] ' [ServerID]
,'''' + s.Hostname + '''' [Hostname]
from config.t_Server s with(nolock)
inner join jobs.t_job j with(nolock) on s.ServerID = j.JobID
)
select distinct
@sqlString1 +=
stuff((select ', ' + ServerID + 'AS ' + Hostname
from c_server
for xml path(''), type)
.value('.','NVARCHAR(MAX)'),1,2,'') --as ServerNames
+ @sqlString2 +
--+ char(13) +
stuff((select ', ' + ServerID
from c_server
for xml path(''), type)
.value('.','NVARCHAR(MAX)'),1,2,'') --as ServerID
+ @sqlString3
from c_server s
print @sqlString1
exec (@sqlString1)
end
go | 