asked on

finding max time in calculations

I need to find the max time difference between two times.
I am getting an aggregate error.

SELECT CLE_IDX_CHAN, COUNT(CLE_IDX_OFFHOOK) ''Call Count'', SUM(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)) ''Seconds'',
            CONVERT(VARCHAR(12),DATEADD(ss, AVG(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)), CAST(0 AS DATETIME)),8) ''Avg Length'',
            CONVERT(VARCHAR(12),DATEADD(ss, SUM(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)), CAST(0 AS DATETIME)),8) ''Total Length'',
            MAX(CONVERT(VARCHAR(12),DATEADD(ss, SUM(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)), CAST(0 AS DATETIME)),8) )
            FROM CL_EVENTS_IDX WITH (NOLOCK)
                  INNER JOIN CL_EVENTS A WITH (NOLOCK) ON CLE_IDX_KEY = CLE_ID
                  INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
                  INNER JOIN CL_EVENTS B WITH (NOLOCK) ON A.CLE_ID = B.CLE_ID AND A.CLE_CALLID = B.CLE_CALLID
                  INNER JOIN CL_APPID_XREF WITH (NOLOCK) on A.CLE_EVID = CLX_APPID
            WHERE A.CLE_TYPE = 508 AND B.CLE_TYPE = 536 and a.CLE_EVID = b.CLE_EVID
                  AND (A.CLE_EVID = 1 OR  A.CLE_EVID = 13)
            Group BY CLE_IDX_CHAN

Open in new window

HugoHiasl

The two lines beginning with CONVERT are not valid aggregations for a group by statement:

CONVERT(VARCHAR(12),DATEADD(ss, AVG(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)), CAST(0 AS DATETIME)),8) ''Avg Length'',

portlight

ASKER

Those are not giving me an issue. It is when I add the

 MAX(CONVERT(VARCHAR(12),DATEADD(ss, SUM(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)), CAST(0 AS DATETIME)),8) )

Open in new window

Line that I begin to get the problem.

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EugeneZ

<I am getting an aggregate error.>
please post the error

Scott Pletcher

>> Line that I begin to get the problem.
MAX(CONVERT(VARCHAR(12),DATEADD(ss, SUM(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)), CAST(0 AS DATETIME)),8) ) <<

You can't do a MAX() of a SUM() in SQL Server; you can't embed any of the aggregate functions within each other.