[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

qry help

Posted on 2012-08-30
5
Medium Priority
?
336 Views
Last Modified: 2012-09-05
I have a table as follows:

LOGID       status      created_dt
1                begin      07/26/2012 1:16:26
2                talk         07/26/2012 1:17:26
3                hold        07/26/2012 1:18:26
4                end         07/26/2012 1:18:50
5                begin      07/26/2012 1:20:50
6                talk         07/26/2012 1:22:50
7                end         07/26/2012 1:23:50
8                begin      07/26/2012 1:25:50
9                begin      07/26/2012 1:26:50
10              talk         07/26/2012 1:27:50
11              hold        07/26/2012 1:28:20
12              talk         07/26/2012 1:30:20
14              end         07/26/2012 1:30:50


begin to end is considered as 1 call. Basically I want identify the calls and put a number next to it. Every call should have start and end. Some times there could be just a begin without any other status and I should discard basically mark it 0(eg number 8 where there is a begin but no end).

So i want the result as follows:



LOGID       status      created_dt                          cnt
1                begin      07/26/2012 1:16:26           1
2                talk         07/26/2012 1:17:26           1
3                hold        07/26/2012 1:18:26           1
4                end         07/26/2012 1:18:50           1
5                begin      07/26/2012 1:20:50            2
6                talk         07/26/2012 1:22:50            2
7                end         07/26/2012 1:23:50            2
8                begin      07/26/2012 1:25:50             0
9                begin      07/26/2012 1:26:50            3
10              talk         07/26/2012 1:27:50            3
11              hold        07/26/2012 1:28:20            3
12              talk         07/26/2012 1:30:20            3
14              end         07/26/2012 1:30:50            3


Please let know how to do this..

Thanks
0
Comment
Question by:gs79
5 Comments
 
LVL 21

Assisted Solution

by:flow01
flow01 earned 400 total points
ID: 38352565
see attached file
ee2.txt
0
 

Author Comment

by:gs79
ID: 38353095
@Flow..

Thank you for your response. It works perfectly well for the above requirement.

Unfortunately I missed one more piece and now I am struggling a bit to incorporate this.

At times there is junk after 'end' before next 'begin'. like below. There can be 'end' after an 'end' without a begin which i need to exclude..

9                begin      07/26/2012 1:26:50            3
10              talk         07/26/2012 1:27:50            3
11              hold        07/26/2012 1:28:20            3
12              talk         07/26/2012 1:30:20            3
14              end         07/26/2012 1:30:50            3
15              hold        07/26/2012 1:30:50            0
16              end         07/26/2012 1:30:50            0
17              end         07/26/2012 1:30:50            0
18              begin       07/26/2012 1:30:50           4
..
..
21              end         07/26/2012 1:30:50            4

from the above example i want to ignore 15,16,17..

Please let me know how to incorporate this..

THanks

ps right now it is printing as follows:

9                begin      07/26/2012 1:26:50            3
10              talk         07/26/2012 1:27:50            3
11              hold        07/26/2012 1:28:20            3
12              talk         07/26/2012 1:30:20            3
14              end         07/26/2012 1:30:50            3
15              hold        07/26/2012 1:30:50            3
16              end         07/26/2012 1:30:50            3
17              end         07/26/2012 1:30:50            3
18              begin       07/26/2012 1:30:50           4
..
..
21              end         07/26/2012 1:30:50            4
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 1200 total points
ID: 38353158
My attempt.
WITH test_data AS (
SELECT 1  logid, 'begin' status , to_date('07/26/2012 1:16:26','mm/dd/yyyy hh24:mi:ss') created_dt FROM dual UNION
SELECT 2       , 'talk'         , to_date('07/26/2012 1:17:26','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 3       , 'hold'         , to_date('07/26/2012 1:18:26','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 4       , 'end'          , to_date('07/26/2012 1:18:50','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 5       , 'begin'        , to_date('07/26/2012 1:20:50','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 6       , 'talk'         , to_date('07/26/2012 1:22:50','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 7       , 'end'          , to_date('07/26/2012 1:23:50','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 8       , 'begin'        , to_date('07/26/2012 1:25:50','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 9       , 'begin'        , to_date('07/26/2012 1:26:50','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 10      , 'talk'         , to_date('07/26/2012 1:27:50','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 11      , 'hold'         , to_date('07/26/2012 1:28:20','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 12      , 'talk'         , to_date('07/26/2012 1:30:20','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 14      , 'end'          , to_date('07/26/2012 1:30:50','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 15      , 'hold'         , to_date('07/26/2012 1:30:50','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 16      , 'end'          , to_date('07/26/2012 1:30:50','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 17      , 'end'          , to_date('07/26/2012 1:30:50','mm/dd/yyyyhh24:mi:ss') FROM dual UNION
SELECT 18      , 'begin'        , to_date('07/26/2012 1:30:50','mm/dd/yyyyhh24:mi:ss') FROM dual
)
SELECT td.logid, td.status, td.created_dt, nvl(v.rn,0) AS cnt
FROM test_data td
LEFT JOIN (SELECT td1.logid AS from_logid, min(td2.logid) AS to_logid
                , td1.created_dt AS from_date, min(td2.created_dt) AS to_date
                , row_number() OVER(ORDER BY td1.logid) rn
           FROM test_data td1
                JOIN test_data td2 ON td2.status = 'end' AND td2.logid > td1.logid
           LEFT JOIN test_data td3 ON td3.status = 'begin' AND td3.logid > td1.logid AND td3.logid < td2.logid
           WHERE td1.status = 'begin'
           AND td3.logid IS NULL
           GROUP BY td1.logid, td1.created_dt) v
       ON td.logid BETWEEN v.from_logid AND v.to_logid
;

Open in new window


This was done with the assumption that logid is increasing.  If not - it could be changed to be based on the create_date.
0
 
LVL 2

Assisted Solution

by:namethis
namethis earned 400 total points
ID: 38356599
Offering another solution:

with tab  as  (
    select 1 as LOGID, 'begin' as status, to_date('07/26/2012 1:16:26','MM/DD/YYYY HH24:MI:SS') created_dt from dual
    union all select 2, 'talk', to_date('07/26/2012 1:17:26','MM/DD/YYYY HH24:MI:SS') from dual
    union all select 3, 'hold', to_date('07/26/2012 1:18:26','MM/DD/YYYY HH24:MI:SS') from dual
    union all select 4, 'end', to_date('07/26/2012 1:18:50','MM/DD/YYYY HH24:MI:SS') from dual
    union all select 5, 'begin', to_date('07/26/2012 1:20:50','MM/DD/YYYY HH24:MI:SS') from dual
    union all select 6, 'talk', to_date('07/26/2012 1:22:50','MM/DD/YYYY HH24:MI:SS') from dual
    union all select 7, 'end', to_date('07/26/2012 1:23:50','MM/DD/YYYY HH24:MI:SS') from dual
    union all select 8, 'begin', to_date('07/26/2012 1:25:50','MM/DD/YYYY HH24:MI:SS') from dual
    union all select 9, 'begin', to_date('07/26/2012 1:26:50','MM/DD/YYYY HH24:MI:SS') from dual
    union all select 10, 'talk', to_date('07/26/2012 1:27:50','MM/DD/YYYY HH24:MI:SS') from dual
    union all select 11, 'hold', to_date('07/26/2012 1:28:20','MM/DD/YYYY HH24:MI:SS') from dual
    union all select 12, 'talk', to_date('07/26/2012 1:30:20','MM/DD/YYYY HH24:MI:SS') from dual
    union all select 14, 'end', to_date('07/26/2012 1:30:50','MM/DD/YYYY HH24:MI:SS') from dual
)
select t.LOGID, t.status, t.created_dt, decode(ok,0,0,sum(ok) over(order by rn)) cnt
from 
(   
    select t.*,  case when 'begin' in (status,last_status) and next_status='end' or 
                        'end' in (status, next_status) and last_status='begin' then decode(status, 'begin',1,null)
                      else 0 end ok 
    from 
    (   select  t.*
            , last_value(case when status in ('begin','end') then status end ignore nulls) 
                over(order by rn range between unbounded preceding  and 1 preceding) last_status
            , first_value(case when status in ('begin','end') then status end ignore nulls) 
                over(order by rn range between 1 following and unbounded following) next_status 
        from    
        (   select t.* , row_number() over(order by t.created_dt, t.logid) rn
            from tab t 
        )t
    )t
)t

Open in new window

0
 

Author Closing Comment

by:gs79
ID: 38371003
G!et answers..anlytic functions are cool!

I ended up implementing lwadwell solution..

Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question