Solved

SQL 2005 Stored Procedure Help!

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

775 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