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:
ID TIME TAGNAME VALUE
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



Description:
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.

Goal:

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".

Misc.
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


Delerna
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

Delerna
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
  13.  
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
@Delerna
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.

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

Delerna
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 
  14.  
  15.  
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
@Delerna
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

Delerna
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

Delerna
Expert 100+
P: 1,134
OK
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
  13.  
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.