Re: analytics help oracle 10.2.0.4
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: ganstadba@xxxxxxxxxxx
- Date: Fri, 12 Nov 2010 15:20:43 +0100
Michael,
Not sure that's exactly what you want but it may be close:
SQL> select messageid,
2 actiondate,
3 userid,
4 actiondate - lag(actiondate, 1) over (partition by messageid
5 order by actiondate) lapse
6 from (select messageid,
7 actiondate,
8 userid,
9 status,
10 prev_status
11 from (select messageid,
12 actiondate,
13 userid,
14 status,
15 lag(status, 1)
16 over (partition by messageid
17 order by actiondate) prev_status
18 from messages)
19 where status = 'Read'
20 and (prev_status = 'Routed'
21 or prev_status is null))
22 order by actiondate
23 /
MESSAGEID ACTIONDATE USERID LAPSE
---------- -------------------- ---------- ----------
21429995 05-NOV-2010 09:07:00 2097
21429995 05-NOV-2010 12:00:00 2106 .120138889
21429995 05-NOV-2010 15:08:00 2097 .130555556
21429995 05-NOV-2010 15:34:00 2106 .018055556
21429995 08-NOV-2010 08:25:00 2097 2.70208333
21429995 08-NOV-2010 11:36:00 2589 .132638889
21429995 09-NOV-2010 12:53:00 2097 1.05347222
7 rows selected.
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
On 11/12/2010 02:19 PM, Michael McMullen wrote:
>
> I am trying to figure out the times between status changes of a message
>
> Problem is
> the message comes in, an agent reads the message and either replies or
> routes the message to another agent until it is replied, it can get
> bounced around to multiple agents or the lifespan of the message
> see below for more info
>
> with data
> as
> (
> select 21429995 messageid,to_date('11/05/2010 09:07:00','MM/DD/YYYY
> HH24:MI:SS') actiondate,2097 userid,'Read' status from dual
> union all
> select 21429995,to_date('11/05/2010 09:20:00','MM/DD/YYYY
> HH24:MI:SS'),2097 userid,'Routed' from dual
> union all
> select 21429995,to_date('11/05/2010 12:00:00','MM/DD/YYYY
> HH24:MI:SS'),2106,'Read' from dual
> union all
> select 21429995,to_date('11/05/2010 14:15:00','MM/DD/YYYY
> HH24:MI:SS'),2106,'Read' from dual
> union all
> select 21429995,to_date('11/05/2010 14:27:00','MM/DD/YYYY
> HH24:MI:SS'),2106,'Read' from dual
> union all
> select 21429995,to_date('11/05/2010 15:00:00','MM/DD/YYYY
> HH24:MI:SS'),2106,'Routed' from dual
> union all
> select 21429995,to_date('11/05/2010 15:08:00','MM/DD/YYYY
> HH24:MI:SS'),2097,'Read' from dual
> union all
> select 21429995,to_date('11/05/2010 15:08:00','MM/DD/YYYY
> HH24:MI:SS'),2097,'Routed' from dual
> union all
> select 21429995,to_date('11/05/2010 15:34:00','MM/DD/YYYY
> HH24:MI:SS'),2106,'Read' from dual
> union all
> select 21429995,to_date('11/08/2010 08:16:00','MM/DD/YYYY
> HH24:MI:SS'),2097,'Read' from dual
> union all
> select 21429995,to_date('11/08/2010 08:17:00','MM/DD/YYYY
> HH24:MI:SS'),2106,'Routed' from dual
> union all
> select 21429995,to_date('11/08/2010 08:25:00','MM/DD/YYYY
> HH24:MI:SS'),2097,'Read' from dual
> union all
> select 21429995,to_date('11/08/2010 08:32:00','MM/DD/YYYY
> HH24:MI:SS'),2097,'Routed' from dual
> union all
> select 21429995,to_date('11/08/2010 11:36:00','MM/DD/YYYY
> HH24:MI:SS'),2589,'Read' from dual
> union all
> select 21429995,to_date('11/09/2010 12:41:00','MM/DD/YYYY
> HH24:MI:SS'),2589,'Routed' from dual
> union all
> select 21429995,to_date('11/09/2010 12:53:00','MM/DD/YYYY
> HH24:MI:SS'),2097,'Read' from dual
> union all
> select 21429995,to_date('11/09/2010 13:06:00','MM/DD/YYYY
> HH24:MI:SS'),2097,'Replied' from dual
> )
> select *
> from
> data
> order by actiondate
>
> which will give something like
> MESSAGEID ACTIONDATE USERID STATUS
>
> 21429995 11/5/2010 9:07:00 AM 2097 Read
> 21429995 11/5/2010 9:20:00 AM 2097 Routed
> 21429995 11/5/2010 12:00:00 PM 2106 Read
> 21429995 11/5/2010 2:15:00 PM 2106 Read
> 21429995 11/5/2010 2:27:00 PM 2106 Read
> 21429995 11/5/2010 3:00:00 PM 2106 Routed
> 21429995 11/5/2010 3:08:00 PM 2097 Read
> 21429995 11/5/2010 3:08:00 PM 2097 Routed
>
>
> there would of course be multiple messageid's but messageid is my main
> grouping.
> In this example the first two records are a group, rec's 3-6 are
> aonther group and so on, everytime it gets routed to another group I
> want to calculate the time difference between routed and the first
> read of that group
> example
> rec 2 actiondate - rec 1 actiondate
>
> rec 6 actiondate - rec 3 actiondate
> rec 8 actiondate - rec 6 actiondate
> I can't seem to figure out the grouping analytics, any help would be
> appreciated. I've used various versions of lag,lead, row_number
>
> Thanks
> Mike
>
Other related posts:
