加入 4 個表和 2 個計數

我有四張桌子。

UserMaster
----------
UserKey - PK
UserName
UserID - UNIQUE

UserLogTransaction
------------------
UserID - FK:UserMaster.UserID
LoginTime
LogoutTime

CallCenter
----------
CallKey - PK
PickedUserKey - FK:UserMaster.UserKey
PickedTime
StartTime
EndTime

JobCardMaster
-------------
JCKey
RecordedUserKey - FK:UserMaster.UserKey
CreatedTime
CallKey - FK:CallCenter.CallKey

這是場景：每個使用者登錄並參加呼叫。工作卡是為某些呼叫創建的，而不是為所有呼叫創建的。

問題：我想要一份包含以下資訊的報告

UserName | LoginTime | LogoutTime | NoOfCallsAnswered^ | NoOfJobCardsCreated^ | AverageCallDuration^

^ 這些列是按每個使用者的每個會話計算的。會話是 UserLogTransaction 表中的一行。

我嘗試了幾次，它們往往需要大約 1 分鐘才能執行。所以，我認為我的方法存在一些問題。

SELECT UM.UserID, ULT.LogInTime, ULT.LogOutTime, COUNT(CC.PickedUserKey)
FROM dbo.UserMaster UM
JOIN dbo.UserLogTransactions ULT
ON ULT.UserID = UM.UserID
JOIN dbo.CallCenter CC
ON CC.PickedUserKey = UM.UserKey
JOIN dbo.JobCardMaster JC
ON JC.RecordedUserKey = UM.UserKey
WHERE (CC.PickedTime > ULT.LogInTime
AND CC.PickedTime < ULT.LogOutTime)
OR (JC.RecordedUserKey > ULT.LogInTime
AND JC.RecordedUserKey < ULT.LogOutTime)
GROUP BY CC.PickedUserKey, UM.UserID, ULT.LogInTime, ULT.LogOutTime

我被困在這個查詢上好幾天了。感謝任何幫助或提示。

您可以嘗試使用子查詢嗎？如果我在我的數據庫伺服器上創建了表和範例數據，我可以更好地設計它。但下面只是一個概念模型，您可以先在伺服器上嘗試。

SELECT 
UM.UserID, ULT.LogInTime, ULT.LogoutTime,
(SELECT COUNT(CC.PickedUserKey) 
   FROM dbo.CallCenter CC 
   WHERE 
    CC.PickedUserKey = (SELECT UserKey FROM dbo.UserMaster WHERE UserID = UM.UserID) AND
    CC.PickedTime > ULT.LogInTime AND
    CC.PickedTime < LOGOUTTIME) NoOfCallsAttended
FROM dbo.UserMaster UM
JOIN dbo.UserLogTransactions ULT
ON ULT.UserID = UM.UserID

隨後，您也可以為其他列創建子查詢。NoOfJobCardsCreated，AverageCallDuration

引用自：https://dba.stackexchange.com/questions/29911