The current stored procedure below which I just duplicate when I have a new client (by just changing the ID number).

I tried to add my other stored procedure but get the error column does not exist. Looking at the data it would be better in a new table any way and the same for another stored procedure I have. The end goal would be to have three stored procedures which populates the tables (a table for each clients_id) summary, questions and kpi's. At present I have 30 tables setup for the summary below (I should have 30 tables for questions and another 30 tables for KPI's).

As you no doubt can tell I'm very new to this, I also did not setup the sql database which makes it harder I think.

I like the setup below as I have duplicated it for many clients now.

Because I can't determine when clients want to access their data putting it into a table at a certain time works I think?

USE [MMAUDIT]

GO

/****** Object: StoredProcedure [dbo].[MMA_GEN_AUDITSUMMARY_STG] Script Date: 10/19/2014 8:55:02 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[MMA_GEN_AUDITSUMMARY_STG] AS

BEGIN

DECLARE @cols AS NVARCHAR(MAX),@cols1 AS NVARCHAR(MAX),

@query AS NVARCHAR(MAX)

-- Client_ID=19 --

Print '19-'

select @cols = STUFF((SELECT ',' + QUOTENAME(section_name)

from MMA_SECTION_MST WHERE CLIENT_ID=19 AND IN_USE=1

--group by section_name

--order by section_name

FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)')

,1,1,'')

select @cols1 = STUFF((SELECT ',sum(' + QUOTENAME(section_name) + ') AS ' + QUOTENAME(section_name)

from MMA_SECTION_MST WHERE CLIENT_ID=19 AND IN_USE=1

--group by section_name

--order by section_name

FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)')

,1,1,'')

set @query = N'SELECT CLIENT_ID,CLIENT_NAME, SITE_NO,SITE_ID,SITE_NAME,AUDIT_DATE, MANAGER, THIS_AUDIT_SCORE, REPEAT_FLAG, ALERTS_FLAG, ACTION_PLAN_FLAG, ' + @cols1 + N' ,Previous_Audit_score,Previous_Audit_date INTO MMA_AUDITSUM_STG_' + '19' + ' FROM

(

SELECT CM.CLIENT_ID AS CLIENT_ID,CM.CLIENT_NAME AS CLIENT_NAME, S.SITE_NO,S.SITE_ID,S.SITE_NAME,Q.AUDIT_DATE, AL.MANAGER,

SM.SECTION_NAME ,SUM(Q.SCORED) AS

SCORED,SUM(QM.AVAILABLE_SCORE) AS AVAILABLE_SCORE,

ROUND(SUM(Q.SCORED) * 100/ SUM(QM.AVAILABLE_SCORE),2) AS SCORE_PER,

0 AS Previous_Audit_score,''2014-07-04'' AS Previous_Audit_date,

(100.00*0) AS THIS_AUDIT_SCORE,0 AS REPEAT_FLAG,0 AS ALERTS_FLAG,0 AS ACTION_PLAN_FLAG

FROM MMA_QUESTION_MST QM LEFT OUTER JOIN MMA_QUESTION_DAT Q ON Q.CLIENT_ID =

QM.CLIENT_ID AND Q.QUESTION_NO=QM.QUESTION_NO AND Q.CLIENT_ID=19

,MMA_SITE_MST S, MMA_SECTION_MST SM, MMA_CLIENT_MST CM, MMA_AUDITOR_LOGIN AL, MMA_AUDITSTATUS_DAT ADS

WHERE Q.CLIENT_ID = S.CLIENT_ID AND Q.SITE_ID = S.SITE_ID

AND Q.CLIENT_ID =19

AND SM.CLIENT_ID = QM.CLIENT_ID

AND SM.SECTION_ID = QM.SECTION_ID

AND CM.CLIENT_ID = Q.CLIENT_ID

AND AL.CLIENT_ID = Q.CLIENT_ID

AND AL.SITE_ID = Q.SITE_ID

AND AL.AUDIT_DATE = Q.AUDIT_DATE

AND ADS.CLIENT_ID=Q.CLIENT_ID

AND ADS.SITE_ID=Q.SITE_ID

AND ADS.AUDIT_DATE=Q.AUDIT_DATE

AND ADS.STATUS_ID=3

AND QM.QUESTION_CATEGORY<>''Report''

GROUP BY CM.CLIENT_ID,CM.CLIENT_NAME,S.SITE_ID,S.SITE_NO,S.SITE_NAME,Q.AUDIT_DATE,

SM.SECTION_NAME, AL.MANAGER

) x

pivot

(

max(SCORE_PER)

for section_name in (' + @cols + N')

) p GROUP BY CLIENT_ID,CLIENT_NAME,SITE_NO,SITE_ID,SITE_NAME,AUDIT_DATE, MANAGER,Previous_Audit_score,Previous_Audit_date,THIS_AUDIT_SCORE, REPEAT_FLAG, ALERTS_FLAG, ACTION_PLAN_FLAG'

IF OBJECT_ID('dbo.MMA_AUDITSUM_STG_19') IS NOT NULL DROP TABLE MMA_AUDITSUM_STG_19

exec sp_executesql @query;

SELECT S.CLIENT_ID,S.SITE_ID,S.SITE_NO,S.SITE_NAME,Q.AUDIT_DATE,SUM(Q.SCORED) AS SCORED,SUM

(QM.AVAILABLE_SCORE) AS AVAILABLE_SCORE,

ROUND(SUM(Q.SCORED) * 100/ SUM(QM.AVAILABLE_SCORE),2) AS SCORE_PER, SUM(CASE

WHEN REPEATED>0 THEN 1 ELSE 0 END) AS REPEATED,SUM(CASE WHEN ALERT>0 THEN 1

ELSE 0 END) AS ALERT, GV.ALERT_KEY_PER ,GV.REPEAT_KEY_PER, MAX(QA.ACTIONPER ) AS ACTION_PER,

(ROUND(SUM(Q.SCORED) * 100/ SUM(QM.AVAILABLE_SCORE),3) + (SUM(CASE WHEN ALERT>0 THEN 1 ELSE 0 END) * GV.ALERT_KEY_PER) +

(SUM(CASE WHEN REPEATED>0 THEN 1 ELSE 0 END) * GV.REPEAT_KEY_PER) - isnull(MAX(QA.ACTIONPER),0) ) AS FINAL_SCORE into #MMA_TEMP

FROM MMA_QUESTION_MST QM LEFT OUTER JOIN MMA_QUESTION_DAT Q ON Q.CLIENT_ID =

QM.CLIENT_ID AND Q.QUESTION_NO=QM.QUESTION_NO AND Q.CLIENT_ID=19

LEFT OUTER JOIN MMA_QAUDIT_DAT QA ON QA.CLIENT_ID = Q.CLIENT_ID AND QA.SITE_ID = Q.SITE_ID AND QA.AUDIT_DATE = Q.AUDIT_DATE

,MMA_SITE_MST S, MMA_GLOBALVAR_MST GV

WHERE Q.CLIENT_ID = S.CLIENT_ID AND Q.SITE_ID = S.SITE_ID

AND Q.CLIENT_ID =19

AND GV.CLIENT_ID = Q.CLIENT_ID

AND QM.QUESTION_CATEGORY<>'Report'

GROUP BY S.CLIENT_ID,S.SITE_ID,S.SITE_NO,S.SITE_NAME,Q.AUDIT_DATE,GV.ALERT_KEY_PER ,GV.REPEAT_KEY_PER

UPDATE MMA_AUDITSUM_STG_19 SET THIS_AUDIT_SCORE= ISNULL((SELECT round(A.FINAL_SCORE,0) FROM #MMA_TEMP A

WHERE A.CLIENT_ID = MMA_AUDITSUM_STG_19.CLIENT_ID

AND A.SITE_ID = MMA_AUDITSUM_STG_19.SITE_ID

AND A.AUDIT_DATE = MMA_AUDITSUM_STG_19.AUDIT_DATE),0)

UPDATE MMA_AUDITSUM_STG_19 SET REPEAT_FLAG= ISNULL((SELECT A.REPEATED FROM #MMA_TEMP A

WHERE A.CLIENT_ID = MMA_AUDITSUM_STG_19.CLIENT_ID

AND A.SITE_ID = MMA_AUDITSUM_STG_19.SITE_ID

AND A.AUDIT_DATE = MMA_AUDITSUM_STG_19.AUDIT_DATE),0)

UPDATE MMA_AUDITSUM_STG_19 SET ALERTS_FLAG= ISNULL((SELECT A.ALERT FROM #MMA_TEMP A

WHERE A.CLIENT_ID = MMA_AUDITSUM_STG_19.CLIENT_ID

AND A.SITE_ID = MMA_AUDITSUM_STG_19.SITE_ID

AND A.AUDIT_DATE = MMA_AUDITSUM_STG_19.AUDIT_DATE),0)

UPDATE MMA_AUDITSUM_STG_19 SET ACTION_PLAN_FLAG= ISNULL((SELECT COUNT(*) FROM MMA_QAUDIT_DAT A

WHERE A.CLIENT_ID = MMA_AUDITSUM_STG_19.CLIENT_ID

AND A.SITE_ID = MMA_AUDITSUM_STG_19.SITE_ID

AND A.AUDIT_DATE = MMA_AUDITSUM_STG_19.AUDIT_DATE

AND A.ACTIONPER<>0),0)

UPDATE MMA_AUDITSUM_STG_19 SET PREVIOUS_AUDIT_DATE= ISNULL((select MAX(SH.AUDIT_DATE) from MMA_SITEHIST_DAT SH LEFT OUTER JOIN MMA_AUDITSTATUS_DAT AST ON AST.CLIENT_ID =SH.CLIENT_ID

AND AST.SITE_ID = SH.SITE_ID AND AST.AUDIT_DATE =SH.AUDIT_DATE AND AST.STATUS_ID =3

WHERE SH.CLIENT_ID =19

AND SH.CLIENT_ID = MMA_AUDITSUM_STG_19.CLIENT_ID

AND SH.SITE_ID = MMA_AUDITSUM_STG_19.SITE_ID

AND SH.AUDIT_DATE < MMA_AUDITSUM_STG_19.AUDIT_DATE),'1900-01-01')

UPDATE MMA_AUDITSUM_STG_19 SET PREVIOUS_AUDIT_SCORE= ISNULL((SELECT A.SCORED FROM MMA_SITEHIST_DAT A

WHERE A.CLIENT_ID = MMA_AUDITSUM_STG_19.CLIENT_ID

AND A.SITE_ID = MMA_AUDITSUM_STG_19.SITE_ID

AND A.AUDIT_DATE = MMA_AUDITSUM_STG_19.PREVIOUS_AUDIT_DATE),0)

UPDATE MMA_AUDITSUM_STG_19 SET PREVIOUS_AUDIT_SCORE= ISNULL((SELECT A.FINAL_SCORE FROM #MMA_TEMP A

WHERE A.CLIENT_ID = MMA_AUDITSUM_STG_19.CLIENT_ID

AND A.SITE_ID = MMA_AUDITSUM_STG_19.SITE_ID

AND A.AUDIT_DATE = MMA_AUDITSUM_STG_19.PREVIOUS_AUDIT_DATE),0)

WHERE EXISTS (SELECT 'X' FROM #MMA_TEMP A

WHERE A.CLIENT_ID = MMA_AUDITSUM_STG_19.CLIENT_ID

AND A.SITE_ID = MMA_AUDITSUM_STG_19.SITE_ID

AND A.AUDIT_DATE = MMA_AUDITSUM_STG_19.PREVIOUS_AUDIT_DATE)

DROP TABLE #MMA_TEMP

ALTER TABLE MMA_AUDITSUM_STG_19 ADD RISKIDENTIFIED_1 nvarchar(max)

ALTER TABLE MMA_AUDITSUM_STG_19 ADD RISKIDENTIFIED_2 nvarchar(max)

ALTER TABLE MMA_AUDITSUM_STG_19 ADD RISKIDENTIFIED_3 nvarchar(max)

UPDATE MMA_AUDITSUM_STG_19 SET RISKIDENTIFIED_1= ISNULL((SELECT A.RISK_IDENTIFIED FROM MMA_KEYRISK_DAT A

WHERE A.CLIENT_ID = MMA_AUDITSUM_STG_19.CLIENT_ID

AND A.SITE_ID = MMA_AUDITSUM_STG_19.SITE_ID

AND A.AUDIT_DATE = MMA_AUDITSUM_STG_19.AUDIT_DATE

AND A.RISK_ID=1),' ')

UPDATE MMA_AUDITSUM_STG_19 SET RISKIDENTIFIED_2= ISNULL((SELECT A.RISK_IDENTIFIED FROM MMA_KEYRISK_DAT A

WHERE A.CLIENT_ID = MMA_AUDITSUM_STG_19.CLIENT_ID

AND A.SITE_ID = MMA_AUDITSUM_STG_19.SITE_ID

AND A.AUDIT_DATE = MMA_AUDITSUM_STG_19.AUDIT_DATE

AND A.RISK_ID=2),' ')

UPDATE MMA_AUDITSUM_STG_19 SET RISKIDENTIFIED_3= ISNULL((SELECT A.RISK_IDENTIFIED FROM MMA_KEYRISK_DAT A

WHERE A.CLIENT_ID = MMA_AUDITSUM_STG_19.CLIENT_ID

AND A.SITE_ID = MMA_AUDITSUM_STG_19.SITE_ID

AND A.AUDIT_DATE = MMA_AUDITSUM_STG_19.AUDIT_DATE

AND A.RISK_ID=3),' ')

Thanks,

J