As i mentioned in the comment to the question, you have to use
Print[
^] method to detect where you've made mistake.
I tested it and it returns:
SELECT A.BRN_NAME ,A.PROD_SERV , A.COMP_TYPE,
P.PROD_SERV_TAT,
COUNT(*) AS OVER_CNT ,
SUM(CASE WHEN DATEADD(DD,P.PROD_SERV_TAT, A.COMP_DATE) < COALESCE(A.DAT_COMP_INV_CLOSE0,A.DAT_COMP_INV_CLOSE,A.DATE_COMP_CLOS_FRMT)THEN 1 ELSE 0 END) AS ACCEPT_TAT,
SUM(CASE WHEN DATEADD(DD,P.PROD_SERV_TAT, A.COMP_DATE) > COALESCE(A.DAT_COMP_INV_CLOSE0,A.DAT_COMP_INV_CLOSE,A.DATE_COMP_CLOS_FRMT)THEN 1 ELSE 0 END) AS BEYOND_TAT,
SUM(CASE WHEN DATEADD(DD,P.PROD_SERV_TAT, A.COMP_DATE) = COALESCE(A.DAT_COMP_INV_CLOSE0,A.DAT_COMP_INV_CLOSE,A.DATE_COMP_CLOS_FRMT)THEN 1 ELSE 0 END) AS EXACT_TAT,
(SELECT COUNT(*) FROM RESPONSE BB WHERE A.BRN_NAME=BB.BRN_NAME) AS GRP_CNT ,OPINIONS,OPINION_CNT
FROM RESPONSE A
LEFT JOIN BRANCH C ON A.BRN_NAME = C.BRN_NAME
LEFT JOIN PROD_SERV P ON A.PROD_SERV = P.PROD_SERV_NAME
LEFT JOIN PAYMAST B ON A.LNAME = B.LNAME
AND A.FNAME = B.FNAME
AND A.EMPNO = B.EMPNO
WHERE (A.COMP_DATE BETWEEN @CFROM AND @CTO) UNPIVOT
(
OPINION_CNT FOR OPINIONS IN
(ACCEPT_TAT,BEYOND_TAT,EXACT_TAT)
) GROUP BY A.BRN_NAME ,A.PROD_SERV , A.COMP_TYPE,
P.PROD_SERV_TAT
Take a look at underlined line.
UNPIVOT
instruction is right after a
WHERE
clause. A proper SQL statement should looks like:
SELECT <unpivoted_columns>
FROM
(
) AS pvt
UNPIVOT
(
) AS unpvt
So, you have to change your sql code accordingly.
Note: i'd change declaration of
@sql
variable to:
DECLARE @sql NVARCHAR(MAX) = N'';
For further details, please see:
Using PIVOT and UNPIVOT | Microsoft Docs[
^]