Calculating Mfg Machine Downtime - Please Help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Calculating Mfg Machine Downtime - Please Help

Author

Topic

hamiltm
Starting Member

3 Posts

Posted - 2011-10-11 : 16:48:04

I am having trouble calculating a manufacturing machine’s downtime from a production run table. I have been working on this query for several days and feel like I may be overcomplicating it.
Here is what the table (ProductionRun) looks like.

StartTime - DateTime
StopTime - DateTime
MachineID - Int
PONumber - Int
PKeyID - Int Primary Key AutoIncrement
UpTimeMinutes - Calculated as datediff returning minutes between stoptime and starttime

The software that writes the records writes a new record with a StartTime, MachineID, and PONumber when the machine is started. StopTime (and therefore the UpTimeMinutes) is NULL until the machine is stopped. It then updates the record with a StopTime when the machine is stopped.

I am going to use a stored procedure that the software can execute by supplying a @MachineID which will then return back the total downtime for a supplied machineID and current shift.
1st Shift – 6:00 AM – 4:10 PM
2nd shift – 4:30 AM – 2:40 AM

The part I am having the biggest trouble with is that downtime during a changeover (determined by PONumber changing from one record to the next for that machine) doesn’t count as downtime. The other part I am struggling with is how to take into a account the last record for a machine that has a null value for stop time.

Multiple machines will be reporting to the table so the next record according to PKeyID may not necessarily be the next record for a specific machine.

So as a summary I need the downtime from 6:00 AM of the current day up through the current time but taking into account that I should not count downtime that occurs during a PONumber change.

The path I was approaching it with was using nested queries to take 570 (number of minutes in the shift) - 30 (minutes for lunch) - (some complex query to determine the number of minutes the machine was down caused by a changeover) - (sum of uptime minutes for that machine and shift) = downtime in minutes

I really hope this makes sense.

2011-10-10 02:00:00.000 2011-10-10 02:40:00.000 3 100473500 645 40.00000
2011-10-11 06:42:43.730 2011-10-11 07:42:59.570 3 100473608 646 0.266666 <--- The 42 minutes between start of shift and the machine starting for the first time should not count since the POnumber was a change from the night before.
2011-10-11 07:43:36.487 2011-10-11 07:44:10.307 3 100473608 647 0.566666
2011-10-11 07:52:17.640 2011-10-11 07:52:37.760 3 100473608 648 0.333333
2011-10-11 08:36:34.630 2011-10-11 09:04:09.700 3 100473608 649 27.583333
2011-10-11 09:04:18.273 2011-10-11 09:23:56.087 3 100473608 650 19.633333
2011-10-11 09:27:22.650 2011-10-11 09:30:32.360 3 100473608 651 3.166666
2011-10-11 09:31:49.407 2011-10-11 09:38:45.380 3 100473608 652 6.933333
2011-10-11 09:40:02.193 2011-10-11 09:40:45.373 3 100473608 653 0.716666
2011-10-11 09:41:15.713 2011-10-11 09:41:17.883 3 100473608 654 0.033333
2011-10-11 09:41:21.710 2011-10-11 10:08:22.833 3 100473608 655 27.016666
2011-10-11 10:09:33.197 2011-10-11 10:12:48.330 3 100473608 656 3.250000
2011-10-11 10:26:46.427 2011-10-11 10:27:11.327 3 100477510 657 0.416666 <----- The downtime between this record and the record above it should not count since the PONumber changed
2011-10-11 10:27:15.700 2011-10-11 11:01:20.773 3 100477510 658 34.083333
2011-10-11 11:05:14.370 2011-10-11 11:07:58.780 3 100477510 659 2.733333
2011-10-11 11:09:38.637 2011-10-11 11:17:55.543 3 100477510 660 8.283333
2011-10-11 11:19:12.387 2011-10-11 11:23:35.823 3 100477510 661 4.383333
2011-10-11 11:25:21.647 2011-10-11 11:55:31.610 3 100477510 662 30.166666
2011-10-11 11:56:13.943 NULL 3 100473608 663 NULL

I need the query to return a single value, being the number of minutes that the machine was not running. Like I said this will be going in a Stored Procedure (which I don't have much experience with) so if it would be easier to do things as multiple queries and do math outside of the queries to figure this out it can be done that way.

This sample data doesn't take into account multiple machines as we are currently only testing with one machine. The good news is that we have the luxury of not working on a production system right now as we are just implementing the software and database.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-11 : 16:53:12

Supply sample data with the PO change, and illustrate the results you want.

Thanks

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

hamiltm
Starting Member

3 Posts

Posted - 2011-10-11 : 17:11:13

Supplied sample data and more info in original post. Sorry, this is my first time posting. I have been working with SQL for a few years but this query has more complex criteria than I am use to dealing with and I am having a hard time wrapping my brain around it.

hamiltm
Starting Member

3 Posts

Posted - 2011-10-11 : 17:56:09

I realized during my last edit that I can do the basic steps in a stored procedure, therefore it doesn't have to be a single query. I will try this and post back if I have further problems. Sorry I just like to try to solve problems myself and don't want to waste anybodies time if I can get it myself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources