Total Scenario of Callerlog Table

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

  1. bashamsc

    bashamsc New Member

    Joined:
    May 22, 2007
    Messages:
    51
    Likes Received:
    7
    Trophy Points:
    0
    Location:
    chennai
    Finding Count of Outgoing and Incoming calls from a Caller Log table in Oracle has not covered total scenario and the query is not universal. Let us suppose the callerlog table with below data

    Code:
    CALLERID	CALLER	CALLEDTO	CALLER_DT	OUTGOING_SEC
    
    1		123	456		02/21/2013	30
    2		123	456		02/21/2013	10
    3		456	123		02/21/2013	10
    4		123	678		02/21/2013	20
    5		678	456		02/21/2013	40
    6		456	678		02/20/2013	20
    7		789	555		02/21/2013	10
    8		567	321		02/20/2013	100

    Here we have included caller_dt and outgoing_sec to cover total scenario.

    The required output should be as below

    Code:
    CALLER	OUTGOING	INCOMING	OUTGOING_SEC	INCOMING_SEC	CALLER_DT
    
    123	3		1		60		10		02/21/2013
    321	0		1		0		100		02/21/2013
    456	1		0		20		0		02/20/2013
    456	1		3		10		80		02/21/2013
    555	0		1		0		10		02/21/2013
    567	1		0		100		0		02/21/2013
    678	0		1		0		20		02/20/2013
    678	1		1		40		20		02/21/2013
    789	1		0		10		0		02/21/2013
    
    If we take an example of callerid 7 and 8 we can observe that 789 has only outgoing not incoing for a particular date and in similar way 321 has only incoing not outgoing.So in output we will be having
    0 incoming for 789 and 0 incoming seconds. For 321 we will be having 0 outgoing and 0 outgoing seconds.


    In previous article we have used below query

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

    The above query will work for the scenario which is mentioned in the article but for the above scenario it will fails. Because it has a joining condition on caller and calledto.
    In the above scenario we have seen the caller's who has only outgoing and also caller's who has only incoming calls.

    Below query will work universally for the above scenario

    Code:
    select caller,outgoing,incoming,outgoing_sec,incoming_sec,caller_dt from
    (
    select caller,sum(outgoing) over (partition by caller,caller_dt order by caller_dt) outgoing ,
    sum(incoming) over (partition by caller,caller_dt order by caller_dt) incoming,
    sum(outgoing_sec) over (partition by caller,caller_dt order by caller_dt) outgoing_sec,
    sum(incoming_sec) over (partition by caller,caller_dt order by caller_dt) incoming_sec,
    caller_dt,
    row_number() over (partition by caller,caller_dt order by caller_dt) rnk
    from
    (
    select caller ,count(*) outgoing, 0 as incoming,caller_dt,sum(outgoing_sec) outgoing_sec ,0 as incoming_sec  from callerlog group by caller,caller_dt
    union
    select calledto,0 as outgoing,count(*) incoming,caller_dt , 0 as outgoing_sec,sum(outgoing_sec) incoming_sec from callerlog group by calledto,caller_dt
    )A
    ) where rnk=1 order by caller,caller_dt
    
    
    Now let us analyze the query part by part.

    The below query

    Code:
    select caller ,count(*) outgoing, 0 as incoming,caller_dt,sum(outgoing_sec) outgoing_sec ,0 as incoming_sec  from callerlog group by caller,caller_dt
    The above query will give the output of only the information of outgoing

    output will be as below

    Code:
    CALLER	OUTGOING	INCOMING	CALLER_DT	OUTGOING_SEC	INCOMING_SEC
    
    123	3		0		02/21/2013	60		0
    456	1		0		02/20/2013	20		0
    456	1		0		02/21/2013	10		0
    567	1		0		02/21/2013	100		0
    678	1		0		02/21/2013	40		0
    789	1		0		02/21/2013	10		0
    
    The below query

    Code:
    select calledto,0 as outgoing,count(*) incoming,caller_dt , 0 as outgoing_sec,sum(outgoing_sec) incoming_sec from callerlog group by calledto,caller_dt
    The above query will give the output of only the information of outgoing

    Output will be as below

    Code:
    CALLEDTO	OUTGOING	INCOMING	CALLER_DT	OUTGOING_SEC	INCOMING_SEC
    
    123		0		1		02/21/2013	0		10
    321		0		1		02/21/2013	0		100
    456		0		3		02/21/2013	0		80
    555		0		1		02/21/2013	0		10
    678		0		1		02/20/2013	0		20
    678		0		1		02/21/2013	0		20
    
    Instead of using join condition as of previous query we are using union to club all the records.

    The below union query will combine the records from two queries output


    Code:
    select caller ,count(*) outgoing, 0 as incoming,caller_dt,sum(outgoing_sec) outgoing_sec ,0 as incoming_sec  from callerlog group by caller,caller_dt
    union
    select calledto,0 as outgoing,count(*) incoming,caller_dt , 0 as outgoing_sec,sum(outgoing_sec) incoming_sec from callerlog group by calledto,caller_dt

    Output will be as below


    Code:
    CALLER	OUTGOING	INCOMING	CALLER_DT	OUTGOING_SEC	INCOMING_SEC
    
    123	0		1		02/21/2013	0		10
    123	3		0		02/21/2013	60		0
    321	0		1		02/21/2013	0		100
    456	0		3		02/21/2013	0		80
    456	1		0		02/20/2013	20		0
    456	1		0		02/21/2013	10		0
    555	0		1		02/21/2013	0		10
    567	1		0		02/21/2013	100		0
    678	0		1		02/20/2013	0		20
    678	0		1		02/21/2013	0		20
    678	1		0		02/21/2013	40		0
    789	1		0		02/21/2013	10		0
    


    Now we need to roll-up the data by using sum function as in below query


    Code:
    select caller,sum(outgoing) over (partition by caller,caller_dt order by caller_dt) outgoing ,
    sum(incoming) over (partition by caller,caller_dt order by caller_dt) incoming,
    sum(outgoing_sec) over (partition by caller,caller_dt order by caller_dt) outgoing_sec,
    sum(incoming_sec) over (partition by caller,caller_dt order by caller_dt) incoming_sec,
    caller_dt,
    row_number() over (partition by caller,caller_dt order by caller_dt) rnk
    from
    (
    select caller ,count(*) outgoing, 0 as incoming,caller_dt,sum(outgoing_sec) outgoing_sec ,0 as incoming_sec  from callerlog group by caller,caller_dt
    union
    select calledto,0 as outgoing,count(*) incoming,caller_dt , 0 as outgoing_sec,sum(outgoing_sec) incoming_sec from callerlog group by calledto,caller_dt
    )A
    
    Output will be as below



    Code:
    CALLER	OUTGOING	INCOMING	OUTGOING_SEC	INCOMING_SEC	CALLER_DT	RNK
    
    123	3		1		60		10		02/21/2013	1
    123	3		1		60		10		02/21/2013	2
    321	0		1		0		100		02/21/2013	1
    456	1		0		20		0		02/20/2013	1
    456	1		3		10		80		02/21/2013	1
    456	1		3		10		80		02/21/2013	2
    555	0		1		0		10		02/21/2013	1
    567	1		0		100		0		02/21/2013	1
    678	0		1		0		20		02/20/2013	1
    678	1		1		40		20		02/21/2013	1
    678	1		1		40		20		02/21/2013	2
    789	1		0		10		0		02/21/2013	1
    
    
    In the above output we can see the duplicate records. To filter it out we have used row_number() function.

    If we filter the duplicate records based on rnk column , we will be getting desired output.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice