December 14, 2020 at 4:34 pm
I'm having a syntax problem with the following, any help much appreciated! It doesn't like the "[TraceyHarley5],[RhonaCornwall5]" in the first line, "(CALL_TIME" in the 6th line and "[TraceyHarley5]" in the 7th line:
SELECT 'TotalTimeByAgent' AS TotalTime, [TraceyHarley5],[RhonaCornwall5]
FROM (SELECT Agent, time
FROM [a2wh].[dbo].[CallLogCommon] with (NOLOCK)) as AgentTable
PIVOT
(
SUM(dbo.fnGetSeconds(CALL_TIME) AS Call_Time_Seconds))
FOR AGENT IN ([TraceyHarley5],[RhonaCornwall5])
) AS PivotTable;
December 14, 2020 at 5:58 pm
Your subquery is not returning TraceyHarley5, RhonaCornwall5 , or CALL_TIME, so the select, function, & PIVOT have no reference to those columns. And the only columns in your subquery -- Agent, time -- are not used anywhere else.
December 14, 2020 at 6:40 pm
then it seems like this should work:
SELECT * FROM (
SELECT agent, dbo.fnGetSeconds(CALL_TIME)
FROM
[a2wh].[dbo].[CallLogCommon]
) t
pivot (
SUM(dbo.fnGetSeconds(CALL_TIME))
FOR agent IN (
[TraceyHarley5],[RhonaCornwall5])
) AS pivot_table;
December 14, 2020 at 9:43 pm
I'm guessing it didn't.... You still aren't returning CALL_TIME in the subquery, but are trying to reference it in the pivot.
I believe that in your select, you would need to return the aliased function result in your subquery -- e.g.,
dbo.fnGetSeconds(CALL_TIME) AS CallTimeSeconds
and then reference that in the PIVOT -- e.g.,
SUM(CallTimeSeconds)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy