Calculating Machine Status Time in SQL

I have a simple SQL express serve开发者_C百科r set up and am logging data to it, I have multiple machines (industrial machinery) I am monitoring. Whenever the machines status changes I create a new record which enters data in the following columns. Time (Time Stamp), Machine Name (Text), Status (1,2, or 3) for running, idle, and down, and ReasonCode (1-10). I need to calculate how long the machine was in each status and also the reason for each status. I would like to make this available via SQL reports. This is a function that A LOT of industrial manufactures are looking to do these days and I am trying to create a simple example. Unfortunately I am not that versed with SQL. I imagine this could be put in a stored procedure and run every n seconds to re calculate. Any help would be most appreciated.

---

Assuming a table called status containing

MachineName NVarChar(whatever)
Status Int
Reason Int
Time DateTime

Then this query should work nicely

Select 
    st.MachineName,
    st.status,
    st.reason
    st.Time as TimeChanged,
    DateDiff(ss, min(dur.Time), st.Time)
From
    Status st inner join
    Status dur on st.MachineName = dur.MachineName and st.Time <dur.Time
group by
    st.MachineName,
    St.Status,
    st.Time,
    st.reason

Edit - in answer to your comment

select
    st.MachineName,
    sum(case when status=1 then Duration else 0 end) as RunningTime,
    sum(case when status=2 then Duration else 0 end) as IdleTime,
    sum(case when status=3 then Duration else 0 end) as DownTime,
From
    (Select 
        st.MachineName,
        st.status,
        st.reason
        st.Time as TimeChanged,
        DateDiff(ss, min(dur.Time), st.Time) as Duration
    From
        Status st inner join
        Status dur on st.MachineName = dur.MachineName and st.Time <dur.Time
    group by
        st.MachineName,
        St.Status,
        st.Time,
        st.reason) as foo
Group by MachineName

That should get machine name and a column for the time spent in each of the states

---

You don't need to re-calculate. You can just query the info on demand. Here is a self contained example

DECLARE @Test 
Table (
    LogTime datetime, 
    MachineName varchar(100),
     Status int , 
     ReasonCode  int)

INSERT INTO @Test VALUES ('01/01/2011 4:19:11.459'  ,   'ServerX',  1,  3)
INSERT INTO @Test VALUES ('01/02/2011 3:43:03.652'  ,   'ServerZ',  0,  4)  
INSERT INTO @Test VALUES ('02/04/2011 11:17:51.827'  ,  'ServerX',  2,  2)  
INSERT INTO @Test VALUES ('02/05/2011 4:22:22.205'  ,   'ServerX',  3,  1)  
INSERT INTO @Test VALUES ('01/03/2011 11:42:44.211'  ,  'ServerZ',  1,  4)  



;with TIMEdelta AS (
SELECT 
    machineName,
    t.LogTime,
    MIN(nextTime) as nextTime 
FROM (
    Select 
        t.MachineName,
        t.LogTime, 

        t1.LogTime nextTime
    from @Test t
        INNER JOIN @Test t1
        ON t.machineName = t1.machineName
        AND t.LogTime < t1.LogTime

    ) t

GROUP BY
    machineName,

    t.LogTime

)

SELECT 
    t.MachineName,
    t.LogTime,
    t.ReasonCode,
    t.Status,
DateDiff(DAY, 0, (TIMEdelta.nextTime - TIMEdelta.LogTime) ) Days,
DatePart(HOUR , TIMEdelta.nextTime - TIMEdelta.LogTime ) Hour,
DatePart(MINUTE, TIMEdelta.nextTime - TIMEdelta.LogTime ) MInute

FROM 

    @Test t
    LEFT JOIN TIMEdelta
    ON t.LogTime = timedelta.logtime
        and t.MachineName = TIMEdelta.MachineName

This outputs

MachineName LogTime                 ReasonCode  Status      Days        Hours       Minutes
----------- ----------------------- ----------- ----------- ----------- ----------- -----------
ServerX     2011-01-01 04:19:11.460 3           1           34          6           58
ServerZ     2011-01-02 03:43:03.653 4           0           1           7           59
ServerX     2011-02-04 11:17:51.827 2           2           0           17          4
ServerX     2011-02-05 04:22:22.207 1           3           NULL        NULL        NULL
ServerZ     2011-01-03 11:42:44.210 4           1           NULL        NULL        NULL

You could modify the output to display the current time - logtime instead of Null for those current state rows

Update For aggregates for pivot you can use PIVOT

  SELECT
 MachineName,
 [0] as Started,
 [1] as Stopped,
 [2] as Paused,
 [3] as Foo

FROM
(
SELECT 
    t.MachineName,
    t.status,
    cast(TimeDelta.nextTime - t.LogTime as DECIMAL(18,10)) duration

FROM 

    @Test t
    LEFT JOIN TIMEdelta
    ON t.LogTime = timedelta.logtime
    and t.MachineName = TIMEdelta.MachineName
) source
PIVOT
(
SUM(duration)
FOR status IN ([0], [1], [2], [3])
) AS PivotTable;

Which has this output

MachineName Started       Stopped        Paused       Foo
----------- ------------  -------------- ------------ ------------
ServerX     NULL          34.2907449846  0.7114627315 1.0000000000
ServerZ     1.3331082948  NULL           NULL         ULL

Update Used dates that crossed months and updated Days calc