You have an overnight job which is triggered by the arrival of some external data. You keep a record of the date and time that the overnight job is run. Someone wants to know what the average start time of the job is. You can’t use the average function on dates and times in T-SQL so you need to write some custom code to calculate the average time the job starts.
First, you need to identify the point in time that you want the calculation to consider the start of a new day. For a job which runs during the day could be 0 – indicating midnight. For an overnight job you would probably want to consider the possibility that the job could be started the next morning. In that situation you might choose 18 – indicating 6pm as the start of a new day.
Then, in the calculation itself:
- identify the number of seconds since the day division
- get the average of number of seconds
- add to a date time and extract the time portion
-- Defines the hour of the day when a new day starts
DECLARE @DayDivision INT = 12
IF OBJECT_ID(N'tempdb..#StartTimes') IS NOT NULL DROP TABLE #StartTimes
CREATE TABLE #StartTimes(
start DATETIME NOT NULL
INSERT INTO #StartTimes
-- 3. Add the number of seconds to a day starting at the
-- day division hour, then extract the time portion
-- 2. Average number of seconds
-- 1. Get the number of seconds from the day division point (@DayDivision)
CASE WHEN DATEPART(HOUR,start) < @DayDivision THEN
,'01 jan 1900 ' + CAST(@DayDivision AS VARCHAR(2)) + ':00') AS TIME) AS AverageStartTime
One of the problems with using the PIVOT clause in T-SQL is that you have to specify all the spreading column names that you want in your final result set.
To get around this you can generate the column names as strings and then use them within a dynamic SQL query that does the pivotting.
I wish this was an unusual situation… Recently I was asked to develop the ETL process for a system I had never used before. The company that provided the system didn’t want to help or provide documentation to assist in my development. Having been in this situation once or twice before, I knew that I needed to get a handle on a the database structure by looking at foreign keys. Then I needed to create a few diagrams to help me navigate my way around.
BUT… not all the relationships are defined with foreign keys. AND this system is spread across 10 or so databases… So I created this script to help me track down database connectons.