Try something like this:

create table #T (

ID int identity primary key,

Time_stamp datetime,

Machine_state int);

insert into #T (Time_stamp, Machine_state)

select '3.27.2009 23:00', 0 union all

select '3.27.2009 23:45', 1 union all

select '3.27.2009 23:55', 2 union all

select '3.28.2009 00:30', 1 union all

select '3.28.2009 02:00', 2 union all

select '3.28.2009 04:00', 1 union all

select '3.28.2009 06:00', 2 union all

select '3.28.2009 09:00', 1 union all

select '3.28.2009 16:30', 2 union all

select '3.28.2009 16:50', 0 union all

select '3.28.2009 16:30', 2 union all

select '3.28.2009 18:50', 1 union all

select '3.28.2009 19:50', 2;

create table #Shifts (

ID int identity primary key,

SNumber int,

SStart int,

SStop int);

insert into #Shifts (SNumber, SStart, SStop)

select 1, 8, 17 union all

select 2, 17, 24 union all

select 2, 0, 1 union all

select 3, 1, 8;

select SNumber as Shift, Machine_State,

case

when datepart(hour, State_Start) >= SStart then State_Start

else dateadd(hour, SStart, dateadd(day, datediff(day, 0, State_Start), 0))

end as EffectiveStart,

case

when datepart(hour, State_End) >= SStop then State_End

else dateadd(hour, SStop, dateadd(day, datediff(day, 0, State_End), 0))

end as EffectiveEnd

from #Shifts Shifts

inner join

(select Machine_State, time_stamp as State_Start,

(select min(time_stamp)

from #T T2

where time_stamp > T1.time_stamp

and Machine_state != T1.Machine_state) as State_End

from #T T1

where ID = 11) StartStop

on SStart >= datepart(hour, State_Start)

or SStop > datepart(hour, State_End);