Multiple updates using Group - 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 2000 Forums

SQL Server Development (2000)

Multiple updates using Group

Author

Topic

inrsence
Starting Member

48 Posts

Posted - 2001-07-06 : 13:33:16

Hello,

I'm writing some code to do some reporting on some test scores.

I essentially have a table that has a Score column.. with values from NULL through 20.

My desired final output will present the following:

ScoreRange varchar(12),
TotalScored (int),
PctScored decimal(5,2)

I want to have ranges like:

No response, 0-1, 2-3.. but need to make sure that they all exist so it graphs properly.

So I've written some code, but it's lengthy and ugly and I know there is something I could be doing to make this better.

It would ideal to use a case statement, but I wasn;t sure how to add the values up quite right.

Here is what I did so far.. using 2 temp tables.

-- Declare variables
DECLARE @TotalTests int
SELECT @TotalTests = Count(*) FROM Challenge_Data_16

-- Prepare the table to store test results in
CREATE TABLE #TempScores (Score int,TotalScored int DEFAULT 0)
CREATE TABLE #TestScores (ScoreRange varchar(12),TotalScored int DEFAULT 0, PctScored decimal(5,2) DEFAULT 0)

-- Insert the score values into temp table
INSERT INTO #TempScores (Score,TotalScored)
SELECT Score,Count(*)
FROM Challenge_Data_16
GROUP BY Score
ORDER BY Score

-- Create the rows we desire in the final table
INSERT INTO #TestScores (ScoreRange) VALUES('No response')
INSERT INTO #TestScores (ScoreRange) VALUES ('0-1')
INSERT INTO #TestScores (ScoreRange) VALUES ('2-3')
INSERT INTO #TestScores (ScoreRange) VALUES ('4-5')
INSERT INTO #TestScores (ScoreRange) VALUES ('6-7')
INSERT INTO #TestScores (ScoreRange) VALUES ('8-9')
INSERT INTO #TestScores (ScoreRange) VALUES ('10-11')
INSERT INTO #TestScores (ScoreRange) VALUES ('12-13')
INSERT INTO #TestScores (ScoreRange) VALUES ('14-15')
INSERT INTO #TestScores (ScoreRange) VALUES ('16-17')
INSERT INTO #TestScores (ScoreRange) VALUES ('18-19')
INSERT INTO #TestScores (ScoreRange) VALUES ('20')

-- Update the values
UPDATE #TestScores SET TotalScored = tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score IS NULL AND ScoreRange = 'No response'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 0 AND ScoreRange = '0-1'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 1 AND ScoreRange = '0-1'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 2 AND ScoreRange = '2-3'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 3 AND ScoreRange = '2-3'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 4 AND ScoreRange = '4-5'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 5 AND ScoreRange = '4-5'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 6 AND ScoreRange = '6-7'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 7 AND ScoreRange = '6-7'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 8 AND ScoreRange = '8-9'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 9 AND ScoreRange = '8-9'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 10 AND ScoreRange = '10-11'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 11 AND ScoreRange = '10-11'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 12 AND ScoreRange = '12-13'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 13 AND ScoreRange = '12-13'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 14 AND ScoreRange = '14-15'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 15 AND ScoreRange = '14-15'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 16 AND ScoreRange = '16-17'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 17 AND ScoreRange = '16-17'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 18 AND ScoreRange = '18-19'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 19 AND ScoreRange = '18-19'
UPDATE #TestScores SET TotalScored = #TestScores.TotalScored + tmp.TotalScored FROM #TempScores tmp WHERE tmp.Score = 20 AND ScoreRange = '20'

-- Update the percentages
UPDATE #TestScores
SET PctScored = CONVERT(decimal(5,2),CONVERT(decimal(5,2),TotalScored)/@TotalTests*100.00)

-- Report and clean up
SELECT * FROM #TempScores
SELECT * FROM #TestScores

DROP TABLE #TempScores
DROP TABLE #TestScores

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources