Cycle time query

[Newbie_nick]

I have a database that stores machine cycle information for multiple machines
in my plant, and I would like to generate a query to calculate individual
cycle times for each cycle for each machine. A sample data table is below:

EMPLOYEE DATE_RECORDED MACHINE_ID PROCESS_NAME
1 1/14/09 6:55 A PROCESS_CYCLE_COMPLETE
4 1/14/09 6:55 B PROCESS_CYCLE_COMPLETE
8 1/14/09 6:55 C PROCESS_CYCLE_COMPLETE
9 1/14/09 7:28 D PROCESS_CYCLE_COMPLETE
1 1/14/09 7:29 A PROCESS_CYCLE_COMPLETE
4 1/14/09 7:29 B PROCESS_CYCLE_COMPLETE
8 1/14/09 7:30 C PROCESS_CYCLE_COMPLETE
9 1/14/09 7:31 D PROCESS_CYCLE_COMPLETE
1 1/14/09 7:35 A PROCESS_CYCLE_COMPLETE
4 1/14/09 7:37 B PROCESS_CYCLE_COMPLETE
8 1/14/09 7:38 C PROCESS_CYCLE_COMPLETE
9 1/14/09 7:39 D PROCESS_CYCLE_COMPLETE

Thanks for the help!
Nick

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this:

SELECT machine_id, date_recorded,
DateDiff("n",(SELECT MAX(date_recorded) FROM machine_cycles
WHERE machine_id = M.machine_id
AND date_recorded < M.date_recorded), date_recorded) As
duration_nins
FROM machine_cycles As M
ORDER BY machine_id, date_recorded

The duration of the first instance of each machine will not have a
duration because there isn't a starting time.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSXevcIechKqOuFEgEQLEhgCg5wt8xUUeG54JoyqVR4AqLk1XFbUAn0BR
5KlYMMVZxWfBzl7Pc+UeWVWR
=O9si
-----END PGP SIGNATURE-----

 

[Newbie_nick]

That worked, thank you!

Can you explain through your logic? I can't figure out what it's actually
doing.

Thanks again!
Nick

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I used the DateDiff() function to find the number of minutes between the
"current" record's time and the "previous" time. The way I found the
previous time was to SELECT the latest time (MAX) that was prior to
(less than "<") the current record's time.


This subquery finds the time immediately before the current time:

SELECT MAX(date_recorded) FROM machine_cycles
WHERE machine_id = M.machine_id
AND date_recorded < M.date_recorded), date_recorded

I just plugged it into the DateDiff() function and used the current time
as the other time parameter.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSXoqIoechKqOuFEgEQJKzwCgxrqtV5Zt6qIgnRFiGAqyvd1JaV8AoLd1
9fiWWf92alVxVceq2BGUfKkF
=2Jqw
-----END PGP SIGNATURE-----

 

[jutlaux]

I have setup a similar query using your thought process with the SQL shown
below. The problem I have is that when I run the Query the DowntimeCalc field
is empty for all records. What am I missing?

Thanks

SELECT CURE_START_DATE_TIME, PRESS_NAME, DateDiff("s",(SELECT
MAX(CURE_START_DATE_TIME) FROM tblTemporary2 WHERE PRESS_NAME=M.PRESS_NAME
AND CURE_START_DATE_TIME<M.CURE_START_DATE_TIME),[CURE_START_DATE_TIME]) AS
DowntimeCalc
FROM tblTemporary2 AS M;