Geek asks and answers

SQL: how to connect correctly to calculate contact statistics from the call log

The call log has the following columns:

CallingParty, CalledParty, Duration, EventTime

and example data is as follows:

X, a, 10, 10:20
X, b, 12, 10:34
X, c, 8, 12:08
a, X, 22, 12:45
X, a, 10, 13:55
d, X, 30, 15:01

What I would like to do is to calculate statistics for each contact (how many times user X called the contact, what was the sum of outgoing calls, how many times was user X's contact, and what was the sum of the duration of incoming calls). I'm actually trying to extract data from the call log of user X.

The statistics for the above example will be as follows:

contactName, incomingCallsCount, IncomingCallsDuration, OutgoingCallsCount, OutgoingCallsDuration
a, 1, 10, 2, 20
b, 0, 0, 1, 12
c, 0, 0, 1, 8
d, 1, 30, 0, 0

I tried the following query with different joins (), but could not set the correct result

    SELECT t1.CallingParty AS Contact, t1.CallingPartyCount, t1.CallingPartyDuration, t2.CalledPartyCount, t2.CalledPartyDuration FROM
(SELECT e.CallingParty, COUNT(*) AS CallingPartyCount, SUM(CAST(REPLACE(e.Duration, 'NULL', '0') AS int)) AS CallingPartyDuration FROM Events e WHERE Duration <> 'NULL' GROUP BY e.CallingParty) t1
<JOIN>
(SELECT e.CalledParty, COUNT(*) AS CalledPartyCount, SUM(CAST(REPLACE(e.Duration, 'NULL', '0') AS int)) AS CalledPartyDuration FROM Events e WHERE Duration <> 'NULL' GROUP BY e.calledParty) t2
ON t1.CallingParty = t2.CalledParty

Does anyone know what the correct query will be to get the right statistics?

Thank!

+3
source share
4

, .

SELECT CallingParty, CalledParty, SUM(Duration), COUNT(*)
FROM table
GROUP BY CallingParty, CalledParty

(a, b, c d), UNION, .

SELECT CallingParty FROM table
UNION
SELECT CalledParty FROM table

CTE.

WITH outgoing AS (
    SELECT CallingParty, CalledParty, SUM(Duration), COUNT(*)
    FROM table
    GROUP BY CallingParty, CalledParty
), incoming AS (
    SELECT CalledParty, CallingParty, SUM(Duration), COUNT(*)
    FROM table
    GROUP BY CalledParty, CallingParty
), users AS (
   SELECT CallingParty AS UserID FROM table
   UNION
   SELECT CalledParty AS UserID FROM table
)
SELECT * 
FROM users
LEFT OUTER JOIN outgoing ON outgoing.CallingParty = UserID
LEFT OUTER JOIN incoming ON incoming.CalledParty = UserID

!

+2

:

WITH qry AS
(
    SELECT a.*,
          CASE CallingParty
            WHEN 'X' THEN CalledParty
            ELSE  CallingParty
          END AS contactName
        FROM CallLog a
     WHERE ( CallingParty = 'X' OR  CalledParty = 'X')
)
SELECT  contactName,
        SUM( CASE CallingParty WHEN 'X' 1 ELSE 0 END) AS incomingCallsCount,
        SUM( CASE CallingParty WHEN 'X' Duration ELSE 0 END) AS incomingDurationCount
        SUM( CASE CallingParty WHEN 'X' 0 ELSE 1 END) AS outgoingCallsCount,
        SUM( CASE CallingParty WHEN 'X' 0 ELSE Duration END) AS outgoingDurationCount
  FROM  qry
GROUP BY contactName
+3

This is much simpler than this:

SELECT contact, 
sum(t1.CallingParty is not null) as CallingPartyCount, 
sum(t1.CallingPartyDuration) as CallingPartyDuration, 
sum(t2.CalledPartyCount is not null) as CalledPartyCount, 
sum(t2.CalledPartyDuration) as CalledPartyDuration
FROM
(select distinct CallingParty as contact from Event) e
left join Event t1 on t1.CallingParty = contact
left join Event t2 on t2.CalledParty = contact
0
source

I would execute it as follows:

WITH EventsForX AS (
  SELECT
    isIncoming  = CASE CalledParty WHEN @X THEN 1 ELSE 0 END,
    contactName = CASE CalledParty WHEN @X THEN CallingParty ELSE CalledParty END,
    Duration,
  FROM Events
  WHERE CallingParty = @X
     OR CalledParty = @X
)
SELECT
  contactName,
  incomingCallsCount     = SUM(isIncoming),
  incomingCallsDuration  = SUM(isIncoming * Duration),
  outcomingCallsCount    = SUM(1 - isIncoming),
  outcomingCallsDuration = SUM((1 - isIncoming) * Duration)
FROM EventsForX
GROUP BY contactName
0
source

More articles:


All Articles