Congratulations to Ken Selvia, a Programmer Analyst for Enterprise Products Company in Houston, Texas and Shane Dubble, a DBA for Innoviant, Inc. in Wausau, Wisconsin. Ken won first prize of \$100 for the best solution to the November Reader Challenge, "Grouping Sequential Changes." Shane won second prize of \$50. Here’s a recap of the problem and the solution to the November Reader Challenge.

### Problem:

Steve is a systems analyst in an IT department for a midsized company. As part of a batch process, data is extracted from his production database, which logs activity into a table at periodic intervals. Each activity entry contains a flag that indicates a particular state. The batch process performs two operations in an infinite loop. As part of the logging, the process includes the time when the operation was started and a flag indicating the state. A sample schema with data is shown below.

`CREATE TABLE Activity ( CreateTime smalldatetime                                 not null, Flag bit not null );                              INSERT INTO Activity values( '2005-10-01 06:00', 0);                              INSERT INTO Activity values( '2005-10-01 06:01', 0);                              INSERT INTO Activity values( '2005-10-01 06:02', 0);                              INSERT INTO Activity values( '2005-10-01 06:03', 1);                              INSERT INTO Activity values( '2005-10-01 06:04', 1);                              INSERT INTO Activity values( '2005-10-01 06:05', 0);                              INSERT INTO Activity values( '2005-10-01 06:06', 0);                              INSERT INTO Activity values( '2005-10-01 06:07', 1);                              INSERT INTO Activity values( '2005-10-01 06:08', 0);                              INSERT INTO Activity values( '2005-10-01 06:09', 1);                              SELECT * FROM Activity;`

In our example, let us assume that the Flag column is a bit data type and the values toggle from 0 to 1 and vice versa. The CreateTime column is a smalldatetime value that’s always increasing. Steve is trying to do the following:

1. Group changes in the Flag column in the sequence in which they occur based on the CreateTime value
2. Determine the start and end values of CreateTime for each group

The expected output of the query should be:

 StartTime EndTime Flag ------------------- ------------------- ------ 2005-10-01 06:00:00 2005-10-01 06:02:00 0 2005-10-01 06:03:00 2005-10-01 06:04:00 1 2005-10-01 06:05:00 2005-10-01 06:06:00 0 2005-10-01 06:07:00 2005-10-01 06:07:00 1 2005-10-01 06:08:00 2005-10-01 06:08:00 0 2005-10-01 06:09:00 2005-10-01 06:09:00 1

Help Steve write a query that will get the expected results showing the changes in the Flag column value in the order in which they occurred and the start and end times when the changes occurred.

### Solution:

Steve can use the fact that the "CreateTime" value is increasing in nature to find the next "CreateTime" value for each row that has a different "Flag" value. For example, given the row where "CreateTime" equals '2005-10-01 06:00', the query to get retrieve the next "CreateTime" value where the "Flag" value equals 1 is below:

`select min(t2.CreateTime)                                from Activity as t2                                where t2.CreateTime > '2005-10-01 06:00'                                and t2.Flag = 1`

Similarly, the query below can be used to determine the next greater " CreateTime" value for the row where "CreateTime" equals '2005-10-01 06:03' and the flag Flag value is 0.

`select min(t2.CreateTime)                                from Activity as t2                                where t2.CreateTime > '2005-10-01 06:03'                                and t2.Flag = 0`

Steve’s query can now be similarily constructed with all rows in the table. The SELECT statement that incorporates the query as a correlated sub-query is shown below.

`select min(t.CreateTime) as StartTime, max(t.CreateTime) as EndTime, t.Flag                                from (                                    select t1.CreateTime, t1.Flag,                                        isnull((select min(t2.CreateTime)                                                        from Activity as t2                                                  where t2.CreateTime > t1.CreateTime                                                        and t2.Flag = case t1.Flag                                                     when 0 then 1                                                     when 1 then 0                                                  end), t1.CreateTime) as GroupTime                                    from Activity as t1                                ) as t                              group by t.GroupTime, t.Flag                              order by t.GroupTime;`

The ISNULL check in the query takes care of the last row in the table or the row with the maximum "CreateTime" value.