event analysis report in sql

[sam13b]

For a communications chanel log, I have a two fields table, field 1= time of event, and field2=eventtype, which can be either R for Received message, S for sent message, or E for error. I get a new row entry each time a message is transferred or the progam fails trying.

I want to write a sql query to give me, by time, the startng and ending time of any consecutive (by time) sequence of error messages, as follows...

table mytable=
Time EventType
064502000 S
064504000 R
064505000 E
064607000 E
064708000 R
064808000 S
064909000 S
065103000 E
065803000 E
070301000 S
..etc..


would give

Error Outages Report
started 064505000 ended 064708000
started 065103000 ended 070301000

How do I go about this???? right now Im trying to do this with sqlite just to see how good or limted sqlite is, but if nessecary I have sqlserver or sqlanywhere available which I can move up to.

Thanks in advance

 

[dnoeth]

select
  min(timecol),
  eventend
from
 (
  select
    timecol,
    (select min(timecol) from tab t2
     where t2.timecol > t1.timecol
     and t2.eventtype <> 'E') as eventend
  from tab t1
  where
    eventtype = 'E'
  ) dt
group by eventend
order by 1

Be shure to have an index on timecol...

Dieter

 

[sam13b]

Many thanks for the help; nice solution; thanks again