RE: analytics help oracle 10.2.0.4

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_at_gmail.com
> To: ganstadba_at_hotmail.com
> CC: oracle-l_at_freelists.org
>
> 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
                                               

--
http://www.freelists.org/webpage/oracle-l