thanks everyone for your help, I definitely was stuck on a one way road on my thinking with this one. > Date: Fri, 12 Nov 2010 10:20:19 -0500 > Subject: Re: analytics help oracle 10.2.0.4 > From: afatkulin@xxxxxxxxx > To: ganstadba@xxxxxxxxxxx > CC: oracle-l@xxxxxxxxxxxxx > > SQL> with data > 2 as > 3 ( > 4 select 21429995 messageid,to_date('11/05/2010 > 09:07:00','MM/DD/YYYY HH24:MI:SS') actiondate,2097 userid,'Read' > status from dual > 5 union all > 6 select 21429995,to_date('11/05/2010 09:20:00','MM/DD/YYYY > HH24:MI:SS'),2097 userid,'Routed' from dual > 7 union all > 8 select 21429995,to_date('11/05/2010 12:00:00','MM/DD/YYYY > HH24:MI:SS'),2106,'Read' from dual > 9 union all > 10 select 21429995,to_date('11/05/2010 14:15:00','MM/DD/YYYY > HH24:MI:SS'),2106,'Read' from dual > 11 union all > 12 select 21429995,to_date('11/05/2010 14:27:00','MM/DD/YYYY > HH24:MI:SS'),2106,'Read' from dual > 13 union all > 14 select 21429995,to_date('11/05/2010 15:00:00','MM/DD/YYYY > HH24:MI:SS'),2106,'Routed' from dual > 15 union all > 16 select 21429995,to_date('11/05/2010 15:08:00','MM/DD/YYYY > HH24:MI:SS'),2097,'Read' from dual > 17 union all > 18 select 21429995,to_date('11/05/2010 15:08:00','MM/DD/YYYY > HH24:MI:SS'),2097,'Routed' from dual > 19 union all > 20 select 21429995,to_date('11/05/2010 15:34:00','MM/DD/YYYY > HH24:MI:SS'),2106,'Read' from dual > 21 union all > 22 select 21429995,to_date('11/08/2010 08:16:00','MM/DD/YYYY > HH24:MI:SS'),2097,'Read' from dual > 23 union all > 24 select 21429995,to_date('11/08/2010 08:17:00','MM/DD/YYYY > HH24:MI:SS'),2106,'Routed' from dual > 25 union all > 26 select 21429995,to_date('11/08/2010 08:25:00','MM/DD/YYYY > HH24:MI:SS'),2097,'Read' from dual > 27 union all > 28 select 21429995,to_date('11/08/2010 08:32:00','MM/DD/YYYY > HH24:MI:SS'),2097,'Routed' from dual > 29 union all > 30 select 21429995,to_date('11/08/2010 11:36:00','MM/DD/YYYY > HH24:MI:SS'),2589,'Read' from dual > 31 union all > 32 select 21429995,to_date('11/09/2010 12:41:00','MM/DD/YYYY > HH24:MI:SS'),2589,'Routed' from dual > 33 union all > 34 select 21429995,to_date('11/09/2010 12:53:00','MM/DD/YYYY > HH24:MI:SS'),2097,'Read' from dual > 35 union all > 36 select 21429995,to_date('11/09/2010 13:06:00','MM/DD/YYYY > HH24:MI:SS'),2097,'Replied' from dual > 37 ) > 38 select messageid, min(actiondate) start_date, max(actiondate) > end_date, (max(actiondate)-min(actiondate))*86400 ela_secs > 39 from ( > 40 select messageid, actiondate, userid, status, sum(w) over > (partition by messageid order by actiondate) g > 41 from ( > 42 select d.*, case when lag(status, 1, status) over (partition by > messageid order by actiondate) != 'Read' then 1 else 0 end w > 43 from data d > 44 )) group by messageid, g > 45 order by messageid, g; > > MESSAGEID START_DATE END_DATE ELA_SECS > ---------- ------------------------- ------------------------- ---------- > 21429995 05/11/2010 9:07:00 AM 05/11/2010 9:20:00 AM 780 > 21429995 05/11/2010 12:00:00 PM 05/11/2010 3:00:00 PM 10800 > 21429995 05/11/2010 3:08:00 PM 05/11/2010 3:08:00 PM 0 > 21429995 05/11/2010 3:34:00 PM 08/11/2010 8:17:00 AM 232980 > 21429995 08/11/2010 8:25:00 AM 08/11/2010 8:32:00 AM 420 > 21429995 08/11/2010 11:36:00 AM 09/11/2010 12:41:00 PM 90300 > 21429995 09/11/2010 12:53:00 PM 09/11/2010 1:06:00 PM 780 > > 7 rows selected > > -- > Alex Fatkulin, > http://afatkulin.blogspot.com > http://www.linkedin.com/in/alexfatkulin > -- > //www.freelists.org/webpage/oracle-l > >