1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Finding Count of Outgoing and Incoming calls from a Caller Log table in Oracle

Discussion in 'Oracle' started by bashamsc, Feb 19, 2013.

  1. bashamsc

    bashamsc New Member

    Joined:
    May 22, 2007
    Messages:
    51
    Likes Received:
    7
    Trophy Points:
    0
    Location:
    chennai
    In day to day life we all use mobile phones and make outgoing and incoming calls. Normally our each call will be stored in the caller log in telecommunication tables.

    We will call to call center to enquire about the no. of incoming calls and outgoing calls we have done. They will enter our no. in the portal and backend it will query it caller log table and it will provide the results.

    Now we will see how it works by looking into below scenario.

    Let us suppose we have a caller_log table and data as below

    Code:
    CALLERID CALLER	CALLEDTO
    
    1	123	456
    2	123	456
    3	456	123
    4	123	678
    5	678	456
    Here 123,456 and 678 are mobile no.

    If we observe the caller_log table we will find that no. of outgoing from mobile no. 123 is 3 (two times called to 456 and one time to 678) and no. of incoming is 1 (456 called one time to 123).

    We need output as below

    Code:
    CALLER	OUTGOING INCOMING
    
    123	3	 1
    456	1	 3
    678	1	 1

    Below query will give the required output

    Code:
    select A.caller,A.outgoing,B.incoming from 
    (select caller ,count(*) outgoing from pbatch.caller group by caller) A ,
    (select calledto,count(*) incoming from pbatch.caller group by calledto) B
    where A.caller=B.calledto
    The inner query


    Code:
    select caller ,count(*) outgoing from pbatch.caller group by caller
    will give below result

    Code:
    CALLER	OUTGOING
    
    123	3
    456	1
    678	1
    The inner query

    Code:
    select calledto,count(*) incoming from pbatch.caller group by calledto
    will give below result

    Code:
    CALLEDTO INCOMING
    
    123	 1
    456	 3
    678	 1

    We will treat these two inner queries as two table and join based on caller and calledto which will give the required output.


    If we add one more date column then we can roll-up the date based on date.

    If we add two more columns incoming time in sec and outgoing time in sec then we will have complete table of caller_log. Now we can write query for total no. of incoming and outgoing calls with total no. of outgoing sec and incoming sec on a particular date.


    We can use below query to achieve above output by using connect by prior clause

    Code:
    select A.caller,A.outgoing,B.incoming from
    (select caller,count(*) outgoing from 
     (select caller,calledto,level lvl from pbatch.caller  connect by NOCYCLE prior calledto=caller)A where A.lvl=1 group by caller)A,
    (select calledto,count(*) incoming from 
     (select caller,calledto,level lvl from pbatch.caller  connect by NOCYCLE prior calledto=caller)A where A.lvl=1 group by calledto)B
    where A.caller=B.calledto
     

Share This Page