비는 시간 잘 찾아서 새로운 JOB을 배치할 때 사용하면 좋을 것으로 예상 합니다. ^^
-- 하루치데이터입력
declare @StartDate datetime, @EndDate datetime, @TmpDate datetime
select @StartDate = '2009-11-18', @EndDate = '2009-11-19'
select @TmpDate = @StartDate
set nocount on
if not exists(select * from tempdb.dbo.sysobjects where name like '#BaseTime%')
begin
create table #BaseTime(Base datetime)
while (@TmpDate < @EndDate)
begin
insert into #BaseTime values(@TmpDate)
set @TmpDate = dateadd(mi, 10, @TmpDate)
end
end
-- JOB 로그를바탕으로각JOB 별실행시간표시
select *
from ( select a.JOBName, a.RealStartTime, a.Duration, right(convert(char(16), b.Base, 121), 5) as 'DT', '=====' as value
from ( select JOBName, convert(char(15), StartTime, 121) + '0' as StartTime, max(Duration) as Duration
, dateadd(second, (left(max(Duration), 2) * 3600) + substring(max(Duration), 4, 2) * 60 + right(max(Duration), 2), min(StartTime)) as 'EndTime'
, min(StartTime) as 'RealStartTime'
from ( select J.name as JOBName
, convert(varchar(19), convert(datetime, convert(char(8), JH.run_date) + ' ' + substring(right('000000' + convert(varchar(6), JH.run_time), 6), 1,2) + ':'
+ substring(right('000000' + convert(varchar(6), JH.run_time), 6), 3,2) + ':' + right('00' + convert(varchar(6), JH.run_time), 2)), 121) as StartTime
, substring(right('000000' + convert(varchar(6), JH.run_duration), 6), 1,2) + ':' + substring(right('000000' + convert(varchar(6), JH.run_duration), 6), 3,2) + ':'
+ right('00' + convert(varchar(6), JH.run_duration), 2) as Duration
from msdb.dbo.sysjobs J with(nolock)
inner join msdb..sysjobhistory JH with(nolock) ON J.job_id = JH.job_id
where JH.step_id = 0 and JH.run_status = 1
) as a
where StartTime >= @StartDate and StartTime < @EndDate
--and JOBName in ('JOB이름')
--and JOBName not like '%10분%'
group by JOBName, convert(char(15), StartTime, 121) + '0'
) a
inner join #BaseTime b with(nolock) on b.Base >= a.StartTime and b.Base < a.EndTime
where b.Base >= @StartDate and b.Base < @EndDate
) as pnt
pivot
(
Max(value)
for DT in ( [00:00], [00:10], [00:20], [00:30], [00:40], [00:50]
, [01:00], [01:10], [01:20], [01:30], [01:40], [01:50]
, [02:00], [02:10], [02:20], [02:30], [02:40], [02:50]
, [03:00], [03:10], [03:20], [03:30], [03:40], [03:50]
, [04:00], [04:10], [04:20], [04:30], [04:40], [04:50]
, [05:00], [05:10], [05:20], [05:30], [05:40], [05:50]
, [06:00], [06:10], [06:20], [06:30], [06:40], [06:50]
, [07:00], [07:10], [07:20], [07:30], [07:40], [07:50]
, [08:00], [08:10], [08:20], [08:30], [08:40], [08:50]
, [09:00], [09:10], [09:20], [09:30], [09:40], [09:50]
, [10:00], [10:10], [10:20], [10:30], [10:40], [10:50]
, [11:00], [11:10], [11:20], [11:30], [11:40], [11:50]
, [12:00], [12:10], [12:20], [12:30], [12:40], [12:50]
, [13:00], [13:10], [13:20], [13:30], [13:40], [13:50]
, [14:00], [14:10], [14:20], [14:30], [14:40], [14:50]
, [15:00], [15:10], [15:20], [15:30], [15:40], [15:50]
, [16:00], [16:10], [16:20], [16:30], [16:40], [16:50]
, [17:00], [17:10], [17:20], [17:30], [17:40], [17:50]
, [18:00], [18:10], [18:20], [18:30], [18:40], [18:50]
, [19:00], [19:10], [19:20], [19:30], [19:40], [19:50]
, [20:00], [20:10], [20:20], [20:30], [20:40], [20:50]
, [21:00], [21:10], [21:20], [21:30], [21:40], [21:50]
, [22:00], [22:10], [22:20], [22:30], [22:40], [22:50]
, [23:00], [23:10], [23:20], [23:30], [23:40], [23:50])
) as pvt
order by 1, 2
drop table #BaseTime;