I desparately need with this problem in constructing the proper
query that can pull out the system downtime from a log based on batch
jobs.

There data is structured as follows:

JOB NAME, START DATE, START TIME, END DATE, END TIME
------------------------------------------------------------------------------------------------
DownJob1,10/10/07,1:00 AM, 10/10/07, 1:34 AM
UpJob1,10/10/07, 2:45 PM, 10/10/07 3:00 PM
DownJob2,10/10/07,3:00 AM, 10/10/07. 1:00 AM
UpJob2,10/10/07, 2:00 PM, 10/10/07, 2:10 PM
....
DownJob23,10/10/07,12:58 AM, 10/10/07, 12:58 PM
UpJob23,10/11/07, 2:45 AM, 10/11/07, 3:45 AM

Now there are a wide variety of jobs in the log but I have a list of
jobs that cause the system to go down. They are in a table called
DOWNJOBS.

I have a list of jobs that cause the system to become available in a
table called UPJOBS.

Consecutive down jobs are meaningless as the system is already down,
subsequent UP jobs are also useless.

Here is an example of the hard part

DownJob1 starts at 1:00 AM and UpJob1 finishes at 3:00 PM
DownJob2 starts at 3:00 AM and UpJob2 finishes at 2:10 PM

I just want to report back blocks of downtime so this block of
downtime is actually from 1:00 AM to 2:10 PM (You can see the
overlapping problem now I hope) and I cannot for the life of me get my
head around how to get this data.

A similar problem is I need to know how long a Down\Up set lasted so
for Job1 I need to know how long from the start of the first DownJob1
(for example, it could technically run multiple times in a row) to the
end time of UpJob1.

More importantly based on both the above I need to be able to, when
it's all said and done find out the down time on a given day (which is
complicated by the fact that if a job spans a day I'd have to
effectivly break it in two. Some jobs could span weeks depending on
the time of year.)

I am guessing this is going to involve subqueries but I have never
used one. Any ideas on how to solve these?

An actual sample is posted below: (STPM (down) and STAM (start) pair;
as is a STP1 and STA1 are also a pair)

XXXXXSTPM 10/7/2007 19:00:37 10/7/2007 19:04:28
XXXXXSTPM 10/7/2007 20:02:10 10/7/2007 20:05:49
XXXXXSTPM 10/7/2007 21:15:05 10/7/2007 21:15:29
XXXXXSTAM 10/7/2007 21:20:38 10/7/2007 21:20:58
XXXXXSTP1 10/8/2007 4:00:08 10/8/2007 4:12:18
XXXXXSTA1 10/8/2007 4:32:57 10/8/2007 4:45:04
XXXXXSTP1 10/9/2007 4:00:06 10/9/2007 4:12:38
XXXXXSTA1 10/9/2007 4:33:08 10/9/2007 4:45:12
XXXXXSTPM 10/9/2007 19:00:47 10/9/2007 19:01:23
XXXXXSTAM 10/9/2007 19:09:08 10/9/2007 19:09:29
XXXXXSTP1 10/10/2007 4:00:10 10/10/2007 4:14:05
XXXXXSTA1 10/10/2007 4:37:38 10/10/2007 4:49:44
XXXXXSTPM 10/10/2007 19:01:15 10/10/2007 19:04:35
XXXXXSTAM 10/10/2007 19:10:17 10/10/2007 19:10:37
XXXXXSTP1 10/11/2007 4:00:09 10/11/2007 4:13:46
XXXXXSTA1 10/11/2007 4:37:48 10/11/2007 4:49:58
XXXXXSTPM 10/11/2007 19:00:50 10/11/2007 19:01:19
XXXXXSTAM 10/11/2007 19:08:33 10/11/2007 19:08:54
XXXXXSTP1 10/12/2007 4:00:04 10/12/2007 4:14:12
XXXXXSTA1 10/12/2007 4:35:56 10/12/2007 4:48:07
XXXXXSTPM 10/12/2007 19:00:54 10/12/2007 19:04:37
XXXXXSTAM 10/12/2007 19:09:31 10/12/2007 19:09:52
XXXXXSTPM 10/13/2007 19:00:19 10/13/2007 19:00:42
XXXXXSTAM 10/13/2007 19:05:39 10/13/2007 19:05:59
XXXXXSTP1 10/13/2007 21:00:03 10/13/2007 21:12:39

On 17 Oct, 14:21, Idgarad <idga...@gmail.com> wrote:
> I desparately need with this problem in constructing the proper
> query that can pull out the system downtime from a log based on batch
> jobs.
>
> There data is structured as follows:
>
> JOB NAME, START DATE, START TIME, END DATE, END TIME
> ------------------------------------------------------------------------------------------------
> DownJob1,10/10/07,1:00 AM, 10/10/07, 1:34 AM
> UpJob1,10/10/07, 2:45 PM, 10/10/07 3:00 PM
> DownJob2,10/10/07,3:00 AM, 10/10/07. 1:00 AM
> UpJob2,10/10/07, 2:00 PM, 10/10/07, 2:10 PM
> ...
> DownJob23,10/10/07,12:58 AM, 10/10/07, 12:58 PM
> UpJob23,10/11/07, 2:45 AM, 10/11/07, 3:45 AM
>
> Now there are a wide variety of jobs in the log but I have a list of
> jobs that cause the system to go down. They are in a table called
> DOWNJOBS.
>
> I have a list of jobs that cause the system to become available in a
> table called UPJOBS.
>
> Consecutive down jobs are meaningless as the system is already down,
> subsequent UP jobs are also useless.
>
> Here is an example of the hard part
>
> DownJob1 starts at 1:00 AM and UpJob1 finishes at 3:00 PM
> DownJob2 starts at 3:00 AM and UpJob2 finishes at 2:10 PM
>
> I just want to report back blocks of downtime so this block of
> downtime is actually from 1:00 AM to 2:10 PM (You can see the
> overlapping problem now I hope) and I cannot for the life of me get my
> head around how to get this data.
>
> A similar problem is I need to know how long a Down\Up set lasted so
> for Job1 I need to know how long from the start of the first DownJob1
> (for example, it could technically run multiple times in a row) to the
> end time of UpJob1.
>
> More importantly based on both the above I need to be able to, when
> it's all said and done find out the down time on a given day (which is
> complicated by the fact that if a job spans a day I'd have to
> effectivly break it in two. Some jobs could span weeks depending on
> the time of year.)
>
> I am guessing this is going to involve subqueries but I have never
> used one. Any ideas on how to solve these?
>
> An actual sample is posted below: (STPM (down) and STAM (start) pair;
> as is a STP1 and STA1 are also a pair)
>
> XXXXXSTPM 10/7/2007 19:00:37 10/7/2007 19:04:28
> XXXXXSTPM 10/7/2007 20:02:10 10/7/2007 20:05:49
> XXXXXSTPM 10/7/2007 21:15:05 10/7/2007 21:15:29
> XXXXXSTAM 10/7/2007 21:20:38 10/7/2007 21:20:58
> XXXXXSTP1 10/8/2007 4:00:08 10/8/2007 4:12:18
> XXXXXSTA1 10/8/2007 4:32:57 10/8/2007 4:45:04
> XXXXXSTP1 10/9/2007 4:00:06 10/9/2007 4:12:38
> XXXXXSTA1 10/9/2007 4:33:08 10/9/2007 4:45:12
> XXXXXSTPM 10/9/2007 19:00:47 10/9/2007 19:01:23
> XXXXXSTAM 10/9/2007 19:09:08 10/9/2007 19:09:29
> XXXXXSTP1 10/10/2007 4:00:10 10/10/2007 4:14:05
> XXXXXSTA1 10/10/2007 4:37:38 10/10/2007 4:49:44
> XXXXXSTPM 10/10/2007 19:01:15 10/10/2007 19:04:35
> XXXXXSTAM 10/10/2007 19:10:17 10/10/2007 19:10:37
> XXXXXSTP1 10/11/2007 4:00:09 10/11/2007 4:13:46
> XXXXXSTA1 10/11/2007 4:37:48 10/11/2007 4:49:58
> XXXXXSTPM 10/11/2007 19:00:50 10/11/2007 19:01:19
> XXXXXSTAM 10/11/2007 19:08:33 10/11/2007 19:08:54
> XXXXXSTP1 10/12/2007 4:00:04 10/12/2007 4:14:12
> XXXXXSTA1 10/12/2007 4:35:56 10/12/2007 4:48:07
> XXXXXSTPM 10/12/2007 19:00:54 10/12/2007 19:04:37
> XXXXXSTAM 10/12/2007 19:09:31 10/12/2007 19:09:52
> XXXXXSTPM 10/13/2007 19:00:19 10/13/2007 19:00:42
> XXXXXSTAM 10/13/2007 19:05:39 10/13/2007 19:05:59
> XXXXXSTP1 10/13/2007 21:00:03 10/13/2007 21:12:39

Before you go any further, please consider using the DATETIME data
format. Date and Time functions in mysql are very powerful but they're
much faster with correctly formatted data. Also, once formatted
correctly, consider providing your dataset as a set of CREATE and
INSERT statements so we can more easily replicate your data.