ALTER PROCEDURE [dbo].[rpt_Inc_Xtat]
(
@CINCIDENT VARCHAR(50),
@CDDL1 VARCHAR(50),
@CDDL2 VARCHAR(50),
@CDCOMPT VARCHAR(50),
@CFROM DATETIME ,
@CTO DATETIME
)
AS
BEGIN
DECLARE @sql nvarchar(4000)
SELECT A.BRN_NAME,A.COMP_NO, A.COMP_CODE, A.COMPLAINANT, A.PTICK_CODE,
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,
COUNT(*) AS OVER_CNT ,
(SELECT COUNT(*) FROM RESPONSE A , BB WHERE A.BRN_NAME=BB.BRN_NAME) AS GRP_CNT,
P.PROD_SERV_TAT
FROM RESPONSE A , BB
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)
GROUP BY A.BRN_NAME,A.COMP_NO, A.COMP_CODE,A.COMPLAINANT, A.PTICK_CODE, P.PROD_SERV_TAT
END
Errors
1. Invalid object name 'BB'
(SELECT COUNT(*) FROM RESPONSE A , BB WHERE A.BRN_NAME=BB.BRN_NAME) AS
GRP_CNT,
2.
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
The multi part-identifier "A.PROD_SERV" could not be found
The multi part-identifier "A.LNAME" could not be found
The multi part-identifier "A.FNAME" could not be found
The multi part-identifier "A.PROD_SERV" could not be found
What I have tried:
Have a similar codes which works.
What am doing here is I have introduced a subquery to COUNT on GROUP .