Read feedback summary by operator

Reads a summary of feedback ratings grouped by operator for the previous year. This aggregation is useful for performance reviews and identifying top-performing agents.

This query aggregates data from the Conversations table, grouping by OwnerEmail and RatingString to provide a count of each rating type per operator.

Standard SQL query example

This is the base query accepted by the connector. To execute it in SQL Server, you have to pass it to the Data Gateway via a Linked Server. See how to accomplish this using the examples below.

SELECT
 OwnerEmail,
 OwnerId, 
 RatingString,
 COUNT(Id) Total
FROM Conversations
GROUP BY OwnerEmail, OwnerId, RatingString
ORDER BY OwnerEmail, RatingString
WITH(
    --OperatorIds='81565000003048017,81565000002408043' --You can supply Operator Ids to filter result for those Operators only
    StartTime='yearstart-1y'
  , EndTime='yearstart-1d'
)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_ZOHO_SALESIQ_IN_GATEWAY], 'SELECT
 OwnerEmail,
 OwnerId, 
 RatingString,
 COUNT(Id) Total
FROM Conversations
GROUP BY OwnerEmail, OwnerId, RatingString
ORDER BY OwnerEmail, RatingString
WITH(
    --OperatorIds=''81565000003048017,81565000002408043'' --You can supply Operator Ids to filter result for those Operators only
    StartTime=''yearstart-1y''
  , EndTime=''yearstart-1d''
)')

Using EXEC in SQL Server (handling larger SQL text)

The major drawback of OPENQUERY is its inability to incorporate variables within SQL statements. This often leads to the use of cumbersome dynamic SQL (with numerous ticks and escape characters).

Fortunately, starting with SQL 2005 and onwards, you can utilize the EXEC (your_sql) AT [LS_TO_ZOHO_SALESIQ_IN_GATEWAY] syntax.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT
 OwnerEmail,
 OwnerId, 
 RatingString,
 COUNT(Id) Total
FROM Conversations
GROUP BY OwnerEmail, OwnerId, RatingString
ORDER BY OwnerEmail, RatingString
WITH(
    --OperatorIds=''81565000003048017,81565000002408043'' --You can supply Operator Ids to filter result for those Operators only
    StartTime=''yearstart-1y''
  , EndTime=''yearstart-1d''
)'
EXEC (@MyQuery) AT [LS_TO_ZOHO_SALESIQ_IN_GATEWAY]