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);

-- This is just to create the table for testing.
create table #MachineStatus (
ID int identity primary key,
Time_stamp datetime,
Machine_state int);

insert into #MachineStatus (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;

-- Here is the actual solution.

;With StatusFlow 
AS
(
	SELECT Time_Stamp,
	       Machine_State,
		   ChangeId = ROW_NUMBER() OVER(ORDER BY Time_Stamp), -- In case the identity gets messed up.
		   Shift = SNumber
	FROM #MachineStatus	ms
	LEFT JOIN #Shifts s ON DATEPART(HOUR,ms.Time_Stamp) BETWEEN s.SStart AND s.SStop
),

Changes AS
(
	SELECT Status = ss.Machine_State,
		   NewStatus = se.Machine_State,
		   StateStart = ss.Time_Stamp,
		   StateEnd = se.Time_Stamp,
		   Duration = DATEDIFF(MINUTE,ss.Time_Stamp, se.Time_Stamp),
		   ss.Shift
	FROM StatusFlow ss
	INNER JOIN StatusFlow se ON se.ChangeId = ss.ChangeId + 1
	
)

SELECT Shift,Status, TotalTime = SUM(Duration)
FROM Changes
GROUP BY Shift,Status
ORDER BY Shift ,Status