October 18, 2014 at 5:52 pm
Hi,
I'm very new to SQL.
I have the following Stored Procedure which works as it should.
I would like to move the data / findings into a perm table?
The reason for this is so clients can connect to the table using excel. I have another stored procedure which is setup in this process already and it works well. I basically have the stored procedure setup on a task to run early in the morning so when clients get up they connect and get their data.
Any help or guides would be very helpful, I have looked and read many guides but most talk about temp tables which doesn't meet my needs.
USE [MMAUDIT]
GO
CREATE PROCEDURE [dbo].[MMA_AUDIT_QUESTIONS_SUMM_STG] AS
BEGIN
SELECT
CLIENT_ID
,SITE_ID
,AUDIT_DATE
,QUESTION_NO
,SCORED, REPEATED
,ALERT
,COMMENTS
FROM
MMA_QUESTION_DAT
WHERE
AUDIT_DATE BETWEEN '2014-07-01' AND '2014-09-28' AND --CHNAGE DATE TO CLIENTS ROUND DATE--
CLIENT_ID = 19 -- CHANGE AS PER CLIENTS ID NUMBER--
END
Thank you in advance
J
October 18, 2014 at 10:52 pm
The task of populating a table from a stored procedure is simple, isolating the data for each client which makes this slightly more complicated as preferably each client should have a dedicated table.
I put together a simple model which creates and populates a dedicated table for each client. To fill in the gap, there is a table for client registration, most likely one already exists. An assumption is also made that all client's login belong to the same group, the code makes certain that only the appropriate client can select from his table.
A client will then access his data in a table called dbo.TBL_CLIENT_nn_DAILY_QUESTIONS_SUMM, where nn is the CLIENT_ID.
The code is somewhat simplified but should get you passed this hurdle.
😎
:exclamation:NOTE: this is a totally untested code, make certain it is tested and adjusted in a safe environment (sandbox)!
The registered clients table
/*
Sample table for registered clients, holding
each client's parameters.
*/
CREATE TABLE dbo.TBL_REGISTERED_CLIENT
(
CLIENT_ID INT NOT NULL CONSTRAINT PK_DBO_TBL_REGISTERED_CLIENT_CLIENT_ID PRIMARY KEY CLUSTERED
,CLIENT_NAME NVARCHAR(100) NOT NULL CONSTRAINT UNQCST_TBL_REGISTERED_CLIENT_CLIENT_NAME UNIQUE
,CLIENT_LOGIN NVARCHAR(128) NOT NULL CONSTRAINT UNQCST_TBL_REGISTERED_CLIENT_CLIENT_LOGIN UNIQUE
,CLIENT_START_DATE DATE NOT NULL
,CLIENT_END_DATE DATE NOT NULL CONSTRAINT CHKCST_TBL_REGISTERED_CLIENT_CLIENT_END_DATE_GT_CLIENT_START_DATE CHECK(CLIENT_END_DATE > CLIENT_START_DATE)
);
The process all clients procedure
USE [MMAUDIT]
GO
/*
An example of a "process all clients" procedure
could look like, using the dbo.TBL_REGISTERED_CLIENT
table to build and populate each client's table.
*/
CREATE PROCEDURE dbo.USP_PROCESS_ALL_CLIENTS
AS
BEGIN
DECLARE @PARAM_STR NVARCHAR(MAX) = N'@CLIENT_ID INT, @CLIENT_START_DATE DATE, @CLIENT_END_DATE DATE';
DECLARE @SQL_TEMPLATE_STR NVARCHAR(MAX) = N'
IF OBJECT_ID(''dbo.TBL_CLIENT_{{@CLIENT_ID}}_DAILY_QUESTIONS_SUMM'') IS NULL
BEGIN
CREATE TABLE dbo.TBL_CLIENT_{{@CLIENT_ID}}_DAILY_QUESTIONS_SUMM
(
CLIENT_ID INT NOT NULL
,SITE_ID INT NOT NULL
,AUDIT_DATE DATETIME NOT NULL
,QUESTION_NO INT NOT NULL
,SCORED INT NOT NULL
,REPEATED INT NOT NULL
,ALERT VARCHAR(100) NOT NULL
,COMMENTS VARCHAR(255) NOT NULL
);
DENY ALL ON dbo.TBL_CLIENT_{{@CLIENT_ID}}_DAILY_QUESTIONS_SUMM TO [CLIENT_GROUP_NAME];
GRANT SELECT ON dbo.TBL_CLIENT_{{@CLIENT_ID}}_DAILY_QUESTIONS_SUMM TO [{{@CLIENT_LOGIN}}];
END
TRUNCATE TABLE dbo.TBL_CLIENT_{{@CLIENT_ID}}_DAILY_QUESTIONS_SUMM;
INSERT INTO TBL_CLIENT_{{@CLIENT_ID}}_DAILY_QUESTIONS_SUMM
(
CLIENT_ID
,SITE_ID
,AUDIT_DATE
,QUESTION_NO
,SCORED
,REPEATED
,ALERT
,COMMENTS
)
SELECT
CLIENT_ID
,SITE_ID
,AUDIT_DATE
,QUESTION_NO
,SCORED
,REPEATED
,ALERT
,COMMENTS
FROM
MMA_QUESTION_DAT
WHERE AUDIT_DATE BETWEEN @CLIENT_START_DATE AND @CLIENT_END_DATE
AND CLIENT_ID = @CLIENT_ID
;
'
/* Declare and populate the client's parameters */
DECLARE @CLIENT_ID INT ;
DECLARE @CLIENT_LOGIN NVARCHAR(128) ;
DECLARE @CLIENT_START_DATE DATE ;
DECLARE @CLIENT_END_DATE DATE ;
DECLARE @SQL_EXEC_STR NVARCHAR(MAX) = N'';
/* Sample cursor code to process all clients */
DECLARE R_SET CURSOR FOR
SELECT
RC.CLIENT_ID
,RC.CLIENT_LOGIN
,RC.CLIENT_START_DATE
,RC.CLIENT_END_DATE
FROM dbo.TBL_REGISTERED_CLIENT RC
OPEN R_SET
FETCH NEXT FROM R_SET INTO
@CLIENT_ID
,@CLIENT_LOGIN
,@CLIENT_START_DATE
,@CLIENT_END_DATE;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL_EXEC_STR = REPLACE(REPLACE(@SQL_TEMPLATE_STR,N'{{@CLIENT_ID}}',@CLIENT_ID),N'{{@CLIENT_LOGIN}}',@CLIENT_LOGIN);
EXEC SP_EXECUTESQL @SQL_EXE_STR, @PARAM_STR,@CLIENT_ID,@CLIENT_START_DATE,@CLIENT_END_DATE;
FETCH NEXT FROM R_SET INTO
@CLIENT_ID
,@CLIENT_LOGIN
,@CLIENT_START_DATE
,@CLIENT_END_DATE;
END
CLOSE R_SET;
DEALLOCATE R_SET;
END
GO
October 19, 2014 at 1:20 am
Hi,
Thanks for this will test,
Would it be easier to show you the code for the other stored procedure?
In the current stored procedure I copy the code down and change the client_id number it then creates the new table in the main database.
I then change the query in the excel sheet (which I send to client) to their client_id number (in reference to the new table created).
This then updates or creates a table with the data in excel which in turns populates their front end dashboard.
Sorry here comes the idiot 🙂
If I need to add to the table created were do I do this?
Can I not create the table first and then just insert it?
Is there an easier way to get excel to pick up the data from a stored procedure (I need to write three more for a master reporting dashboard)?
Thanks,
J
October 19, 2014 at 1:46 am
Of course the more information provided the better the answer/advice;-)
The tables can of course be created beforehand, the solution posted is one of many ways of doing this. Tell us a little bit more about your preference and we'll take it from there.
😎
October 19, 2014 at 2:11 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply