UNPIVOT CHALLENGE - where to assign the fields

See more:

SQL-Server-2008R2

ALTER PROCEDURE [dbo].[grpt_Inc_tat]
 (
   @CINCIDENT VARCHAR(50),
   @CDDL1     VARCHAR(50),
   @CDDL2     VARCHAR(50),
   @CDCOMPT   VARCHAR(50),
   @CFROM     DATETIME   ,
   @CTO       DATETIME 
)
AS
  BEGIN
      --  DECLARE @sql nvarchar(4000)
       
        DECLARE @sql NVARCHAR(MAX) = N''

       
      SET @sql = '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.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.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.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 '
 
   SET @sql = @sql + '  WHERE (A.COMP_DATE BETWEEN @CFROM AND @CTO) '
  
  	      IF @CINCIDENT <>'ALL' 	
             BEGIN			
               IF @CINCIDENT = 'OPENED' 	
                   SET @sql = @sql + ' AND A.COMP_DATE+P.PROD_SERV_TAT >= GETDATE()'   				
               ELSE IF @CINCIDENT = 'CLOSED' 	
                   SET @sql = @sql + ' AND A.COMP_DATE+P.PROD_SERV_TAT <  GETDATE()
                                       AND A.RESOLUTION_STAT_FRONT NOT IN ("BAD","UNHAPPY","UNSATISFACTORY")'  
               ELSE IF @CINCIDENT = 'UNRESOLVED' 	
                   SET @sql = @sql + ' AND A.COMP_DATE+P.PROD_SERV_TAT <  GETDATE()
  									 AND A.RESOLUTION_STAT_FRONT     IN ("BAD","UNHAPPY","UNSATISFACTORY")'  
              END
 
 
  SET @sql = 'SELECT BRN_NAME, PROD_SERV, COMP_TYPE, PROD_SERV_TAT,OVER_CNT,GROUP_CNT,OPINIONS,OPINION_CNT 
    from ('+  @sql+') AA
   UNPIVOT
     (
      OPINIONS_CNT FOR [OPINIONS] IN ([ACCEPT_TAT], [BEYOND_TAT] , [EXACT_TAT]) 
     ) AB  ' 
  
        -----------------------------------
        ------COMPLAINT_TYPE---------------
        -----------------------------------	  
        IF @CDCOMPT <>'ALL' 	
           SET @sql = @sql + ' AND A.COMP_TYPE=@CDCOMPT' 	
                                    
		-------------------------- 
		--------------------------
		---GROUPING & ORDERING---
		--------------------------
		--------------------------

		SET @sql = @sql + ' GROUP BY A.BRN_NAME ,A.PROD_SERV , A.COMP_TYPE,	P.PROD_SERV_TAT ' 

       	
        EXECUTE sp_executesql @sql, N'@CINCIDENT VARCHAR(50), @CDDL1   VARCHAR(50),
                                      @CDDL2     VARCHAR(50), @CDCOMPT VARCHAR(50),
                                      @CFROM     DATETIME   , @CTO     DATETIME'
                                  , @CINCIDENT = @CINCIDENT
                                  , @CDDL1     = @CDDL1	
                                  , @CDDL2     = @CDDL2    
                                  , @CDCOMPT   = @CDCOMPT  
                                  , @CFROM     = @CFROM    
								  , @CTO       = @CTO      
		
			
  END

Issue with the 'UNPIVOT'.
----------------------------------

I have defined 2 fields for the 'UNPIVOT'

They are [OPINIONS],OPINION_CNT

Should they be defined with the first select statement

or with the Second Select Statement just before the UNPIVOT command
below

SET @sql = 'SELECT BRN_NAME, PROD_SERV, COMP_TYPE, PROD_SERV_TAT,OVER_CNT,GROUP_CNT,OPINIONS,OPINION_CNT 
    from ('+  @sql+') AA
   UNPIVOT
     (
      OPINIONS_CNT FOR [OPINIONS] IN ([ACCEPT_TAT], [BEYOND_TAT] , [EXACT_TAT]) 
     ) AB  '

2. Having Problem with the Grouping.

Should the grouping be on the First Select Statement or on the Second
Select Statement.

I have tried all and I Had this errors
--------------------------------------

Msg 8120, Level 16, State 1, Line 4
Column 'RESPONSE.BRN_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "A.BRN_NAME" could not be bound.

Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "A.PROD_SERV" could not be bound.

Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "A.COMP_TYPE" could not be bound.

Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "P.PROD_SERV_TAT" could not be bound.

Msg 207, Level 16, State 1, Line 3
Invalid column name 'GROUP_CNT'.

Msg 207, Level 16, State 1, Line 3
Invalid column name 'OPINION_CNT'.

(1 row(s) affected)

What I have tried:

On going development which requires assistance from experts