Re: analytics help oracle 10.2.0.4
- From: Alex Fatkulin <afatkulin@xxxxxxxxx>
- To: ganstadba@xxxxxxxxxxx
- Date: Fri, 12 Nov 2010 10:20:19 -0500
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
Other related posts:
