Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/articles/oracle/)
-   -   Total Scenario of Callerlog Table (http://www.go4expert.com/articles/total-scenario-callerlog-table-t29520/)

bashamsc 28Feb2013 11:58

Total Scenario of Callerlog Table
 
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.


All times are GMT +5.5. The time now is 19:30.