Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure

Author

Topic

dsmith8
Starting Member

6 Posts

Posted - 2007-06-26 : 09:13:11

Hi all

As you'll probably guess from my code, I'm a little new to the world of stored procedures. I'm looking for some advice really with how I can move forward.

Basically, I used to have an ASP page with lots of SQL statements to pull all kinds of data out of my database and display it in a table. However, I think this can probably be achieved more efficiently with a stored procedure. SO I have had a go!


USE [stpauls]
GO
/****** Object:  StoredProcedure [stpauls].[Survey_Retrieve_Results]    Script Date: 06/26/2007 14:04:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [stpauls].[Survey_Retrieve_Results] 
(
	@surveyTeacher varchar(24),
	@surveySubject varchar(100),
    @surveyLevel varchar(8),
    @surveyFrom datetime,
	@surveyTo datetime,
    @CountTotalMales int OUTPUT


)

AS

/* Total number of results */

DECLARE @total decimal(2,1)

SELECT @total = Count(surveyID)
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

/* Total number of females */

SELECT Count(surveyID) As CountTotalFemales
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyGender = 'F'
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;


/* Total number of males */

SET @CountTotalMales = (SELECT Count(surveyID) As CountTotalMales
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyGender = 'M'
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo);

/* Average overall result */

SELECT convert(decimal(2,1),Avg(surveyQ1)) AS AvgQ1, convert(decimal(2,1),Avg(surveyQ2)) AS AvgQ2, convert(decimal(2,1),Avg(surveyQ3)) AS AvgQ3, convert(decimal(2,1),Avg(surveyQ4)) AS AvgQ4, convert(decimal(2,1),Avg(surveyQ5)) AS AvgQ5, convert(decimal(2,1),Avg(surveyQ6)) AS AvgQ6, convert(decimal(2,1),Avg(surveyQ7)) AS AvgQ7, convert(decimal(2,1),Avg(surveyQ8)) AS AvgQ8, convert(decimal(2,1),Avg(surveyQ9)) AS AvgQ9, convert(decimal(2,1),Avg(surveyQ10)) AS AvgQ10, convert(decimal(2,1),Avg(surveyQ11)) AS AvgQ11, convert(decimal(2,1),Avg(surveyQ12)) AS AvgQ12, convert(decimal(2,1),Avg(surveyQ13)) AS AvgQ13, convert(decimal(2,1),Avg(surveyQ14)) AS AvgQ14, convert(decimal(2,1),Avg(surveyQ15)) AS AvgQ15
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

/* Average female result */

SELECT convert(decimal(2,1),Avg(surveyQ1)) AS AvgFemaleQ1, convert(decimal(2,1),Avg(surveyQ2)) AS AvgFemaleQ2, convert(decimal(2,1),Avg(surveyQ3)) AS AvgFemaleQ3, convert(decimal(2,1),Avg(surveyQ4)) AS AvgFemaleQ4, convert(decimal(2,1),Avg(surveyQ5)) AS AvgFemaleQ5, convert(decimal(2,1),Avg(surveyQ6)) AS AvgFemaleQ6, convert(decimal(2,1),Avg(surveyQ7)) AS AvgFemaleQ7, convert(decimal(2,1),Avg(surveyQ8)) AS AvgFemaleQ8, convert(decimal(2,1),Avg(surveyQ9)) AS AvgFemaleQ9, convert(decimal(2,1),Avg(surveyQ10)) AS AvgFemaleQ10, convert(decimal(2,1),Avg(surveyQ11)) AS AvgFemaleQ11, convert(decimal(2,1),Avg(surveyQ12)) AS AvgFemaleQ12, convert(decimal(2,1),Avg(surveyQ13)) AS AvgFemaleQ13, convert(decimal(2,1),Avg(surveyQ14)) AS AvgFemaleQ14, convert(decimal(2,1),Avg(surveyQ15)) AS AvgFemaleQ15
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyGender = 'F'
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

/* Average male result */

SELECT convert(decimal(2,1),Avg(surveyQ1)) AS AvgMaleQ1, convert(decimal(2,1),Avg(surveyQ2)) AS AvgMaleQ2, convert(decimal(2,1),Avg(surveyQ3)) AS AvgMaleQ3, convert(decimal(2,1),Avg(surveyQ4)) AS AvgMaleQ4, convert(decimal(2,1),Avg(surveyQ5)) AS AvgMaleQ5, convert(decimal(2,1),Avg(surveyQ6)) AS AvgMaleQ6, convert(decimal(2,1),Avg(surveyQ7)) AS AvgMaleQ7, convert(decimal(2,1),Avg(surveyQ8)) AS AvgMaleQ8, convert(decimal(2,1),Avg(surveyQ9)) AS AvgMaleQ9, convert(decimal(2,1),Avg(surveyQ10)) AS AvgMaleQ10, convert(decimal(2,1),Avg(surveyQ11)) AS AvgMaleQ11, convert(decimal(2,1),Avg(surveyQ12)) AS AvgMaleQ12, convert(decimal(2,1),Avg(surveyQ13)) AS AvgMaleQ13, convert(decimal(2,1),Avg(surveyQ14)) AS AvgMaleQ14, convert(decimal(2,1),Avg(surveyQ15)) AS AvgMaleQ15
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyGender = 'M'
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

/* Percentages for Strongly Agree */

SELECT (convert(decimal(2,1),Count(surveyQ1))/@total)*100 AS countStronglyAgreeQ1
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ1 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ2))/@total)*100 AS countStronglyAgreeQ2
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ2 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ3))/@total)*100 AS countStronglyAgreeQ3
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ3 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ4))/@total)*100 AS countStronglyAgreeQ4
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ4 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ5))/@total)*100 AS countStronglyAgreeQ5
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ5 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ6))/@total)*100 AS countStronglyAgreeQ6
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ6 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ7))/@total)*100 AS countStronglyAgreeQ7
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ7 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ8))/@total)*100 AS countStronglyAgreeQ8
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ8 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ9))/@total)*100 AS countStronglyAgreeQ9
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ9 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ10))/@total)*100 AS countStronglyAgreeQ10
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ10 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ11))/@total)*100 AS countStronglyAgreeQ11
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ11 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ12))/@total)*100 AS countStronglyAgreeQ12
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ12 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ13))/@total)*100 AS countStronglyAgreeQ13
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ13 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ14))/@total)*100 AS countStronglyAgreeQ14
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ14 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ15))/@total)*100 AS countStronglyAgreeQ15
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ15 = 4.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

/* Percentages for Agree */

SELECT (convert(decimal(2,1),Count(surveyQ1))/@total)*100 AS countAgreeQ1
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ1 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ2))/@total)*100 AS countAgreeQ2
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ2 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ3))/@total)*100 AS countAgreeQ3
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ3 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ4))/@total)*100 AS countAgreeQ4
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ4 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ5))/@total)*100 AS countAgreeQ5
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ5 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ6))/@total)*100 AS countAgreeQ6
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ6 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ7))/@total)*100 AS countAgreeQ7
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ7 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ8))/@total)*100 AS countAgreeQ8
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ8 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ9))/@total)*100 AS countAgreeQ9
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ9 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ10))/@total)*100 AS countAgreeQ10
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ10 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ11))/@total)*100 AS countAgreeQ11
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ11 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ12))/@total)*100 AS countAgreeQ12
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ12 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ13))/@total)*100 AS countAgreeQ13
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ13 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ14))/@total)*100 AS countAgreeQ14
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ14 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ15))/@total)*100 AS countAgreeQ15
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ15 = 3.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

/* Percentages for Disagree */

SELECT (convert(decimal(2,1),Count(surveyQ1))/@total)*100 AS countDisagreeQ1
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ1 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ2))/@total)*100 AS countDisagreeQ2
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ2 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ3))/@total)*100 AS countDisagreeQ3
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ3 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ4))/@total)*100 AS countDisagreeQ4
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ4 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ5))/@total)*100 AS countDisagreeQ5
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ5 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ6))/@total)*100 AS countDisagreeQ6
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ6 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ7))/@total)*100 AS countDisagreeQ7
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ7 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ8))/@total)*100 AS countDisagreeQ8
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ8 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ9))/@total)*100 AS countDisagreeQ9
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ9 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ10))/@total)*100 AS countDisagreeQ10
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ10 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ11))/@total)*100 AS countDisagreeQ11
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ11 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ12))/@total)*100 AS countDisagreeQ12
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ12 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ13))/@total)*100 AS countDisagreeQ13
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ13 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ14))/@total)*100 AS countDisagreeQ14
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ14 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ15))/@total)*100 AS countDisagreeQ15
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ15 = 2.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

/* Percentages for Strongly Disagree */

SELECT (convert(decimal(2,1),Count(surveyQ1))/@total)*100 AS countStronglyDisagreeQ1
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ1 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ2))/@total)*100 AS countStronglyDisagreeQ2
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ2 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ3))/@total)*100 AS countStronglyDisagreeQ3
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ3 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ4))/@total)*100 AS countStronglyDisagreeQ4
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ4 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ5))/@total)*100 AS countStronglyDisagreeQ5
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ5 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ6))/@total)*100 AS countStronglyDisagreeQ6
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ6 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ7))/@total)*100 AS countStronglyDisagreeQ7
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ7 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ8))/@total)*100 AS countStronglyDisagreeQ8
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ8 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ9))/@total)*100 AS countStronglyDisagreeQ9
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ9 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ10))/@total)*100 AS countStronglyDisagreeQ10
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ10 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ11))/@total)*100 AS countStronglyDisagreeQ11
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ11 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ12))/@total)*100 AS countStronglyDisagreeQ12
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ12 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ13))/@total)*100 AS countStronglyDisagreeQ13
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ13 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ14))/@total)*100 AS countStronglyDisagreeQ14
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ14 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

SELECT (convert(decimal(2,1),Count(surveyQ15))/@total)*100 AS countStronglyDisagreeQ15
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ15 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;

Now I was kind of hoping this stored procedure would return all the calculations into one recordset (I'll be calling it from ASP .net). Then I can display all the values in a table.

Is it possible for me to do this? Or does each SELECT statement have to be a different stored procedure? I was hoping to do it in one stored procedure because I imagined that I wouldn't have to keep going back and forth to/from the database.

Does that make sense? Any advice would be greatly appreciated.

Daniel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-26 : 09:19:49

Use Union all

Madhivanan

Failing to plan is Planning to fail

dsmith8
Starting Member

6 Posts

Posted - 2007-06-26 : 09:28:15

Thank you Madhivanan

I have tried union all (you can sense a BUT coming) but it doesn't return the data in separate columns but instead in one column as several records.

Am I right in thinking that?!

So I would really like it to be like

TotalFemale | TotalMale | AvgQ1 | .....
10 | 12 | 3.5 | .....

Instead, union all seems to return something like

TotalFemale
10
12
3.5
...

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-06-26 : 10:13:09

instead of one record set, return it as is, into a dataset instead. Then you can access each table inside the dataset to get the numbers you need. Otherwise, it will drive you insane.
And just to nit pick, you can change

SET @CountTotalMales = (SELECT Count(surveyID) As CountTotalMales
FROM Survey...code]

TO
[code] SELECT @CountTotalMales = Count(surveyID) 
FROM Survey...

END my 2 cents

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-26 : 10:17:10

With this horrible non-normalized table the best you can do is only scan the table once with judicious use of CASE. I started doing this but got bored.
This should be enough to give you the idea. (ps It is probably best to do the percent calculations on the front end.)

SELECT COUNT(*) AS Total
    ,COUNT(CASE surveyGender WHEN 'F' THEN 1 END) AS CountTotalFemales
    ,COUNT(CASE surveyGender WHEN 'M' THEN 1 END) AS CountTotalMales
    ,CAST(AVG(surveyQ1) AS decimal(2,1)) AS AvgQ1
    ,CAST(AVG(surveyQ2) AS decimal(2,1)) AS AvgQ2
    ,CAST(AVG(surveyQ3) AS decimal(2,1)) AS AvgQ3
    ,CAST(AVG(surveyQ4) AS decimal(2,1)) AS AvgQ4
    ,CAST(AVG(surveyQ5) AS decimal(2,1)) AS AvgQ5
    ,CAST(AVG(surveyQ6) AS decimal(2,1)) AS AvgQ6
    ,CAST(AVG(surveyQ7) AS decimal(2,1)) AS AvgQ7
    ,CAST(AVG(surveyQ8) AS decimal(2,1)) AS AvgQ8
    ,CAST(AVG(surveyQ9) AS decimal(2,1)) AS AvgQ9
    ,CAST(AVG(surveyQ10) AS decimal(2,1)) AS AvgQ10
    ,CAST(AVG(surveyQ11) AS decimal(2,1)) AS AvgQ11
    ,CAST(AVG(surveyQ12) AS decimal(2,1)) AS AvgQ12
    ,CAST(AVG(surveyQ13) AS decimal(2,1)) AS AvgQ13
    ,CAST(AVG(surveyQ14) AS decimal(2,1)) AS AvgQ14
    ,CAST(AVG(surveyQ15) AS decimal(2,1)) AS AvgQ15
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ1 END) AS decimal(2,1)) AS AvgFemaleQ1
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ2 END) AS decimal(2,1)) AS AvgFemaleQ2
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ3 END) AS decimal(2,1)) AS AvgFemaleQ3
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ4 END) AS decimal(2,1)) AS AvgFemaleQ4
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ5 END) AS decimal(2,1)) AS AvgFemaleQ5
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ6 END) AS decimal(2,1)) AS AvgFemaleQ6
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ7 END) AS decimal(2,1)) AS AvgFemaleQ7
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ8 END) AS decimal(2,1)) AS AvgFemaleQ8
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ9 END) AS decimal(2,1)) AS AvgFemaleQ9
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ10 END) AS decimal(2,1)) AS AvgFemaleQ10
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ11 END) AS decimal(2,1)) AS AvgFemaleQ11
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ12 END) AS decimal(2,1)) AS AvgFemaleQ12
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ13 END) AS decimal(2,1)) AS AvgFemaleQ13
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ14 END) AS decimal(2,1)) AS AvgFemaleQ14
    ,CAST(AVG(CASE surveyGender WHEN 'F' THEN surveyQ15 END) AS decimal(2,1)) AS AvgFemaleQ15
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ1 END) AS decimal(2,1)) AS AvgMaleQ1
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ2 END) AS decimal(2,1)) AS AvgMaleQ2
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ3 END) AS decimal(2,1)) AS AvgMaleQ3
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ4 END) AS decimal(2,1)) AS AvgMaleQ4
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ5 END) AS decimal(2,1)) AS AvgMaleQ5
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ6 END) AS decimal(2,1)) AS AvgMaleQ6
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ7 END) AS decimal(2,1)) AS AvgMaleQ7
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ8 END) AS decimal(2,1)) AS AvgMaleQ8
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ9 END) AS decimal(2,1)) AS AvgMaleQ9
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ10 END) AS decimal(2,1)) AS AvgMaleQ10
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ11 END) AS decimal(2,1)) AS AvgMaleQ11
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ12 END) AS decimal(2,1)) AS AvgMaleQ12
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ13 END) AS decimal(2,1)) AS AvgMaleQ13
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ14 END) AS decimal(2,1)) AS AvgMaleQ14
    ,CAST(AVG(CASE surveyGender WHEN 'M' THEN surveyQ15 END) AS decimal(2,1)) AS AvgMaleQ15

    ,COUNT(CASE surveyQ1 WHEN 4 THEN surveyQ1 END) AS countStronglyAgreeQ1
    ,COUNT(CASE surveyQ2 WHEN 4 THEN surveyQ2 END) AS countStronglyAgreeQ2
    ,COUNT(CASE surveyQ3 WHEN 4 THEN surveyQ3 END) AS countStronglyAgreeQ3





FROM Survey
WHERE surveySubject = @surveySubject
    AND surveyLevel = @surveyLevel
    AND surveyTeacher = @surveyTeacher
    AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo

[Edit:] With ADO it would actually be more efficient to return lots of OUTPUT parameters instead of a one row RecordSet.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-06-26 : 10:20:13

Thank you for confirming my insanity clause

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dsmith8
Starting Member

6 Posts

Posted - 2007-06-26 : 10:36:32

Thank you both! I'm going to play with this tonight and see what happens.

'horrible non-normalized table' - are you refering to my table lol

It is normalised, has primary and foreign keys and all the rest of it! I can prove it!!!

My database knowledge is 'good' (not excellent) so I think I can say the table is ok. Would it be useful to see the table structure?!

Well, I'll give your suggestions a blast first.

Do you mind me asking, which would be best? A dataset? or the 'Cast' approach?

Thanks again

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-26 : 10:40:40

quote:
It is normalised,

Google First Normal Form.

dsmith8
Starting Member

6 Posts

Posted - 2007-06-26 : 10:46:33

quote:
Originally posted by Ifor

quote:
It is normalised,

Google First Normal Form.

Thank you - I have.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-06-26 : 10:53:52

Datasets have a larger overhead than a simple recordset.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-06-27 : 16:29:43

[code]
ELECT (convert(decimal(2,1),Count(surveyQ14))/@total)*100 AS countStronglyDisagreeQ14
FROM Survey
WHERE surveySubject = @surveySubject
AND surveyLevel = @surveyLevel
AND surveyTeacher = @surveyTeacher
AND surveyQ14 = 1.0
AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo;
[/code]

You're wrong about your database being normalized. If it were, you wouldn't have multiple columns per row with names like surveyQ1, surveyQ2, etc. Instead, you'd have one row per question, which would allow you to achieve the results you want with a single query using GROUP BY.

Google "First Normal Form" again (assuming you're not lying about already having done so), and this time actually read the result pages.

dsmith8
Starting Member

6 Posts

Posted - 2007-06-28 : 03:45:45

Thank you - I did read it and I did understand it. So I guess that means I am not a liar!

I'm nothing but grateful for the help people offer on here. Still, there is no need to be rude!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-28 : 05:44:56

With percentage too...

SELECT		COUNT(*) AS [Total number of results],
		SUM(CASE WHEN surveyGender = 'F' THEN 1 ELSE 0 END) AS CountTotalFemales,
		SUM(CASE WHEN surveyGender = 'M' THEN 1 ELSE 0 END) AS CountTotalMales,
		CAST(AVG(surveyQ1) AS DECIMAL(4, 1)) AS AvgQ1,
		CAST(AVG(surveyQ2) AS DECIMAL(4, 1)) AS AvgQ2,
		CAST(AVG(surveyQ3) AS DECIMAL(4, 1)) AS AvgQ3,
		CAST(AVG(surveyQ4) AS DECIMAL(4, 1)) AS AvgQ4,
		CAST(AVG(surveyQ5) AS DECIMAL(4, 1)) AS AvgQ5,
		CAST(AVG(surveyQ6) AS DECIMAL(4, 1)) AS AvgQ6,
		CAST(AVG(surveyQ7) AS DECIMAL(4, 1)) AS AvgQ7,
		CAST(AVG(surveyQ8) AS DECIMAL(4, 1)) AS AvgQ8,
		CAST(AVG(surveyQ9) AS DECIMAL(4, 1)) AS AvgQ9,
		CAST(AVG(surveyQ10) AS DECIMAL(4, 1)) AS AvgQ10,
		CAST(AVG(surveyQ11) AS DECIMAL(4, 1)) AS AvgQ11,
		CAST(AVG(surveyQ12) AS DECIMAL(4, 1)) AS AvgQ12,
		CAST(AVG(surveyQ13) AS DECIMAL(4, 1)) AS AvgQ13,
		CAST(AVG(surveyQ14) AS DECIMAL(4, 1)) AS AvgQ14,
		CAST(AVG(surveyQ15) AS DECIMAL(4, 1)) AS AvgQ15
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ1 END) AS DECIMAL(4, 1)) AS AvgFemaleQ1,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ2 END) AS DECIMAL(4, 1)) AS AvgFemaleQ2,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ3 END) AS DECIMAL(4, 1)) AS AvgFemaleQ3,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ4 END) AS DECIMAL(4, 1)) AS AvgFemaleQ4,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ5 END) AS DECIMAL(4, 1)) AS AvgFemaleQ5,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ6 END) AS DECIMAL(4, 1)) AS AvgFemaleQ6,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ7 END) AS DECIMAL(4, 1)) AS AvgFemaleQ7,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ8 END) AS DECIMAL(4, 1)) AS AvgFemaleQ8,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ9 END) AS DECIMAL(4, 1)) AS AvgFemaleQ9,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ10 END) AS DECIMAL(4, 1)) AS AvgFemaleQ10,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ11 END) AS DECIMAL(4, 1)) AS AvgFemaleQ11,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ12 END) AS DECIMAL(4, 1)) AS AvgFemaleQ12,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ13 END) AS DECIMAL(4, 1)) AS AvgFemaleQ13,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ14 END) AS DECIMAL(4, 1)) AS AvgFemaleQ14,
		CAST(AVG(CASE WHEN surveyGender = 'F' THEN surveyQ15 END) AS DECIMAL(4, 1)) AS AvgFemaleQ15,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ1 END) AS DECIMAL(4, 1)) AS AvgMaleQ1,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ2 END) AS DECIMAL(4, 1)) AS AvgMaleQ2,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ3 END) AS DECIMAL(4, 1)) AS AvgMaleQ3,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ4 END) AS DECIMAL(4, 1)) AS AvgMaleQ4,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ5 END) AS DECIMAL(4, 1)) AS AvgMaleQ5,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ6 END) AS DECIMAL(4, 1)) AS AvgMaleQ6,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ7 END) AS DECIMAL(4, 1)) AS AvgMaleQ7,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ8 END) AS DECIMAL(4, 1)) AS AvgMaleQ8,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ9 END) AS DECIMAL(4, 1)) AS AvgMaleQ9,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ10 END) AS DECIMAL(4, 1)) AS AvgMaleQ10,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ11 END) AS DECIMAL(4, 1)) AS AvgMaleQ11,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ12 END) AS DECIMAL(4, 1)) AS AvgMaleQ12,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ13 END) AS DECIMAL(4, 1)) AS AvgMaleQ13,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ14 END) AS DECIMAL(4, 1)) AS AvgMaleQ14,
		CAST(AVG(CASE WHEN surveyGender = 'M' THEN surveyQ15 END) AS DECIMAL(4, 1)) AS AvgMaleQ15
		CAST(SUM(CASE WHEN surveyQ1 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ1, 
		CAST(SUM(CASE WHEN surveyQ2 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ2, 
		CAST(SUM(CASE WHEN surveyQ3 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ3, 
		CAST(SUM(CASE WHEN surveyQ4 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ4, 
		CAST(SUM(CASE WHEN surveyQ5 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ5, 
		CAST(SUM(CASE WHEN surveyQ6 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ6, 
		CAST(SUM(CASE WHEN surveyQ7 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ7, 
		CAST(SUM(CASE WHEN surveyQ8 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ8, 
		CAST(SUM(CASE WHEN surveyQ9 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ9, 
		CAST(SUM(CASE WHEN surveyQ10 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ10, 
		CAST(SUM(CASE WHEN surveyQ11 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ11, 
		CAST(SUM(CASE WHEN surveyQ12 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ12, 
		CAST(SUM(CASE WHEN surveyQ13 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ13, 
		CAST(SUM(CASE WHEN surveyQ14 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ14, 
		CAST(SUM(CASE WHEN surveyQ15 = 4 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyAgreeQ15, 
		CAST(SUM(CASE WHEN surveyQ1 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ1, 
		CAST(SUM(CASE WHEN surveyQ2 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ2, 
		CAST(SUM(CASE WHEN surveyQ3 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ3, 
		CAST(SUM(CASE WHEN surveyQ4 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ4, 
		CAST(SUM(CASE WHEN surveyQ5 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ5, 
		CAST(SUM(CASE WHEN surveyQ6 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ6, 
		CAST(SUM(CASE WHEN surveyQ7 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ7, 
		CAST(SUM(CASE WHEN surveyQ8 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ8, 
		CAST(SUM(CASE WHEN surveyQ9 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ9, 
		CAST(SUM(CASE WHEN surveyQ10 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ10, 
		CAST(SUM(CASE WHEN surveyQ11 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ11, 
		CAST(SUM(CASE WHEN surveyQ12 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ12, 
		CAST(SUM(CASE WHEN surveyQ13 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ13, 
		CAST(SUM(CASE WHEN surveyQ14 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ14, 
		CAST(SUM(CASE WHEN surveyQ15 = 3 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countAgreeQ15, 
		CAST(SUM(CASE WHEN surveyQ1 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ1, 
		CAST(SUM(CASE WHEN surveyQ2 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ2, 
		CAST(SUM(CASE WHEN surveyQ3 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ3, 
		CAST(SUM(CASE WHEN surveyQ4 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ4, 
		CAST(SUM(CASE WHEN surveyQ5 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ5, 
		CAST(SUM(CASE WHEN surveyQ6 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ6, 
		CAST(SUM(CASE WHEN surveyQ7 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ7, 
		CAST(SUM(CASE WHEN surveyQ8 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ8, 
		CAST(SUM(CASE WHEN surveyQ9 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ9, 
		CAST(SUM(CASE WHEN surveyQ10 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ10, 
		CAST(SUM(CASE WHEN surveyQ11 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ11, 
		CAST(SUM(CASE WHEN surveyQ12 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ12, 
		CAST(SUM(CASE WHEN surveyQ13 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ13, 
		CAST(SUM(CASE WHEN surveyQ14 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countDisagreeQ14, 
		CAST(SUM(CASE WHEN surveyQ15 = 2 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ15, 
		CAST(SUM(CASE WHEN surveyQ1 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ1, 
		CAST(SUM(CASE WHEN surveyQ2 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ2, 
		CAST(SUM(CASE WHEN surveyQ3 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ3, 
		CAST(SUM(CASE WHEN surveyQ4 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ4, 
		CAST(SUM(CASE WHEN surveyQ5 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ5, 
		CAST(SUM(CASE WHEN surveyQ6 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ6, 
		CAST(SUM(CASE WHEN surveyQ7 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ7, 
		CAST(SUM(CASE WHEN surveyQ8 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ8, 
		CAST(SUM(CASE WHEN surveyQ9 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ9, 
		CAST(SUM(CASE WHEN surveyQ10 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ10, 
		CAST(SUM(CASE WHEN surveyQ11 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ11, 
		CAST(SUM(CASE WHEN surveyQ12 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ12, 
		CAST(SUM(CASE WHEN surveyQ13 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ13, 
		CAST(SUM(CASE WHEN surveyQ14 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ14, 
		CAST(SUM(CASE WHEN surveyQ15 = 1 THEN 100.0 ELSE 0.0 END) / COUNT(*) AS DECIMAL(4, 1)) AS countStronglyDisagreeQ15, 
FROM		Survey
WHERE		surveySubject = @surveySubject
		AND surveyLevel = @surveyLevel
		AND surveyTeacher = @surveyTeacher
		AND surveyCompletion BETWEEN @surveyFrom AND @surveyTo

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-06-28 : 07:43:13

Thus we confirm that Peter is a nut case. (pun intended)

dsmith8
Starting Member

6 Posts

Posted - 2007-06-28 : 08:01:13

Thank you very very much Peter! It works brilliantly despite my poor table design.

I'm going to try and scrub up on my skills and sort my table out like the other techies suggested but in the meantime, your code does just the trick. There are some very clever techies out there!!! :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources