calculate amount of time at a certain status

Hello All,

I have a program that pulls status data from our machines on the manufacturing floor so we can tell when a machine sitting idle or needs maintenance and so on.  Each of these status types is assigned a number and the machines are checked every five minutes.  This data is stored in a table.
  For Example:
Machine             DateTime                      Status
12345                 4/25/15 0:00                 20
12345                 4/25/15 0:05                 30
12345                 4/25/15 0:10                 30
12345                 4/25/15 0:15                 30
12345                 4/25/15 0:20                 30
12345                 4/25/15 0:25                 30
12345                 4/25/15 0:30                 30
12345                 4/25/15 0:35                 30
12345                 4/25/15 0:40                 20
12345                 4/25/15 0:45                 30
12345                 4/25/15 0:50                 30


I want to grab the current status and I want to calculate based in the table above how long the machine has been at this status.  The problem that I am having is if the status goes from say 30 to 20 and back to 30 again, I only want to know how long it has been at the 30 status currently.  ie for 5 minutes.
NettieEckAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
This approach does not rely on testing for specific status values, instead it finds the latest status value, then looks backward to find the next most recent status that is a different value, then it calculates the time difference in minutes. It can also list what the current and previous status values are.
select L.Machine, L.CurrentStatus, OA.PrevStatus, OA.PrevTime, L.[DateTime]
     , DATEDIFF(minute, ISNULL(OA.PrevTime,L.[DateTime]) ,L.[DateTime]) AS MinsAtThisState
from (
      select Machine, [DateTime], Status as CurrentStatus
           , ROW_NUMBER() OVER(PARTITION BY Machine ORDER BY [DateTime] DESC) AS RN 
      from machinelog
     ) as L
OUTER APPLY (
  SELECT TOP(1) Status, [DateTime]
  FROM machinelog
  WHERE L.CurrentStatus <> Status
  ORDER BY [DateTime] DESC
  ) AS OA (PrevStatus, PrevTime)
WHERE L.RN = 1      
 

Open in new window


example result:
| Machine | CurrentStatus | PrevStatus |                PrevTime |                DateTime | MinsAtThisState |
|---------|---------------|------------|-------------------------|-------------------------|-----------------|
|   12345 |            30 |         20 | April, 25 2015 00:40:00 | April, 25 2015 00:50:00 |              10 |

Open in new window


details:
**MS SQL Server 2014 Schema Setup**:

    
    
    CREATE TABLE machinelog
        ([Machine] int, [DateTime] datetime, [Status] int)
    ;
        
    INSERT INTO machinelog
        ([Machine], [DateTime], [Status])
    VALUES
        (12345, '2015-04-25 00:00:00', 20),
        (12345, '2015-04-25 00:05:00', 30),
        (12345, '2015-04-25 00:10:00', 30),
        (12345, '2015-04-25 00:15:00', 30),
        (12345, '2015-04-25 00:20:00', 30),
        (12345, '2015-04-25 00:25:00', 30),
        (12345, '2015-04-25 00:30:00', 30),
        (12345, '2015-04-25 00:35:00', 30),
        (12345, '2015-04-25 00:40:00', 20),
        (12345, '2015-04-25 00:45:00', 30),
        (12345, '2015-04-25 00:50:00', 30)
    ;
    
**Query 1**:

    select L.Machine, L.CurrentStatus, OA.PrevStatus, OA.PrevTime, L.[DateTime]
         , DATEDIFF(minute, ISNULL(OA.PrevTime,L.[DateTime]) ,L.[DateTime]) AS MinsAtThisState
    from (
          select Machine, [DateTime], Status as CurrentStatus
               , ROW_NUMBER() OVER(PARTITION BY Machine ORDER BY [DateTime] DESC) AS RN 
          from machinelog
         ) as L
    OUTER APPLY (
      SELECT TOP(1) Status, [DateTime]
      FROM machinelog
      WHERE L.CurrentStatus <> Status
      ORDER BY [DateTime] DESC
      ) AS OA (PrevStatus, PrevTime)
    WHERE L.RN = 1      
      

**[Results][2]**:
    | Machine | CurrentStatus | PrevStatus |                PrevTime |                DateTime | MinsAtThisState |
    |---------|---------------|------------|-------------------------|-------------------------|-----------------|
    |   12345 |            30 |         20 | April, 25 2015 00:40:00 | April, 25 2015 00:50:00 |              10 |

  [1]: http://sqlfiddle.com/#!6/3bac5/6

Open in new window

0
 
Mark ElySenior Coldfusion DeveloperCommented:
Try something like this:

WITH Status30 AS
 (SELECT Count(status) AS C30
   FROM NameOfYourTable
   WHERE Status = 30
  ),
 StatusNot30 AS
  (SELECT Count(status) AS C30
   FROM NameOfYourTable
   WHERE Status <> 30
  )

SELECT (S1.C30 * .05) AS SumStatus30InSeconds, (S2.C30 * .05) AS SumStatusNot30InSeconds
FROM Status30 AS S1
CROSS APPLY StatusNot30 AS S2

Open in new window

0
 
Mark ElySenior Coldfusion DeveloperCommented:
Based on your example I made a few assumptions.  1) a status checks is done every 5 seconds 2) there is only one machine (otherwise you would add group by machine), 3) The actual time does not matter as much as the number of times it was not at status equal to 30.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mark ElySenior Coldfusion DeveloperCommented:
Ops.. for some reason I was thinking seconds.  change my wording from seconds to minutes.
0
 
Mark ElySenior Coldfusion DeveloperCommented:
I assumed there were only two status states.  However the same principle would apply if you group by status then did a count of the entries within that group ...

SELECT TOP 1 Status, Machine, (COUNT(Status)*5) AS TimeAtStatus
FROM NameOfYourTable
GROUP BY Status, Machine
0
 
Mark ElySenior Coldfusion DeveloperCommented:
SELECT TOP 1 Status, Machine, (COUNT(Status)*5) AS TimeAtStatus, MAX(DateTime) AS LastTime
FROM machinelog
GROUP BY Status, Machine
ORDER BY LastTime DESC
0
 
NettieEckAuthor Commented:
Perfect.  Thanks for your help.  That is exactly what I was looking for.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.