I have table as below:

CallDetails

Expected output:

MissedCallDetails

The 2 major conditions to union are as below:

1. When there is a returned call from the receiver to the same caller - it should considered IsContactedAgain = YES

2. When call is not returned back after the LastModifiedTimestamp to the caller

I need a union of missed calls which are answered and unanswered.

So far, I have come up with below:

SELECT
    m.StartedAt AS MissedCallAt
    , r.StartedAt AS CalledBackAt
    , 'Y' AS IsContactedAgain
    , DATEDIFF(mi, m.StartedAt, r.StartedAt) AS 'CalledIn(Minutes)'
FROM 
    CallDetails m, CallDetails r
WHERE 
    CONVERT(char(10), M.StartedAt,126) = DATEADD(Day, -1, CAST( GETDATE() AS Date)) 
    AND m.UserId IS NULL 
    AND m.Outcome = 'Missed call'
    AND r.Recipient = m.Caller
    AND r.StartedTimestamp > m.LastModifiedTimestamp
    AND r.Direction = 'Outbound'
ORDER BY 
    m.StartedAt, r.StartedAt

Thanks.

CodePudding user response：

A left join can be useful here to include the missed calls without callback.

untested notepad scribble

SELECT m.StartedAt AS MissedCallAt
, r.StartedAt AS CalledBackAt
, CASE WHEN r.StartedAt IS NOT NULL THEN 'YES' ELSE 'NO' END AS IsContactedAgain
, DATEDIFF(minute, m.StartedAt, r.StartedAt) AS [CalledIn(Minutes)] 
FROM CallDetails m
LEFT JOIN CallDetails r 
  ON r.Direction = 'Outbound'
 AND r.Recipient = m.Caller
 AND r.Caller = m.Recipient
 AND r.StartedTimestamp > m.LastModifiedTimestamp
WHERE m.Direction = 'Inbound' 
  AND m.Outcome = 'Missed call'
  AND m.StartedAt > DATEDIFF(SECOND,'1970-01-01', DATEADD(Day, -1, CAST(GETUTCDATE() AS DATE)))
ORDER BY m.StartedAt, r.StartedAt

CodePudding user response：

I'd start with looking at the latest missed inbound calls per caller and callee.

Then, there should be only one callback it seems, but I'd write my query such that it can deal with multiple callbacks. Say, they call back, then see that they missed explaining a detail and call back the next day again. For this case we must decide whether to take the first callback time or the last one. A lateral join (OUTER APPLY) is a good way to deal with such situation.

with latest_missed as
(
  select top(1) with ties *
  from calldetails
  where direction = 'Inbound' and outcome = 'Missed'
  order by rank() over (partition by caller, receiver order by startedat desc)
)
select
  lm.caller,
  lm.receiver,
  lm.startedat as missed_call_at,
  callback.startedat as called_back_at,
  case when callback.startedat is null then 'No' else 'Yes' end as is_contacted_again,
  datediff(minute, lm.startedat, callback.startedat) as answered_missed_call_in_minutes
from latest_missed lm
outer apply
(
  select top(1) *
  from calldetails cd
  where cd.caller = lm.receiver
    and cd.receiver = lm.caller
    and cd.startedat > lm.startedat
    and cd.direction = 'Outbound'
    order by cd.startedat desc
) callback
order by lm.caller, lm.receiver;

You may also want to consider how to treat outbound callbacks that are not 'Answered', but failed for some reason.