Solved

SQL 2005 Stored Procedure Help!

Posted on 2011-03-24
2
241 Views
Last Modified: 2012-05-11
I have the following Stored Procedure that selects Survey results based on 3 Parameters @EventID, @FromDate and @ToDate. Each Survey online has 5 questions the answers to those questions are stored in  the AnswerText field the 5th Question with QuestionID = 5 has a comments field that is stored in AnswerText2.

What I want is a single line for each user that has the following info.

Title, ReportDate, LastName, FirstName, MiddleInitial, Designation, Address1, Address2, City, State, Zip, SurveyDate, Credit, AnswerText where QuestionID =1, AnswerText where QuestionID =2, AnswerText where QuestionID =3, AnswerText where QuestionID =4,  AnswerText where QuestionID =5,  AnswerText2 where QuestionID =5

So I would have a single line for each user with there answers stored in there own column.

What I get now is 5 rows for each user with all their personal information like name and address duplicated and all the answers are in the same column.
USE [EXPORTHpixPROD]
GO
/****** Object:  StoredProcedure [dbo].[spGetMonthlyEvaluationByEventID]    Script Date: 03/24/2011 07:25:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Michael Burk
-- Create date: 03232011
-- Description:	Report that selects Evaluations by EventID and Month
-- =============================================
ALTER PROCEDURE [dbo].[spGetMonthlyEvaluationByEventID] 
	-- Add the parameters for the stored procedure here
	(@EventID AS BIGINT,
	 @FromDate AS VARCHAR(10),
	 @ToDate AS VARCHAR(10))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT DISTINCT    
						CME_Parsed_Survey_XML.SurveyID
					   ,CME_Parsed_Survey_Results_XML.SurveyID
					   ,CONVERT(VARCHAR (10),SurveyDate,101) AS SurveyDate
					   ,CME_Parsed_Survey_XML.EventID
					   ,CME_Parsed_Survey_XML.LicenseNumber
					   ,CME_Parsed_Survey_Results_XML.QuestionID
					   ,CME_Parsed_Survey_Results_XML.QuestionText
					   ,CME_Parsed_Survey_Results_XML.AnswerTextID
					   ,CME_Parsed_Survey_Results_XML.AnswerText
					   ,CME_Parsed_Survey_Results_XML.AnswerText2
					   ,CME_Parsed_Registration_XML.Designation
					   ,CME_Parsed_Registration_XML.FirstName
					   ,CME_Parsed_Registration_XML.MiddleInitial
					   ,CME_Parsed_Registration_XML.LastName
					   ,CME_Parsed_Registration_XML.Title AS Suffix
					   ,CME_Parsed_Registration_XML.Address1
					   ,CME_Parsed_Registration_XML.Address2
					   ,CME_Parsed_Registration_XML.City
					   ,CME_Parsed_Registration_XML.State
					   ,CME_Parsed_Registration_XML.ZipCode
					   ,CME_Courses.Title AS Title
					   ,CME_Courses.Credit
					   ,@ToDate AS ReportDate					  
FROM        CME_Parsed_Survey_XML INNER JOIN
			CME_Parsed_Survey_Results_XML ON CME_Parsed_Survey_XML.SurveyID = CME_Parsed_Survey_Results_XML.SurveyID
			INNER JOIN
			CME_Parsed_Registration_XML ON CME_Parsed_Survey_XML.LicenseNumber = CME_Parsed_Registration_XML.MedicalLicenseNumber
			INNER JOIN
			CME_Courses ON CME_Parsed_Survey_XML.EventID = CME_Courses.EventID
WHERE SurveyDate BETWEEN @FromDate AND @ToDate AND dbo.CME_Courses.EventID = @EventID




END

Open in new window

0
Comment
Question by:mburk1968
2 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 35209726
try this.
USE [EXPORTHpixPROD] 

GO 

/****** Object:  StoredProcedure [dbo].[spGetMonthlyEvaluationByEventID]    Script Date: 03/24/2011 07:25:14 ******/ 
SET ANSI_NULLS  ON 

GO 

SET QUOTED_IDENTIFIER  ON 

GO 

-- =============================================  
-- Author:    Michael Burk  
-- Create date: 03232011  
-- Description:  Report that selects Evaluations by EventID and Month  
-- ============================================= 
ALTER PROCEDURE [dbo].[SPGETMONTHLYEVALUATIONBYEVENTID]-- Add the parameters for the stored procedure here  
( 
                @EventID AS BIGINT, 
               @FromDate AS VARCHAR(10), 
                 @ToDate AS VARCHAR(10)) 
AS 
  BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements. 
    SET NOCOUNT  ON; 
     
    -- Insert statements for procedure here 
    ;WITH cte 
         AS (SELECT ps.SurveyID, 
                    psr.SurveyID                        AS psr_SurveyID, 
                    CONVERT(VARCHAR(10),SurveyDate,101) AS SurveyDate, 
                    ps.EventID, 
                    ps.LicenseNumber, 
                    psr.QuestionID, 
                    psr.QuestionText, 
                    psr.AnswerTextID, 
                    psr.AnswerText, 
                    psr.AnswerText2, 
                    pr.Designation, 
                    pr.FirstName, 
                    pr.MiddleInitial, 
                    pr.LastName, 
                    pr.Title                            AS Suffix, 
                    pr.Address1, 
                    pr.Address2, 
                    pr.City, 
                    pr.State, 
                    pr.ZipCode, 
                    c.Title                             AS Title, 
                    c.Credit, 
                    @ToDate                             AS ReportDate 
               FROM CME_Parsed_Survey_XML ps 
                    INNER JOIN CME_Parsed_Survey_Results_XML psr 
                      ON ps.SurveyID = psr.SurveyID 
                    INNER JOIN CME_Parsed_Registration_XML pr 
                      ON ps.LicenseNumber = pr.MedicalLicenseNumber 
                    INNER JOIN CME_Courses c 
                      ON ps.EventID = c.EventID 
              WHERE SurveyDate BETWEEN @FromDate AND @ToDate 
                    AND c.EventID = @EventID) 
      SELECT SurveyID, 
             psr_SurveyID, 
             SurveyDate, 
             EventID, 
             LicenseNumber, 
             1        AS QuestionID1, 
             MAX(CASE 
                   WHEN QuestionID = 1 THEN QuestionText 
                 END) AS QuestionText1, 
             MAX(CASE 
                   WHEN QuestionID = 1 THEN AnswerText 
                 END) AS AnswerText1, 
             2        AS QuestionID2, 
             MAX(CASE 
                   WHEN QuestionID = 2 THEN QuestionText 
                 END) AS QuestionText2, 
             MAX(CASE 
                   WHEN QuestionID = 2 THEN AnswerText 
                 END) AS AnswerText2, 
             3        AS QuestionID3, 
             MAX(CASE 
                   WHEN QuestionID = 3 THEN QuestionText 
                 END) AS QuestionText3, 
             MAX(CASE 
                   WHEN QuestionID = 3 THEN AnswerText 
                 END) AS AnswerText3, 
             4        AS QuestionID4, 
             MAX(CASE 
                   WHEN QuestionID = 4 THEN QuestionText 
                 END) AS QuestionText4, 
             MAX(CASE 
                   WHEN QuestionID = 4 THEN AnswerText 
                 END) AS AnswerText4, 
             5        AS QuestionID5, 
             MAX(CASE 
                   WHEN QuestionID = 5 THEN QuestionText 
                 END) AS QuestionText5, 
             MAX(CASE 
                   WHEN QuestionID = 5 THEN AnswerText 
                 END) AS AnswerText5, 
             MAX(CASE 
                   WHEN QuestionID = 5 THEN AnswerText2 
                 END) AS AnswerText5_2, 
             Designation, 
             FirstName, 
             MiddleInitial, 
             LastName, 
             Suffix, 
             Address1, 
             Address2, 
             City, 
             [State], 
             ZipCode, 
             Title, 
             Credit, 
             ReportDate 
        FROM cte 
    GROUP BY SurveyID, 
             psr_SurveyID, 
             SurveyDate, 
             EventID, 
             LicenseNumber, 
             Designation, 
             FirstName, 
             MiddleInitial, 
             LastName, 
             Suffix, 
             Address1, 
             Address2, 
             City, 
             [State], 
             ZipCode, 
             Title, 
             Credit, 
             ReportDate 
  END

Open in new window

0
 

Author Closing Comment

by:mburk1968
ID: 35209747
Perfect!

Thank You
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question