Solved

SQL 2005 Stored Procedure Help!

Posted on 2011-03-24
2
236 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 40

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

919 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now