List:General Discussion« Previous MessageNext Message »
From:João Cândido de Souza Neto Date:April 27 2010 2:25pm
Subject:Re: Query Help!
View as plain text  
Not tested, but I think it can help you or at least give you an ideia on how 
to do it.

select
  EndDateTime + INTERVAL 1 SECOND as startLazy,
  (select StartDateTime - INTERVAL 1 SECOND from table t2 where 
t2.StartDateTime > t1.EndDateTime limit 1) as endLazy
from
  table t1
where
  (select StartDateTime - INTERVAL 1 SECOND from table t2 where 
t2.StartDateTime >= t1.EndDateTime limit 1) > (EndDateTime + INTERVAL 1 
SECOND)


"John Daisley" <mg_sv_r@stripped> escreveu na mensagem 
news:m2x571a6edf1004270647j3d1ef220n4eb9394c339f1949@ style="color:#666">stripped...
> Hi All,
>
> I have a query I need to run but can't think how to get this working so I 
> am
> hoping someone can advise.
>
> I have a table which logs start and end times of Scheduled jobs. It 
> includes
> for simplicity a `DayID`, `StartDateTime` and `EndDateTime` column. Both
> `StartDateTime` and `EndDateTime` are 'datetime' datatypes.
>
> What I need to do is find all times in a day when there was nothing 
> running
> on the system - so all times which do not occur between any of the
> `StartDateTime` and `EndDateTime` values for a particular day.
>
> A simple example, if the table had values
>
> *DayId     StartDateTime                EndDateTime
> 1            2010-02-26 16:40:27       2010-02-26 16:41:27
> 1            2010-02-26 16:41:21       2010-02-26 16:45:57
> 1            2010-02-26 16:47:01       2010-02-26 16:49:21
> 1            2010-02-26 16:49:27       2010-02-26 16:49:55
>
> *I can see the system was free between 16:45:57 and 16:47:01 on 26th
> February 2010 and this is what I would need the query to return only 
> working
> with a lot more data. Any ideas?
>
> Thank you in advance for any help, suggestions. This is currently on a 
> MySQL
> 5.1 system.
>
> Regards
>
>
>
> -- 
> John Daisley
>
> Certified MySQL 5 Database Administrator
> Certified MySQL 5 Developer
> Cognos BI Developer
>
> Telephone: +44 (0)7918 621621
> Email: john.daisley@stripped
> 


Thread
Query Help!John Daisley27 Apr
  • Re: Query Help!Johan De Meersman27 Apr
  • Re: Query Help!joao27 Apr