The problem I am struggling with is I have a database of batch jobs,
very simple.

JOBNAME,START TIME, FINISH TIME

JOB1STOP;1/1/7 9:00:00;1/1/7 9:00:00
....
JOB1STOP;1/1/7 9:01:00;1/1/7 9:07:00
....
JOB1START;1/1/7 12:34:01;1/1/7 12:54:03
....
JOB1START;1/1/7 12:37:01;1/1/7 12:59:03

The reality of this is The JOB1STOP begins a database down for
maintenance and JOB1START begins it back up.

The second JOB1STOP doesn't matter (it's already down) nor does the
second JOB1START (it's already up) so I am guessing that a query in
psuedo-code would look like (***uming we want just jobs from 1/1/7:

SELECT (jobname,start time, finish time) from JOBTABLE where STARTTIME
like "1/1/7" AND JOBNAME like "START" or "STOP"

But then I have to figure out how to clip only the first STOP and the
FIRST START ignoring the duplicates. (distinct perhaps?)

Depending on the server the logs are from means I would invert the
criteria needing only the LAST of a given record. (order by prior to
distinct?)

I am not very good with complex SQL queries, but my best guess to this
point is (using $vars)

SELECT (jobname,start_time,end_time) from $TABLE
WHERE start_time >= "1/1/7" AND
jobname LIKE ("START" OR "STOP")
ORDER BY start_time DISTINCT

Am I close? and How could I ignore the query if one of the two are not
present (ignore STARTS or STOPS on a day that the other is missing?)

Id..

Idgarad wrote:
> The problem I am struggling with is I have a database of batch jobs,
> very simple.
>
> JOBNAME,START TIME, FINISH TIME
>
> JOB1STOP;1/1/7 9:00:00;1/1/7 9:00:00
> ...
> JOB1STOP;1/1/7 9:01:00;1/1/7 9:07:00
> ...
> JOB1START;1/1/7 12:34:01;1/1/7 12:54:03
> ...
> JOB1START;1/1/7 12:37:01;1/1/7 12:59:03
>
> The reality of this is The JOB1STOP begins a database down for
> maintenance and JOB1START begins it back up.
>
> The second JOB1STOP doesn't matter (it's already down) nor does the
> second JOB1START (it's already up) so I am guessing that a query in
> psuedo-code would look like (***uming we want just jobs from 1/1/7:
>
> SELECT (jobname,start time, finish time) from JOBTABLE where STARTTIME
> like "1/1/7" AND JOBNAME like "START" or "STOP"
>
> But then I have to figure out how to clip only the first STOP and the
> FIRST START ignoring the duplicates. (distinct perhaps?)
>
> Depending on the server the logs are from means I would invert the
> criteria needing only the LAST of a given record. (order by prior to
> distinct?)
>
> I am not very good with complex SQL queries, but my best guess to this
> point is (using $vars)
>
> SELECT (jobname,start_time,end_time) from $TABLE
> WHERE start_time >= "1/1/7" AND
> jobname LIKE ("START" OR "STOP")
> ORDER BY start_time DISTINCT
>
> Am I close? and How could I ignore the query if one of the two are not
> present (ignore STARTS or STOPS on a day that the other is missing?)
>
> Id..

You need what I call the "Strawberry Query"
See the pattern for it in my response on this thread:
http://tinyurl.com/yex7s2