THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

T-SQL Tuesday #001: Splitting Date/Time Ranges and Intersections

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.

Published Tuesday, December 08, 2009 3:17 PM by Jonathan Kehayias
Filed under: ,

Comments

 

Adam Machanic said:

Wow! The response to the first T-SQL Tuesday was truly amazing. We ended up with 20 great posts, from

December 9, 2009 8:20 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement