What's up !

I used GSquared tables for a solution like this:

-- 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 #MachineStatusms

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