sql/ms.sql

   1 -- http://metaclass.livejournal.com/381806.html?thread=3001454#t3001454
   2
   3 declare @log table (
   4         Id int identity(1,1) primary key,
   5         ShiftId int not null,
   6         State1 int not null,
   7         State2 int null
   8 );
   9
  10 -- Completely random data, just as a sample
  11 insert into @log (ShiftId, State1, State2)
  12 select top 300 low as [ShiftId],
  13         cast(substring(cast(newid() as binary(16)), 1, 3) as int) as [State1], case
  14                 when (number % 5) % 3 = 0 then null
  15                 else cast(substring(cast(newid() as binary(16)), 1, 3) as int)
  16         end as [State2]
  17 from master.dbo.spt_values where type = 'P'
  18 order by newid();
  19
  20 -- Now get start and end conditions for each shift
  21 select Id, ShiftId, State1, State2, case RN when 1 then 'Start' else 'End' end as [StateTime]
  22 from (
  23         select *,
  24                 row_number() over(partition by shiftid order by id) as [rn],
  25                 count(*) over(partition by Shiftid) as [ShiftSize]
  26         from @log
  27         ) sq
  28 where rn in (1, ShiftSize)
  29 order by ShiftId, rn;