TSQL Challenges

TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

It is again time to play with some date/time values. This challenge involves reading the audit log entries generated by a monitoring application and create a report that shows the up-time and down-time of a system that the application is monitoring.

Red Gate SQL Backup Pro is sponsoring TSQL Challenge 23. The top winner of TSQL Challenge 23 will win a license of SQL Backup Pro worth $795, and a Red Gate goodie bag including a T-shirt, frisbee and more.

The Context

NP Corporation is using a Legacy Application that their employees use to perform some of the day-to-day operations. The system is quite old, the people that built the system is not around any more. Due to some unknown reasons the system hangs frequently and the only way to bring it up is to reboot the server. Though it is not an acceptable behavior, there is no alternative and the management of NP corporation has decided to migrate to a new system soon. The management wants to keep track of the uptime and downtime of the system and created a monitoring application that populate an audit table as given below.

AuditTime               IsOnline
----------------------- --------
2010-01-01 10:11:23.000 1
2010-01-01 10:14:03.000 1
2010-01-01 10:19:36.000 1
2010-01-01 10:23:59.000 0
2010-01-01 10:27:14.000 0
2010-01-01 10:31:24.000 1
2010-01-01 10:34:15.000 1
2010-01-01 10:36:11.000 0
2010-01-01 10:39:40.000 1
2010-01-01 10:42:23.000 1

The monitoring application checks the status of the application preriodically and logs the status to the audit table. If the server is online, it writes "1" and "0" if the system is not online. "AuditTime" column stores the time at which the monitoring application checked the status of the server. The monitoring application has a custom logic that decides the interval at which the audit takes place.

I know, none of you are happy with the way the system works and the monitoring application behaves. Some of you might even refuse to work in such an environment arguing the whole setup does not make sense. However, for the purpose of this challenge, assume that you have no choice than to live with it!

The Challenge

Your job is to write a query that produces a report showing the uptime and downtime of the system. Here is the output that you need to produce, based on the given input data.

TimeFrom            TimeTo              IsOnline
------------------- ------------------- --------
2010-01-01 10:00:00 2010-01-01 10:21:47 Yes
2010-01-01 10:21:47 2010-01-01 10:29:19 No
2010-01-01 10:29:19 2010-01-01 10:35:13 Yes
2010-01-01 10:35:13 2010-01-01 10:37:55 No
2010-01-01 10:37:55 2010-01-01 11:00:00 Yes

There are a few points to keep in mind when generating the output data.

• The solution should be a single query that starts with a "SELECT", ";WITH" or "WITH"
• The first and last periods start and end on the hour preceding and following. (Note that the "TimeFrom" on the first row is 10 AM and "TimeTo" in the last row is 11 AM. For example, if the first reading is at 08:23:45, the first period in the resultset should start at 8:00:00. If the last reading is at 21:23:45, the last period in the result should end at 22:00:00.
• All rows except for the first and last should finish at the midpoint between the readings. For example the reading at 10:19 found the system to be online and the reading at 10:23 found it to be offline. Since the server went offline some time between the two readings, you should take the midpoint between the readings: 10:21
• The first and last periods should be whatever status the first and last readings indicate (rather than assuming that the period starts up or down).
• The result should use the datetime type, rounded down to the nearest second. Each reading is taken at an exact second - there is no millisecond component.

Sample Data

Here is the script to genreate the source tables and populate the sample data

IF OBJECT_ID('TC23','U') IS NOT NULL BEGIN
	DROP TABLE TC23
END
GO

CREATE TABLE TC23(
	AuditTime DATETIME,
	IsOnline BIT -- 0:Down, 1:Up
)
GO

INSERT INTO TC23(AuditTime, IsOnline)
SELECT '2010-01-01T10:11:23', 1 UNION ALL
SELECT '2010-01-01T10:14:03', 1 UNION ALL
SELECT '2010-01-01T10:19:36', 1 UNION ALL
SELECT '2010-01-01T10:23:59', 0 UNION ALL
SELECT '2010-01-01T10:27:14', 0 UNION ALL
SELECT '2010-01-01T10:31:24', 1 UNION ALL
SELECT '2010-01-01T10:34:15', 1 UNION ALL
SELECT '2010-01-01T10:36:11', 0 UNION ALL
SELECT '2010-01-01T10:39:40', 1 UNION ALL
SELECT '2010-01-01T10:42:23', 1 

SELECT * FROM TC23
/*
AuditTime               IsOnline
----------------------- --------
2010-01-01 10:11:23.000 1
2010-01-01 10:14:03.000 1
2010-01-01 10:19:36.000 1
2010-01-01 10:23:59.000 0
2010-01-01 10:27:14.000 0
2010-01-01 10:31:24.000 1
2010-01-01 10:34:15.000 1
2010-01-01 10:36:11.000 0
2010-01-01 10:39:40.000 1
2010-01-01 10:42:23.000 1
*/

Notes

1. Read the Submission Guidelines and make sure that your solution follows them.
2. The solution should work on SQL Server 2005, 2008 or later versions
3. Use this forum for any questions related to TSQL Challenge #23
4. Last Date to submit your entries: 22 February 2010 Midnight GMT.

Red Gate SQL Backup Pro is sponsoring TSQL Challenge 23. The top winner of TSQL Challenge 23 will win a license of SQL Backup Pro worth $795, and a Red Gate goodie bag including a T-shirt, frisbee and more.

Note: In case more than one user comes in the first place, we will select the winner through a lucky draw

Special thanks to the Early Review Team, especially Sergejack and dave ballantyne who reviewed the challenge and provided valuable inputs to make the challenge description and rules more comprehensible. Rui, Peter and Rob from the TSQL Challenge Team worked really hard on this challenge to get it to the way you see it now.