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

[superleochen]

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.

[Delerna]

Working out a possible query.
What is the criteria for frequency>
Per day?
Per Week?
Per Month??
Per Year??
other ????

[Delerna]

This is straight out of my head and without data I can't test it.
Something like this

SELECT TagName,DateDiff(mm,Down,Up) as MinutesDown
FROM
(  SELECT a.TagName,a.[Time] as Down,
             (SELECT min([Time])
              FROM TheTable b
              WHERE b.[Value]='A_OK' 
                 and b.TagName=a.TagName
                 and b.Time>a.Time
              ) as Up
   FROM TheTable a
   WHERE [Value]='ALARM'
)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!

[superleochen]

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.

[superleochen]

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

[Delerna]

oops my bad

Try this

SELECT ID,TagName,Down,Up,DateDiff(mi,Down,Up) as MinutesDown
FROM 
(  SELECT a.ID,a.TagName,a.[Time] as Down, 
             (SELECT min([Time]) 
              FROM MachineAlarm b 
              WHERE b.[Value]='A_OK'  
                 and b.ID>a.ID
                 and b.TagName=a.TagName 
                 and b.Time>=a.Time 
              ) as Up
   FROM MachineAlarm a 
   WHERE [Value]='ALARM' 
)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

[superleochen]

@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?

[Delerna]

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.

[Delerna]

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

SELECT ID, TagName, Down, Up, DateDiff('n',[Down],[Up]) AS MinutesDown
FROM (SELECT a.ID,a.TagName,a.[Time] as Down,  
             (SELECT min([Time])  
              FROM [MachineAlarm] b  
              WHERE b.[Value]='A_OK'   
                 and b.ID>a.ID 
                 and b.TagName=a.TagName  
                 and b.Time>=a.Time  
              ) as Up 
   FROM MachineAlarm a  
   WHERE [Value]='ALARM'  
) z
 

This worked on my machine on your data in access 2003