Solved

SQL 2005 Stored Procedure Help!

Posted on 2011-03-24
2
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

622 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