我有如下表：
通話詳情
| 呼叫者 | 接收者 | 開始于 | 開始時間戳 | 上次修改時間戳 | 結果 | 方向 |
|---|---|---|---|---|---|---|
| 111111 | 91919 | 13.39 | 1636342600 | 1636342601 | 錯過了 | 入站 |
| 111111 | 91919 | 15.40 | 1636342828 | 1636343128 | 錯過了 | 入站 |
| 91919 | 111111 | 19.45 | 1636400728 | 1636401028 | 已回答 | 出站 |
| 222222 | 91919 | 20.50 | 1636404628 | 1636405000 | 錯過了 | 入站 |
預期輸出：
未接來電詳情
| 呼叫者 | 接收者 | 未接來電 | CalledBackAt | 再次聯系 | 接聽未接來電（分鐘） |
|---|---|---|---|---|---|
| 111111 | 91919 | 15.40 | 19.45 | 是的 | 245 |
| 222222 | 91919 | 20.50 | 不 |
合并的兩個主要條件如下：
當接收者向同一個呼叫者回傳呼叫時 - 應該考慮
IsContactedAgain = YES當呼叫沒有回傳
LastModifiedTimestamp到呼叫者之后
我需要一個未接電話的聯合，它們是answered和unanswered。
到目前為止，我想出了以下內容：
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
謝謝。
uj5u.com熱心網友回復：
左連接在此處非常有用，可以在沒有回撥的情況下包含未接來電。
未經測驗的記事本涂鴉
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
uj5u.com熱心網友回復：
我首先查看每個呼叫者和被叫者的最新未接來電。
然后，看起來應該只有一個回呼，但我會撰寫我的查詢，以便它可以處理多個回呼。比如說，他們回電，然后發現他們錯過了解釋細節，并在第二天再次回電。對于這種情況，我們必須決定是采用第一個回呼時間還是最后一個。橫向連接 ( OUTER APPLY) 是處理這種情況的好方法。
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;
您可能還想考慮如何處理未“回答”但由于某種原因失敗的出站回呼。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/353038.html
標籤：sql sql-server 查询语句 自加入