Sum working hours

  • Thread starter Thread starter thomas
  • Start date Start date
T

thomas

Hi !

I need to make a chart for statistics of machines.

Table:
MachineID: 43
Starttime: dd.mm.yy hh:nn
Endtime: dd.mm.yy hh:nn

What I need is a query which tells me for each machine how many hours a
day have been worked and how many not.
Looks easy but isnt.
The intervall between start and end can be from hours to several days.
24h a day => 18h worked, 6 h not working

Thanks Thomas
 
Hi,


SELECT MachineID, 24*SUM(EndTime-StartTime) AS upTime,
24*(Max(EndTime)-MIN(StartTime)-SUM(EndTime-StartTime)) AS downTime
FROM myTable
GROUP BY MachineID




24* is used to convert days (and decimal portion) into hours.

EndTime-StartTime gives the uptime for a record
SUM() of the previous gives the uptime for all records, given a MachineID
MIN(StartTime) gives the earliest considered starting time,
MAX(EndTime) gives the latest mentioned time, for a machineID

and, if there are more than one record for a given machineID, the MIN and
the MAX do not occur in the same record.

Clearly, MAX-MIN returns the total time where the machine is involved, so,
subtract its upTime from it to get its downTime.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top