Database Challenges

Here is the basic testing data for TSQL Challenge 23

Source data

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

Expected Results

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

Sample Data Script

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
*/