#title Batch Window

음... 2000버전에서는 맞았는데..
다시 만들어야 함...

{{{
IF object_id('usp_batch_window') IS NOT NULL
	DROP proc usp_batch_window
go
 
CREATE proc usp_batch_window
	@unit int = 10 --분단위 입력
,	@OPTION tinyint = 0 --0:전체, 1:작업단계단위그룹
,	@time char(5) = NULL
 
AS
/*
예제: 
	--일일 작업 전체보기
	exec usp_batch_window 15, 0
 
	--일일 작업 30분단위로 전체보기
	exec usp_batch_window 30, 0
 
	--일일 작업그룹 단위로 보기
	exec usp_batch_window 15, 1
 
	--특정시간대에 실행되는 작업보기
	exec usp_batch_window 15, 0, '06:35'
 
	--이러면 안되요
	exec usp_batch_window -1, 0, '06:35'
 
*/
 
IF @unit <= 0 
begin
	SELECT 
		case 
			when @unit = 0 then '이런 멍충이! 0으로 나누기 오류자나! 1이상의 값을 입력해!'
			else '마이너스(-)를 입력하면 나보고 어쩌라고요~'
		end 메시지
	RETURN
end
 
SELECT
	name
,	job_id
,	step_name
,	job_name 
,	step_id 
,	exec_begin_time 
,	max_run_duration
INTO #temp
FROM (
	SELECT
		min(job_name) job_name
	,	job_id
	,	min(name) name
	,	min(step_name) step_name
	,	min(step_id) step_id
	,	min(exec_begin_time) exec_begin_time
	,	max(run_duration) max_run_duration
	FROM (
		SELECT 
			a.name + '(' + b.step_name + ')' job_name
		,	a.name
		,	a.job_id
		,	b.step_name
		,	b.step_id 
		,	LEFT(RIGHT('000000' + cast(c.next_run_time AS varchar(8)), 6),2) + ':' +
			substring(RIGHT('000000' + cast(c.next_run_time AS varchar(8)), 6), 3, 2) exec_begin_time
		,	b.run_duration
		FROM msdb..sysjobs a INNER JOIN msdb..sysjobhistory b
		ON a.job_id = b.job_id INNER JOIN msdb..sysjobschedules c
		ON a.job_id = c.job_id 
		WHERE b.step_id > 0
		AND a.enabled = 1
		--2005이상의 버전에는 아래의 조건에 해당되는 컬럼이 없다.
		--AND c.freq_type = 4
		--AND c.freq_subday_interval = 0
	) t
	GROUP BY 
		job_id
	,	job_name
	,	step_id
	,	exec_begin_time
) t
 
--■▶▷▤▥▦▧▌▐▬▬□
--declare @unit tinyint
--set @unit = 1
 
SELECT
	name 작업명
,	step_name 작업단계명
,	step_id 작업단계순서
,	exec_begin_time 실행시작시간
,	exec_end_time 실행종료시간
,	replicate('▷', datediff(mi, case when @OPTION = 0 then '00:00' else begin_time end, exec_begin_time) / @unit) +
	replicate('▶', case when datediff(mi, exec_begin_time, exec_end_time) / @unit = 0 then 1 else datediff(mi, exec_begin_time, exec_end_time) / @unit end) + 
	'(' + cast(sec_run_duration AS varchar(6)) + ')' 차트
FROM (
	SELECT
		name
	,	step_name
	--,	job_name 
	,	step_id
	--,	exec_begin_time
	,	begin_time
	,	RIGHT(convert(char(16), dateadd(ss, acc_run_duration - max_run_duration, exec_begin_time + ':00'), 121), 5) exec_begin_time
	,	RIGHT(convert(char(16), dateadd(ss, acc_run_duration, exec_begin_time + ':00'), 121), 5) exec_end_time
	,	case when acc_run_duration/60 < 60 then 1 else acc_run_duration/60 end acc_run_duration
	,	case when max_run_duration/60 < 60 then 1 else max_run_duration/60 end min_run_duration
	,	max_run_duration sec_run_duration
	FROM (
		SELECT
			name
		,	step_name
		,	job_name 
		,	step_id
		,	exec_begin_time
		,	(SELECT min(exec_begin_time) FROM #temp where a.job_id = job_id) begin_time
		,	max_run_duration
		,	(SELECT sum(max_run_duration) FROM #temp where a.job_id = job_id and a.step_id >= step_id) acc_run_duration
		FROM #temp a
	) t
) t
WHERE case when @time IS NULL then '00:00' else @time end BETWEEN 
		case when @time IS NULL then '00:00' else exec_begin_time end AND 
		case when @time IS NULL then '11:59' else exec_end_time end
ORDER BY 
	case when @OPTION = 0 then exec_begin_time else name end
,	case when @OPTION = 1 then step_id end
go
}}}