Dennis Lee Bieber
1/3/2008 6:20:00 AM
On Wed, 2 Jan 2008 14:11:07 -0500, "Israel Carr" <icarr@compx.com>
declaimed the following in comp.lang.python:
> Here is a small sample of the records in the table:
>
> name date time status
> machine1 01/01/2008 13:00:00 system ok
> machine1 01/01/2008 13:05:00 system ok
> machine1 01/01/2008 13:10:00 status1
> machine1 01/01/2008 13:10:30 status1
> machine1 01/01/2008 13:11:00 system ok
> machine1 01/01/2008 13:16:30 status2
> machine1 01/01/2008 13:17:00 status2
> machine1 01/01/2008 13:17:30 status2
> machine1 01/01/2008 13:18:00 status2
> machine1 01/01/2008 13:18:30 status2
> machine1 01/01/2008 13:19:00 system ok
> machine1 01/01/2008 13:24:00 status2
> machine1 01/01/2008 13:24:30 status2
> machine1 01/01/2008 13:25:00 system ok
>
> I need to report from this data.
> The detail report needs to be something like:
> machine1 01/01/2008 13:10:00 status1 00:01:30
> machine1 01/01/2008 13:16:30 status2 00:02:30
> machine1 01/01/2008 13:24:00 status2 00:01:00
>
How are you calculating that final column?
13:11:00 - 13:10:00 => 00:01:00
13:11:00 - 13:05:00 => 00:06:00
13:10:30 - 13:05:00 => 00:04:30
>
> I'm not sure how to query when a 'bad' status is found to find the next
> 'good' status and calculate based on the times. Essentially, I need
> help creating the reports mentioned above. Your questions may also help
> clarify my fuzzy description.
>
It sounds very much as if you are attempting to use the status field
as a control break for the report. Unfortunately, you aren't doing
something simple -- like adding up a value at each record into a total;
instead you want to take a value /at/ the control break, and difference
it with the value from the start of the control group. And if you are
only concerned with "machine1", you don't need to waste time returning
the name field...
I'd suggest just coding the control break handling in Python itself,
working directly from your first data sample. Though are you sure that
date and time were actually stored as separate fields? It's much easier
if they are a single datetime entity in the database AND get converted
to a datetime type by the DB-API being used.
-=-=-=-=- pseudo-code/untested
totals = {}
last_time = None
last_status = None
machine = "machine1"
#use the current placeholder for your db-api
cur.execute("""select datetime, status from some_table
where name = ?
order by datetime""",
(machine,) )
for (dt, st) in cur:
if st != last_status:
if last_status and last_time:
print "%30s %20s %10s %10s" % (machine,
last_time,
last_status
dt - last_time)
totals[last_status] = (totals.get(last_status, 0.0)
+ (dt - last_time) )
last_status = st
last_time = dt
#handle last record? has to be same status or above would trigger
if last_time and (dt != last_time):
print "%30s %20s %10s %10s" % (machine,
last_time,
last_status
dt - last_time)
totals[last_status] = (totals.get(last_status, 0.0)
+ (dt - last_time) )
#print summary
for st in totals:
print "..." % (machine, st, totals[st])
-=-=-=-=-=-=-
Now, after all this, if you think it is too slow, you might want to
look into coding the computation loop as a PostgreSQL (or are you really
using the ancient precursor Postgres?) stored procedure. I don't know
enough about stored procedures to know how to set up a multiple record
computed return (and the books I checked at work used examples that only
did a single record operation). May you have to create a temporary table
for the results, do the computation (replace the prints with an INSERT
INTO temptable...), and then end the stored procedure with a SELECT *
from temptable... and a DROP TABLE temptable.
--
Wulfraed Dennis Lee Bieber KD6MOG
wlfraed@ix.netcom.com wulfraed@bestiaria.com
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-asst@bestiaria.com)
HTTP://www.bestiaria.com/