Databases
StackOverflow2013
Postsdbo
POHow to group together sequential, timestamped rows in SQL and return the date range for each group
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to group together sequential, timestamped rows in SQL and return the date range for each group
    text
    copied!<p>I've got an MS SQL 2008 database table that looks like the following:</p> <p><code>Registration | Date | DriverID | TrailerID</code></p> <p>An example of what some of the data would look like is as follows:</p> <pre><code>AB53EDH,2013/07/03 10:00,54,23 AB53EDH,2013/07/03 10:01,54,23 ... AB53EDH,2013/07/03 10:45,54,23 AB53EDH,2013/07/03 10:46,54,NULL &lt;-- Trailer changed AB53EDH,2013/07/03 10:47,54,NULL ... AB53EDH,2013/07/03 11:05,54,NULL AB53EDH,2013/07/03 11:06,54,102 &lt;-- Trailer changed AB53EDH,2013/07/03 11:07,54,102 ... AB53EDH,2013/07/03 12:32,54,102 AB53EDH,2013/07/03 12:33,72,102 &lt;-- Driver changed AB53EDH,2013/07/03 12:34,72,102 </code></pre> <p>As you can see, the data represents which driver and which trailer were attached to which registration at any point in time. What I'd like to do is to generate a report that contains periods that each combination of driver and trailer were active for. So for the above example data, I'd want to generate something that looks like this:</p> <pre><code>Registration,StartDate,EndDate,DriverID,TrailerID AB53EDH,2013/07/03 10:00,2013/07/03 10:45,54,23 AB53EDH,2013/07/03 10:46,2013/07/03 11:05,54,NULL AB53EDH,2013/07/03 11:06,2013/07/03 12:32,54,102 AB53EDH,2013/07/03 12:33,2013/07/03 12:34,72,102 </code></pre> <p>How would you go about doing this via SQL? </p> <p><strong>UPDATE:</strong> Thanks to the answers so far. Unfortunately, they stopped working when I applied it to production data I have. The queries submitted so far fail to work correctly when applied on part of the data.</p> <p>Here's some sample queries to generate a data table and populate it with the dummy data above. There is more data here than in the example above: the driver,trailer combinations 54,23 and 54,NULL have been repeated in order to make sure that queries recognise that these are two distinct groups. I've also replicated the same data three times with different date ranges, in order to test if queries will work when run on part of the data set:</p> <pre><code>CREATE TABLE [dbo].[TempTable]( [Registration] [nvarchar](50) NOT NULL, [Date] [datetime] NOT NULL, [DriverID] [int] NULL, [TrailerID] [int] NULL ) INSERT INTO dbo.TempTable VALUES ('AB53EDH','2013/07/03 10:00', 54,23), ('AB53EDH','2013/07/03 10:01', 54,23), ('AB53EDH','2013/07/03 10:45', 54,23), ('AB53EDH','2013/07/03 10:46', 54,NULL), ('AB53EDH','2013/07/03 10:47', 54,NULL), ('AB53EDH','2013/07/03 11:05', 54,NULL), ('AB53EDH','2013/07/03 11:06', 54,102), ('AB53EDH','2013/07/03 11:07', 54,102), ('AB53EDH','2013/07/03 12:32', 54,102), ('AB53EDH','2013/07/03 12:33', 72,102), ('AB53EDH','2013/07/03 12:34', 72,102), ('AB53EDH','2013/07/03 13:00', 54,102), ('AB53EDH','2013/07/03 13:01', 54,102), ('AB53EDH','2013/07/03 13:02', 54,102), ('AB53EDH','2013/07/03 13:03', 54,102), ('AB53EDH','2013/07/03 13:04', 54,23), ('AB53EDH','2013/07/03 13:05', 54,23), ('AB53EDH','2013/07/03 13:06', 54,23), ('AB53EDH','2013/07/03 13:07', 54,NULL), ('AB53EDH','2013/07/03 13:08', 54,NULL), ('AB53EDH','2013/07/03 13:09', 54,NULL), ('AB53EDH','2013/07/03 13:10', 54,NULL), ('AB53EDH','2013/07/03 13:11', NULL,NULL) INSERT INTO dbo.TempTable SELECT Registration, DATEADD(M, -1, Date), DriverID, TrailerID FROM dbo.TempTable WHERE Date &gt; '2013/07/01' INSERT INTO dbo.TempTable SELECT Registration, DATEADD(M, 1, Date), DriverID, TrailerID FROM dbo.TempTable WHERE Date &gt; '2013/07/01' </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload