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.
LVL 1
NettieEckAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Mark ElySenior Coldfusion DeveloperCommented:
Ops.. for some reason I was thinking seconds.  change my wording from seconds to minutes.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.