I got this nice puzzle from Chip Smithson. The data involved contains travel information. Use the following code to create a table called Travel and populate it with sample data:
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.Travel', 'U') IS NOT NULL
DROP TABLE dbo.Travel;
CREATE TABLE dbo.Travel
(
travelerid INT NOT NULL,
destination VARCHAR(25) NOT NULL,
travelstart DATETIME2(0) NOT NULL,
milestraveled INT NOT NULL,
PRIMARY KEY(travelerid, travelstart)
);
INSERT INTO dbo.Travel(travelerid, destination, travelstart, milestraveled) VALUES
(2, 'England', '20110212 10:00:00', 0),
(2, 'England', '20110212 12:00:00', 200),
(2, 'England', '20110212 14:00:00', 300),
(2, 'England', '20110213 08:00:00', 500),
(2, 'Germany', '20110215 10:00:00', 700),
(2, 'Germany', '20110216 03:00:00', 900),
(2, 'Germany', '20110216 08:00:00', 1100),
(2, 'Germany', '20110218 06:00:00', 1300),
(2, 'England', '20110219 12:00:00', 1700),
(2, 'England', '20110219 14:00:00', 1900),
(2, 'England', '20110221 10:00:00', 2300),
(7, 'USA', '20110314 10:00:00', 0),
(7, 'USA', '20110314 12:00:00', 100),
(7, 'USA', '20110314 14:00:00', 200),
(7, 'Canada', '20110315 08:00:00', 300),
(7, 'Canada', '20110317 10:00:00', 900),
(