# 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
###### Who is Participating?

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.

Senior 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
0
Senior 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
Senior Coldfusion DeveloperCommented:
Ops.. for some reason I was thinking seconds.  change my wording from seconds to minutes.
0
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

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

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
0

Experts Exchange Solution brought to you by

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

Senior 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
Senior 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
Author 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.