I am trying to write a report that returns call statistics for individual Agents. I'm grouping the result based on the Agent. I can easily return the total number of calls for each agent, but when I try to do an aggregate sub-query to return the number of calls that met specific criteria for each agent, I get the same value for all. I understand why this is happening, because I'm not specifying in my subquery the agent, but I can't figure out how to specify this within the sub-query.

This is part of my query:

SELECT c.Contact as [Agent]
,COUNT (df.DynamicFileID) as [Total Number Calls] ---Total Number Calls
,(SELECT COUNT (df.DynamicFileID) --- Calls with Right Party Contact
    FROM DynamicFile df
    LEFT JOIN DynamicFileExtension_79 dfe ON dfe.DynamicFileID = df.DynamicFileID
    LEFT JOIN DynamicFileExtension_79_1 dfe1 ON dfe1.DynamicFileID = df.DynamicFileID
    LEFT JOIN Contact c ON c.ExternalSourceID = dfe.Custom_CallAgent
    WHERE df.FiletemplateID = @CallHistoryFTID
        AND df.Status = 'Complete'
        AND dfe1.Custom_WasBorrowerSpokenWith IN ('Authorized Contact','Spoke with Borrower')
        AND (dfe.Custom_CallResult = 'Promise to Pay'
            OR dfe1.Custom_IsPropertyOwnerOccupied = 'Yes' AND dfe1.Custom_IsPropertyInGoodCondition = 'Yes')
) as [Calls with Right Party Contact]
FROM DynamicFile df
LEFT JOIN DynamicFileExtension_79 dfe ON dfe.DynamicFileID = df.DynamicFileID
LEFT JOIN DynamicFileExtension_79_1 dfe1 ON dfe1.DynamicFileID = df.DynamicFileID
LEFT JOIN Contact c ON c.ExternalSourceID = dfe.Custom_CallAgent

WHERE df.FiletemplateID = @CallHistoryFTID

GROUP BY c.Contact

CodePudding user response：

i dont know struture db...

1. rename aliaces for table in sub-query so as not to overlap of main query

2. You need to add a condition to the subquery in the where section where you need to link the current agent ID likely this

   AND df_sub.Contact = c.Contact

Or by another unique key that you use

CodePudding user response：

Would this work?

SELECT c.Contact as [Agent]
  ,COUNT (df.DynamicFileID) as [Total Number Calls] ---Total Number Calls
  ,COUNT 
     (CASE WHEN df.Status = 'Complete' AND dfe1.Custom_WasBorrowerSpokenWith IN ('Authorized Contact','Spoke with Borrower')
           AND (dfe.Custom_CallResult = 'Promise to Pay' OR (dfe1.Custom_IsPropertyOwnerOccupied = 'Yes' AND dfe1.Custom_IsPropertyInGoodCondition = 'Yes'))
           THEN 1 ELSE 0 END) as [Calls with Right Party Contact]
FROM DynamicFile df
LEFT JOIN DynamicFileExtension_79 dfe ON dfe.DynamicFileID = df.DynamicFileID
LEFT JOIN DynamicFileExtension_79_1 dfe1 ON dfe1.DynamicFileID = df.DynamicFileID
LEFT JOIN Contact c ON c.ExternalSourceID = dfe.Custom_CallAgent
WHERE df.FiletemplateID = @
GROUP BY c.Contact