Re: analytics help oracle 10.2.0.4
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 12 Nov 2010 10:10:52 -0800
Did you get what you wanted Michael? Because based on your description, I
would have expected 3 rows of output.
Mike
On Fri, Nov 12, 2010 at 5:19 AM, Michael McMullen <ganstadba@xxxxxxxxxxx>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:
