By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

How to get the production downtime I need from "ALARM Database"?

P: 13
Database is simliar to this:
5967383 8/1/2008 1:00:24 AM I16MUDL11 ALARM
5967384 8/1/2008 1:00:24 AM I16MUDL09 ALARM
5967391 8/1/2008 1:00:32 AM I16MUDL11 A_OK
5967392 8/1/2008 1:00:32 AM I16MUDL09 A_OK
5967398 8/1/2008 1:00:36 AM I16MUDL11 ALARM
5967406 8/1/2008 1:01:10 AM I16MUDL11 A_OK
5967407 8/1/2008 1:01:10 AM I16MUDL09 A_OK

If the value equals "ALARM", the machine will break down until the corresponding "A_OK" appears. Each ALARM and A_OK have a "TAGNAME" that indicates different parts of the machine.


We want to find out how much time we lose in producition due to the breakdown of different parts. So we need to calculate the time between each "ALARM" and "A_OK" for each different "TAGNAME".

The database is huge, containing about 600,000 entries.

I need:
TagName Frequency Total Downtime

It's driving me crazy. Please please give me a helping hand.
Attached Images
File Type: jpg database question.JPG (34.0 KB, 201 views)
Dec 18 '08 #1
Share this Question
Share on Google+
8 Replies

Expert 100+
P: 1,134
Working out a possible query.
What is the criteria for frequency>
Per day?
Per Week?
Per Month??
Per Year??
other ????
Dec 18 '08 #2

Expert 100+
P: 1,134
This is straight out of my head and without data I can't test it.
Something like this
Expand|Select|Wrap|Line Numbers
  1. SELECT TagName,DateDiff(mm,Down,Up) as MinutesDown
  2. FROM
  3. (  SELECT a.TagName,a.[Time] as Down,
  4.              (SELECT min([Time])
  5.               FROM TheTable b
  6.               WHERE b.[Value]='A_OK' 
  7.                  and b.TagName=a.TagName
  8.                  and b.Time>a.Time
  9.               ) as Up
  10.    FROM TheTable a
  11.    WHERE [Value]='ALARM'
  12. )z
If that runs slow you could try
1) Adding an index to TheTable for TagName and Time together
2) Run the query each night as a DTS into a table and build your reports from that.

Good luck
I'm here for any questions!
Dec 18 '08 #3

P: 13
Thank you, Delerna.

I am trying your query. Hope it works.

Actually I shouldn't say "Frequency", what I want is simply the total times each machine part, which is "Tagname", breakdown.
Dec 19 '08 #4

P: 13
Sorry, I can't get it work. I attach part of the record and hope you could play it around and continue to help me out.

Attached Files
File Type: txt machine alarm.txt (22.0 KB, 342 views)
Dec 19 '08 #5

Expert 100+
P: 1,134
oops my bad

Try this
Expand|Select|Wrap|Line Numbers
  1. SELECT ID,TagName,Down,Up,DateDiff(mi,Down,Up) as MinutesDown
  2. FROM 
  3. (  SELECT a.ID,a.TagName,a.[Time] as Down, 
  4.              (SELECT min([Time]) 
  5.               FROM MachineAlarm b 
  6.               WHERE b.[Value]='A_OK'  
  7.                  and b.ID>a.ID
  8.                  and b.TagName=a.TagName 
  9.                  and b.Time>=a.Time 
  10.               ) as Up
  11.    FROM MachineAlarm a 
  12.    WHERE [Value]='ALARM' 
  13. )z 
I added some extra conditions to the where clause of the subquery

and b.Time>=a.Time because a tagname can go down and back up in less than one second

and b.ID>a.ID because a taganame can go down and back up again multiple times in the same second.

Also the datediff parameter for minutes is mi not mm.
mm is months
Dec 21 '08 #6

P: 13
I try your updated code again and receive this error.

I think your algorithm is very good and in the right way. But it just didn't work in my computer. I am using Access 2003. Does that make any difference?
Attached Images
File Type: jpg error.JPG (14.4 KB, 127 views)
Dec 22 '08 #7

Expert 100+
P: 1,134
Yes it might, I have given you SQLServer2000 code because you have posted into the SQL Server thread.

Having said that the syntax for SQL in SQLSever and Access are very similar and I know the query I have given you works in SQLServer because I have tested it on your sample data.

Let me fire up access and see what I can come up with.
Dec 22 '08 #8

Expert 100+
P: 1,134
In Access DateDiff uses n for minutes and it has to be in quotes
Also, try putting your table name in square brackets instead of quotes

Try this
Expand|Select|Wrap|Line Numbers
  1. SELECT ID, TagName, Down, Up, DateDiff('n',[Down],[Up]) AS MinutesDown
  2. FROM (SELECT a.ID,a.TagName,a.[Time] as Down,  
  3.              (SELECT min([Time])  
  4.               FROM [MachineAlarm] b  
  5.               WHERE b.[Value]='A_OK'   
  6.                  and b.ID>a.ID 
  7.                  and b.TagName=a.TagName  
  8.                  and b.Time>=a.Time  
  9.               ) as Up 
  10.    FROM MachineAlarm a  
  11.    WHERE [Value]='ALARM'  
  12. ) z
This worked on my machine on your data in access 2003
Dec 22 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.