>if the strings was like under how could be the SQL syntax calculating the times between these times ?
>
>05/09/2002 14:53 PM Start 1 M2A|M4D|M6A|M8A|M0A
>05/09/2002 14:54 PM Start 1 M2A|M4D|M6A|M8A|M0A|M1A|M3D|M5A|M7A|M9A
>05/09/2002 14:58 PM Start 1 M2D|M3D|M5A|M7A|M9A

hello soykan -

from looking at your sample data, i make these assumptions and
have the following questions:

- your serial input arrives at random times
- you want to calculate running times (probably minutes or hours)
  and a few seconds one way or the other doesn't matter
- you have some sort of serial "network" ??
- not every machine reports in every time ??

you can certainly build a table of the records you show, but
it will be REALLY difficult to do any calculations with the
data in that format. you might want to retain the data in that
format, for historical reasons. a more useful form would
be to parse out the info for each machine from each of these
records and make a separate record for each machine/time-entry
combination (and indexed on time).

and by the way -- when you are parsing out the info, convert
the 12 hour time format to 24 hour format.
(or even better, convert it into a format representing
"number of seconds since a certain date" to make your later
calculations really easy).

in other words, starting with your sample, you will end up
with something like this:

05/09/2002 14:53 M2A
05/09/2002 14:53 M4D
05/09/2002 14:53 M6A
05/09/2002 14:53 M8A
05/09/2002 14:53 M0A
05/09/2002 14:54 M2A
05/09/2002 14:54 M4D
05/09/2002 14:54 M6A
05/09/2002 14:54 M8A
05/09/2002 14:54 M0A
05/09/2002 14:54 M1A
05/09/2002 14:54 M3D
05/09/2002 14:54 M5A
05/09/2002 14:54 M7A
05/09/2002 14:54 M9A
05/09/2002 14:58 M2D
05/09/2002 14:58 M3D
05/09/2002 14:58 M5A
05/09/2002 14:58 M7A
05/09/2002 14:58 M9A

now, for each machine, you can scan through that table
and reduce the entries to just the running/stopped (or stopped/running)
transitions. in your sample data, M2 is the only machine
that makes a running/stopped transition. in your sample data, all
other machines are already running and continue to run or are
already stopped and remain stopped. (during the time the data
was collected).

M0A 05/09/2002 14:53
M1A 05/09/2002 14:54
M2A 05/09/2002 14:53
M2D 05/09/2002 14:58
M3D 05/09/2002 14:54
M4D 05/09/2002 14:53
M5A 05/09/2002 14:54
M6A 05/09/2002 14:53
M7A 05/09/2002 14:54
M8A 05/09/2002 14:53
M9A 05/09/2002 14:54

you could make the calculations without creating this intermediate
table, but this seems to be info that would be of interest to
a production manager, especially if you made a report grouped by
machine.

anyhow, from this, you can easily make (or update) another table, with
only one record per machine, with the total running time
(and optionally total down-time). you could have multiple entries
per machine if you want to break it down by day or week or month, etc.

hope that helps.