Total Scenario of Callerlog Table

bashamsc's Avatar author of Total Scenario of Callerlog Table
This is an article on Total Scenario of Callerlog Table in Oracle.
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.