Adding up time

[chr1stoper1]

Hi Guys

Im creating a database which has four tables .1.Machine hourly rate 2. OEE Downtime 3. Tooling 4.Part cost
I upload a daily report into table 2. Which has feilds which has number of stoppages and the downtime of the machine as well as a number of other things.


I would like to add up the time the machine has been inactive .
Any ideas would be much appreciated

[CJ_London]

without knowing your table structure, impossible to advise. suggest you provide some example data in a table format (i.e. don't describe in text) and the result you require for that sample data

[chr1stoper1]

Heres the table and the primary key is the id field.
My main interest at the moment is with the field NO which is the number of times a machine has stopped in 24 hours and the field stop which is minutes the machine was not in production. I would like to add the downtime into hoors and days

[CJ_London]

you have not provided an example of the required outcome and there are some inconsistencies in your requirement

[QUOTE]a machine has stopped in 24 hours[/QUOTE]you only have an amount of time stopped and a date in your data, so can only be stopped in a day (which is not the same as 24 hours which could be from midday to midday)

I would like to add the downtime into hoors and days

since the downtime cannot be more than a day for any given day, don't understand where the days come from

suggest something like this to get you going

SELECT Machine, Date, sum(No) as timesStop, sum(timevalue(Stop)) as downtime
FROM tblOEE
WHERE NO<>0
GROUP BY Machine, Date

note that Date and No are reserved words - so using them as a field name can cause unexpected errors with misleading error messages

[chr1stoper1]

Thankyou for your reply
[I wish to query machine and tool and return the ammount of stoppages and amount of time due to stoppages between dates . The issues I have is summing doesnt work.
I know the Date and No can be an issue but that is the way the report is worded ]

[CJ_London]

sorry your requirement seems to have changed and is still not clear - provide an example of the required output from the sample data you uploaded.

The issues I have is summing doesnt work.

'doesn't work' does not help me to help you. What does it mean? Did you try the example sql I provided? what is the actual sql you used? If you cannot be clear in your requirements then I cannot help you

[ssanfu]

My main interest at the moment is with the field NO which is the number of times a machine has stopped in 24 hours and the field stop which is minutes the machine was not in production.

A major problem that I see is that column F (spreadsheets have columns, databases have fields) "Stop" has TIMEs, NOT Durations.

For example, ID 452 (Row 450) in the Stop column, the value is 15:32. If you click in that cell and look at the formula bar, you will see 3:32:00 PM. If the down time duration is 15 hours and 32 minutes, you would enter "15h 32m" or 15 hours and 32 minutes or 15.5334 hours (32/60 of an hour= 0.533333333333_).

At ID 24 (row 23), the value in Cell F23 is 3:12 (3:12:00 AM). If the machine was down for 3 hours and 12 minutes, you would enter 3 hours and 12 minutes or 3.2 hours.

If a machine was down for more that a day, say 25 hours and 12 minutes, you could not write it as 25:12.
You could write it as
25 hours and 12 minutes
or 1 day, 1 hour and 12 minutes
or 1 day and 1.2 hours
or 25.2 hours.