At the end of November, Adam Machanic put out the invite to join a weekly blog party called TSQL Tuesday where bloggers have a given topic to blog about offering tips and tricks. This weeks theme is Date/Time tricks so I thought I’d share a trick I learned a long time ago that continues to be useful from time to time when I need to split date ranges.
I was going to use a post from StackOverflow that I answered, to demonstrate this, but I have to many other issues with how the table is designed and the data is stored in that example so I created a different one. Lets say that your database server uses a lot of SQL Agent jobs and a new requirement is to track how many of those jobs run during “peak” business hours and then report on a monthly basis the total number of minutes that each job was executing during those hours. Some of the jobs in your environment run only a few minutes, while others, particularly maintenance jobs, run over 24 hours during the weekends which have lighter load, but still define the same “peak” business hours (trying not to overcomplicate this, but I can show how to solve for different weekend hours if necessary).
If you’ve ever worked with the sysjobhistory table in msdb, you probably already know that getting the start date and time and end date and time for a job isn’t as easy as you’d initially expect. It unfortunately requires some additional coding steps to translate the integer values in the run_date, run_time, and run_duration columns into the start and end date times. The solution used below was attributed to Steve Kass in a ASPFAQ article.
The Code
DECLARE @MinJobDate DATETIME, @MaxJobDate DATETIME
-- Get the min and max run_dates
SELECT
@MinJobDate = MIN(CONVERT(DATETIME, RTRIM(run_date))),
@MaxJobDate = MAX(CONVERT(DATETIME, RTRIM(run_date)))
FROM msdb..sysjobhistory
-- Define the Periods of Business and Non-Business Hours
;WITH OfficeHours (StartTime, EndTime, OfficeHours)
AS
(
SELECT d1=CAST('00:00:00' AS datetime), d2=CAST('08:30:00' AS datetime), officehours=CAST(0 AS bit)
UNION ALL
SELECT d1=CAST('08:30:00' AS datetime), d2=CAST('17:00:00' AS datetime), officehours=CAST(1 AS bit)
UNION ALL
SELECT d1=CAST('17:00:00' AS datetime), d2=CAST('23:59:59' AS datetime), officehours=CAST(0 AS bit)
),
-- Generate a List of dates betweent he min and max values
SingleDays (theDate)
AS
(
SELECT DATEADD(dd, number, @MinJobDate-1)
FROM master..spt_values
WHERE type = N'P'
AND DATEADD(dd, number, @MinJobDate-1) <= @MaxJobDate
),
-- Merge Business and Non-Business Hours with each of the days
OfficeTimes (StartTime, EndTime, OfficeHours)
AS
(
SELECT theDate+StartTime, theDate+EndTime, OfficeHours
FROM SingleDays, OfficeHours
),
-- Generate normal Start and End DateTime values from the jobhistory.
AgentJobHistory (JobId, StartDateTime, EndDateTime)
AS
(
SELECT
job_id,
CAST(LTRIM(STR(run_date))+' '+STUFF(STUFF(RIGHT('000000'+LTRIM(STR(run_time)), 6) , 3, 0, ':'), 6, 0, ':') AS datetime) AS StartTime,
CAST(LTRIM(STR(run_date))+' '+STUFF(STUFF(RIGHT('000000'+LTRIM(STR(run_time)), 6) , 3, 0, ':'), 6, 0, ':') AS datetime) + CAST(STUFF(STUFF(REPLACE(STR(run_duration,6),' ','0'),5,0,':'),3,0,':') AS datetime) AS EndTime
FROM msdb..sysjobhistory jh
WHERE step_name = '(Job outcome)'
),
-- Split the job runs into Business and Non-Business Time segments
SplitJobs (JobId, StartTime, EndTime, OfficeHours)
AS
(
SELECT
JobID,
CASE
WHEN j.StartDateTime BETWEEN ot.StartTime AND ot.EndTime THEN j.StartDateTime
ELSE ot.StartTime
END,
CASE
WHEN j.EndDateTime BETWEEN ot.StartTime AND ot.EndTime
THEN j.EndDateTime
ELSE ot.EndTime
END,
OfficeHours
FROM AgentJobHistory AS j
CROSS JOIN OfficeTimes AS ot
WHERE ot.EndTime >= j.startDateTime
AND ot.StartTime < j.EndDateTime
),
-- Aggregate the output for reporting.
JobTotals (JobId, StartTime, EndTime, NonOfficeMins, OfficeMins)
AS
(
SELECT
j.JobId,
j.StartDateTime,
j.EndDateTime,
SUM(CASE
WHEN OfficeHours = 0 THEN DATEDIFF(mi, sj.StartTime, DATEADD(ss, 1, sj.EndTime))
ELSE 0
END) AS NonOfficeMins,
SUM(CASE
WHEN OfficeHours = 1 THEN DATEDIFF(mi, sj.StartTime, DATEADD(ss, 1, sj.EndTime))
ELSE 0
END) AS OfficeMins
FROM AgentJobHistory AS j
JOIN SplitJobs AS sj ON j.JobID = sj.JobID
GROUP BY j.JobId, j.StartDateTime, j.EndDateTime
)
-- Generate aggregate report of Peak and NonPeak minutes
SELECT j.name, SUM(NonOfficeMins) AS NonPeakMinutes, SUM(OfficeMins) AS PeakMinutes
FROM msdb..sysjobs AS j
JOIN JobTotals jt ON j.job_id = jt.JobId
GROUP BY j.name
HAVING SUM(OfficeMins) > 0
ORDER BY SUM(OfficeMins) DESC
Explanation
There really isn’t any magic here. The first three CTE’s build a set of dates and times that correlate to “peak” or “nonpeak” hours for each date that is covered by the jobhistory entries. The fourth CTE generates the start and end times for the jobs in datetime format so that they can be used for splitting in the subsequent CTE. The split occurs based on the following rules:
- IF timerange.EndTime >= job.StartTime AND timerange.StartTime < job.EndTime
- IF job.StartTime BETWEEN timerange.StartTime and timerange.EndTime use job.StartTime ELSE use timerange.StartTime
- IF job.EndTime BETWEEN timerange.StartTime and timerange.EndTime use job.EndTime ELSE use timerange.EndTime
The CROSS JOIN to the time ranges allows for a multi-day or multi-range execution to be properly split into the non-peak and peak portions based on the above rules. This kind of split can be used for any kind of time range calculation. I first saw something similar in my first job where it was used to split charges into monthly portions that aligned with contract dates. The key to its usage is to know the time ranges that the data needs to be split into, and create either a derived table that contains those values, or as shown here use one or more CTE’s to generate those values on the fly. A calendar table would be a good utility tool for this kind of operation where the ranges are generally known and performance is important.