• I needed better closure bands. The current query grouped tickets as "less than 1 hour", "1-24 hours", and "greater than 24 hours". I needed bands that are more reflective of the various SLA options we have in place.
• I needed to create numerous versions with different groupings (average time to close by technician, by location, by issue type, etc.).
• I needed the query to limit the open date. The current query lets you select tickets closed within a specified date range. The issue for me with that approach is that it doesn't give a true snapshot of what is happening in my environment. I may want to look at the last 30, 60, or 90 days' worth of data but the current query would include tickets opened outside of that range...as long as they were closed within the specified window. That means that one long, nagging ticket can skew the data. I needed to look at tickets closed within a date range that were also opened within that range...basically, if a user opened a ticket right now what could they expect in terms of timelines. As a large install base (18,000 computers in KACE) in education, data on closure rates in September are not relevant in November.

• Weekends and Holidays! The current query just counts every 24 hour cycle as a day...a ticket opened on Friday and closed on Monday would show a 3 day time to close when it was really less than one day. Additionally, being a school system, the week at Thanksgiving, two weeks at Christmas, Spring Break, etc. can really make your numbers look terrible. A ticket created on 12/17 and closed on 1/4 was closed in 2 days NOT 18 days!
• More useful time reporting. The current query displays the average time to close in each band as days:hours:minutes:seconds. In practice, once a ticket has been opened for more than a day the hours:minutes:seconds don't matter. Even tickets closed within 24 hours don't need the seconds reported. I also found "4 hours 12 minutes" difficult to compare to "3 hours 56 minutes". Presenting them as "4.2 hours" and "3.9 hours" just made understanding the data much easier for me.

SELECT
    USER.FULL_NAME AS TECHNICIAN,
    (CASE
	WHEN 
            DATE(TIME_OPENED) = DATE(TIME_CLOSED) 
        THEN 
            ' Same Day'
        
	WHEN 
            5 * (DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1, 1) - (
                SELECT
                    COUNT(*)
                FROM
                    HD_SLA_HOLIDAYS
                WHERE
                    (WEEKDAY(VALUE) < 5)
                AND 
                	(VALUE BETWEEN TIME_OPENED AND TIME_CLOSED)
			) < 5 
        THEN
	    '1-5 Days'
		
        WHEN 
            5 * (DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1,	1) - (
                SELECT
                    COUNT(*)
                FROM
                    HD_SLA_HOLIDAYS
                WHERE
                    (WEEKDAY(VALUE) < 5)
                AND (VALUE BETWEEN TIME_OPENED AND TIME_CLOSED)
            ) < 10 
	THEN
	    '5-10 Days'
		
        WHEN 
            5 * (DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1,	1) - (
                SELECT
                    COUNT(*)
                FROM
                    HD_SLA_HOLIDAYS
                WHERE
                    (WEEKDAY(VALUE) < 5)
                AND (VALUE BETWEEN TIME_OPENED AND TIME_CLOSED)
            ) >= 10 
	THEN
	    'More than 10 days'
		
        ELSE
	    'error'
	END)
        AS CLOSE_GROUP,
	count(HD_TICKET.ID) AS NUMBER_OF_TICKETS,

    IF (
        DATE(TIME_OPENED) = DATE(TIME_CLOSED),
        CONCAT(ROUND((SUM(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,	IF (TIME_OPENED = '0', TIME_CLOSED,	TIME_OPENED)))) / COUNT(HD_TICKET.ID) MOD 86400) / 3600, 1), ' Hours'),
        CONCAT((GREATEST(0, ROUND(SUM(5 * (DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1, 1) - (
                        SELECT
                            COUNT(*)
                        FROM
                            HD_SLA_HOLIDAYS
                        WHERE
                            (WEEKDAY(VALUE) < 5)
                        AND (
                        VALUE BETWEEN TIME_OPENED AND TIME_CLOSED
                        )
                    ))/COUNT(HD_TICKET.ID), 1))), ' Days')
        ) AS AVG_TIME_TO_CLOSE
    
FROM
    HD_TICKET
LEFT JOIN USER ON USER.ID=HD_TICKET.OWNER_ID
INNER JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID

WHERE
	HD_STATUS. NAME = 'Complete'
	AND TIME_CLOSED <> 0
	AND TIME_CLOSED > '2015-11-01' /*change the start date here*/
	AND TIME_CLOSED < '2015-12-15' /*change the end date here*/
	AND TIME_OPENED > '2015-11-01' /*remove this line */
        AND HD_TICKET.HD_QUEUE_ID IN (20) /*add queue numbers here separated by commas*/

GROUP BY
	HD_TICKET.OWNER_ID,
	CLOSE_GROUP

Now let's look at each section to see where the changes are from the original Dell KB query and to see how each section works.

The basics of any SQL query designed to retrieve data from the database take the following structure (capitalization doesn't matter but its conventional to write your operators in all caps):

SELECT
	field 1, field 2, field 3
FROM
	data base table name
JOIN
	if the fields are in multiple tables this is where you tell the database how to relate them i.e.
	Table 2 is related to Table 1 because Table 2 Field 3 has the same information as Table 1 Field 1
WHERE
	This is where you specify the criteria for returning records...things like
	field 2 is greater than 7
GROUP BY
	Do you want the records returned to be grouped together based on any fields (you can group by more than one)
ORDER BY
	Not used in this query but you could add it if you wanted the data sorted by any fields

Now lets take each section of the query to look at how it works.

In the opening SELECT statement we are telling the database to return the following fields:

• USER.FULL_NAME AS TECHNICIAN (this is saying table "USER">field "FULL_NAME and instead of calling it "FULL_NAME" in the returned record set please call it "TECHNICIAN"
• The "CASE" statement is written to examine how long each ticket has been opened and return the closure band, i.e. "Same Day", "1-5 Days", etc. and call it "CLOSE GROUP" in the returned record set. This statement is explained in detail below.
• The next IF statement within the SELECT statement calculates the average time to close for each cluster of records created by our GROUP BY statement at the end of the query. This is returned as "AVG_TIME_TO_CLOSE" in the record set...FYI, the AS statements can be changed..."AVG_TIME_TO_CLOSE" is not a SQL command. It's simply what you want this column of data to be titled in the returned record set.

CASE
	WHEN
		This is true
	THEN
		Return this value
	
	WHEN
		This is true
	THEN
		Return this value

	WHEN
		This is true
	THEN
		Return this value

	ELSE
		Return this value
END

The case statement is simply a list of tests performed in order on each row of the database as the query runs. As soon as the current row matches one of the "WHEN" tests then the value specified is returned and the case statement is exited.

For the original query and my version, it is crucial that the closure bands be tested in order of shortest to longest. As soon as a ticket matches a test the statement is exited. This means we only have to test the upper bounds in each band...For the 1-5 day band I don't have to ask if it's greater than 1 day AND less than 5 days. I just need to ask if its less than 5 days. As long as my first "WHEN" asked for tickets that were less than 1 day then all of those are already filtered out by the time it gets to the 1-5 day band.

In the original KB version of the query the bands are built as follows:

CASE
	WHEN
		TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL 1 HOUR)
	THEN
		'0-1 hour'
	
	WHEN
		TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL 24 HOUR) 
	THEN
		'1-24 hours'
	
	WHEN
		TIME_CLOSED>DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL 24 HOUR) 
	THEN
		'>24 hours'
	
	ELSE
		'error' 
END

 So, basically in each of the WHEN statements the TIME_CLOSED is compared to the TIME_OPENED plus some interval. If I add 1 hour to this ticket's TIME_OPENED is it still less than the TIME_CLOSED? If so, then move to the next WHEN test...add 24 hours...if its still less than move to the final check, greater than 24 hours.

My problem is that this approach assumes that every 24 hour period should be counted as an open "day" in the average. In reality, I don't want to count weekends or holidays as open time.

Lets look at my new CASE statement.

My first WHEN clause is very straight forward:

WHEN 
    DATE(TIME_OPENED) = DATE(TIME_CLOSED) 
THEN 
    ' Same Day'

If the date portion (the data in this field is a date/time stamp...the DATE(field) syntax pulls out just the YYYY-MM-DD portion) of TIME_OPENED is the same as TIME_CLOSED then this ticket was closed on the same day it was opened...so create a closure band called ' Same Day' (the leading space is to put this band before the other bands alphabetically).

Now the really new portion of my version of this query.

WHEN 
            5 * (DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1, 1) - (
                SELECT
                    COUNT(*)
                FROM
                    HD_SLA_HOLIDAYS
                WHERE
                    (WEEKDAY(VALUE) < 5)
                AND 
                	(VALUE BETWEEN TIME_OPENED AND TIME_CLOSED)
			) < 5 
        THEN
	    '1-5 Days'

The first portion is the really tricky section...it calculates the number of work days between two dates. This results in a number that ignores weekends. A great write up on variations of this approach can be found here. This algorithm counts jumps...so Monday to Tuesday is 1 jump. Thursday to Tuesday is 3 jumps (Thurs>Fri>Mon>Tue). 

5 * (DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1, 1)

Once we know how many days are between our TIME_OPENED and TIME_CLOSED we need to see if any of those were holidays because we don't want to count them.

So we need to query our HD_SLA_HOLIDAYS table within the existing query.

SELECT
    COUNT(*)
FROM
    HD_SLA_HOLIDAYS
WHERE
    (WEEKDAY(VALUE) < 5)
AND 
    (VALUE BETWEEN TIME_OPENED AND TIME_CLOSED)

This simple SELECT statement gets a count of all dates entered into the Holiday table (Help Desk>Configuration>Define Holidays) that fall on a weekday (we've already accounted for weekends above) and fall within the TIME_OPENED and TIME_CLOSED range. We then subtract this number from the number of weekdays calculation to determine the total number of business days a ticket was open.

We then compare that number to the upper range of the current closure band in the CASE statement and then return the band name AS "CLOSE_GROUP".

The next major update was to change the format of the average time to close from "3d 6h 47m 28s" to simply the number of hours in decimal form if it was closed on the same day or the number of days if it was closed on a later date. For me, once it's past a same day turn around I only care about the number of days...I don't need to see that it was 6 days 4 hours 7 minutes 46 seconds...6.2 days is fine at that point. In fact, I wanted to calculate the average days to close by only looking at the total number of work days tickets in the band were open divided by the number of tickets in the band. I didn't want to use the hours/minutes/seconds from the date/time stamp because I wasn't dealing with work hours...just work days. Perhaps someone else will add in the data from the HD_SLA_BUSINESS_HOURS if they need that level of detail :)

The first thing I needed was to make the query smart enough to do two different calculations and return the answer formatted differently depending on whether its returning the same day format (4.5 Hours) or the multi-day format (4 Days).

Time for the basics of an IF statement in SQL:

IF(conditional test, do if true, do if false)

Pretty simple...test if something is true (is A = B) and do the first item otherwise do the second item in the list.

My statement uses the same comparison we ran in the CASE statement to determine if a ticket was closed on the same day. If so, it calculates the number of hours in decimal format rounded to the nearest tenth. If you need more decimal places you can change the '1' to a '2' or '3' (or more).

count(HD_TICKET.id) MOD 86400) / 3600, 1) <<<Change this 1 to change the number of decimal places displayed

Otherwise it calculates the number of days open (same technique as used in the CASE statement above)...the results of this IF statement are returned in the record set at "AVG_TIME_TO_CLOSE" but you can change that to anything you want.

That should layout the basics of how this query works and hopefully helps more users begin exploring writing their own queries or helps others modify and improve this one.

Below is my version that groups the closure bands by location. 

We have a custom field on our ticket layout that is a drop down selection for location. For us, this is CUSTOM_1 in the ticket layout (Help Desk>Configuration>Queue Customization). In the ticket layout view the custom fields are indexed from 1, however, in the database they are indexed from 0. So, CUSTOM_1 corresponds to CUSTOM_FIELD_VALUE0 in the database.

The query below includes this field in the SELECT statement and groups by it rather than the technician name as above (this one is formated to make it easier to read and see what is going on...SQL ignores white space)

SELECT
	HD_TICKET.CUSTOM_FIELD_VALUE0 AS Location,
	(
		CASE
		WHEN DATE(TIME_OPENED) = DATE(TIME_CLOSED) THEN
			' Same Day'
		WHEN 5 * (
			DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7
		) + MID(
			'0123455401234434012332340122123401101234000123450',
			7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1,
			1
		) - (
			SELECT
				COUNT(*)
			FROM
				HD_SLA_HOLIDAYS
			WHERE
				(WEEKDAY(VALUE) < 5)
			AND (

				VALUE
					BETWEEN TIME_OPENED
				AND TIME_CLOSED
			)
		) < 5 THEN
			'1-5 Days'
		WHEN 5 * (
			DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7
		) + MID(
			'0123455401234434012332340122123401101234000123450',
			7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1,
			1
		) - (
			SELECT
				COUNT(*)
			FROM
				HD_SLA_HOLIDAYS
			WHERE
				(WEEKDAY(VALUE) < 5)
			AND (

				VALUE
					BETWEEN TIME_OPENED
				AND TIME_CLOSED
			)
		) < 10 THEN
			'5-10 Days'
		WHEN 5 * (
			DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7
		) + MID(
			'0123455401234434012332340122123401101234000123450',
			7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1,
			1
		) - (
			SELECT
				COUNT(*)
			FROM
				HD_SLA_HOLIDAYS
			WHERE
				(WEEKDAY(VALUE) < 5)
			AND (

				VALUE
					BETWEEN TIME_OPENED
				AND TIME_CLOSED
			)
		) >= 10 THEN
			'More than 10 days'
		ELSE
			'error'
		END
	) AS CLOSE_GROUP,
	count(HD_TICKET.ID) AS NUMBER_OF_TICKETS,

IF (
	DATE(TIME_OPENED) = DATE(TIME_CLOSED),
	CONCAT(
		ROUND(
			(
				SUM(
					TIME_TO_SEC(
						TIMEDIFF(
							TIME_CLOSED,

						IF (
							TIME_OPENED = '0',
							TIME_CLOSED,
							TIME_OPENED
						)
						)
					)
				) / COUNT(HD_TICKET.ID) MOD 86400
			) / 3600,
			1
		),
		' Hours'
	),
	CONCAT(
		(
			GREATEST(
				0,
				ROUND(
					SUM(
						5 * (
							DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7
						) + MID(
							'0123455401234434012332340122123401101234000123450',
							7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1,
							1
						) - (
							SELECT
								COUNT(*)
							FROM
								HD_SLA_HOLIDAYS
							WHERE
								(WEEKDAY(VALUE) < 5)
							AND (

								VALUE
									BETWEEN TIME_OPENED
								AND TIME_CLOSED
							)
						)
					)
				/COUNT(HD_TICKET.ID), 
			1)
			)
		),
		' Days'
	)
) AS AVG_TIME_TO_CLOSE
FROM
	HD_TICKET
INNER JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
WHERE
	HD_STATUS. NAME = 'Complete'
AND TIME_CLOSED <> 0
AND TIME_CLOSED > '2015-11-01' /*change the start date here*/
AND TIME_CLOSED < '2015-12-15' /*change the end date here*/
AND TIME_OPENED > '2015-11-01'
AND HD_TICKET.HD_QUEUE_ID IN (20) /*add queue numbers here*/
GROUP BY
	HD_TICKET.CUSTOM_FIELD_VALUE0,
	CLOSE_GROUP