468,242 Members | 1,575 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

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

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, 246 views)
Dec 18 '08 #1
8 2459
1,134 Expert 1GB
Working out a possible query.
What is the criteria for frequency>
Per day?
Per Week?
Per Month??
Per Year??
other ????
Dec 18 '08 #2
1,134 Expert 1GB
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
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
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, 367 views)
Dec 19 '08 #5
1,134 Expert 1GB
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
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, 142 views)
Dec 22 '08 #7
1,134 Expert 1GB
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
1,134 Expert 1GB
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.

Similar topics

4 posts views Thread by (Pete Cresswell) | last post: by
33 posts views Thread by bill | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.