There is a table inside SQL Server called tbl_contacts which will look similar to this screenshot (I have just highlighted few rows in yellow because those rows are going to get aggregated so that you can see difference between input and expected output):

For my requirement, I created a view for all the data manipulations. For an agent working in a team in a day, we wanted to find the avg talk time and max wait time.

I have written the following SQL query:

SELECT
    Date, agentID, teamId,
    AVG([talk time]) AS avg_talk_time,
    MAX([wait time]) AS max_wait_time
FROM
    tbl_contacts
GROUP BY
    Date, agentID, teamId

Now, I have an additional requirement to add 2 columns,

1. The CallStart time whenever the wait time was maximum
2. The CallEnd time whenever the wait time was maximum.

Basically, whenever max wait time occurred in a day, we want to see when the call started and when the call ended.

I tried to extend my query like this:

SELECT
    Date, agentID, teamId,
    AVG([talk time]) AS avg_talk_time,
    MAX([wait time]) AS max_wait_time,
    MAX(CASE  
            WHEN [wait time] = MAX([wait time]) 
                THEN [Call Start] 
        END) AS [Wait Time Call Start],
    MAX(CASE 
            WHEN [wait time] = MAX([wait time]) 
                THEN [Call End] 
        END) AS [Wait Time Call End]
FROM
    tbl_contacts
GROUP BY 
    Date, agentID, teamId

But I'm getting this error:

Cannot use aggregate functions inside of aggregate functions.

I need your help with this SQL query.

I just need to return these 2 columns from my query.

I'm also attaching the screenshot of the expected output below so that it is clear: