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
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
Below query will give the required output
The inner query
will give below result
The inner query
will give below result
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
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
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
Code:
select caller ,count(*) outgoing from pbatch.caller group by caller
Code:
CALLER OUTGOING 123 3 456 1 678 1
Code:
select calledto,count(*) incoming from pbatch.caller group by calledto
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