Calculating percentage of 3 test forms

Please help me to simplify the script below. It has a lot of variables and a lot of values are getting assigned to them. Can I make it simpler?

It has to calculate the percentage of 3 test forms: pre, post and final.

```
DECLARE @tablefinal TABLE
(
RowID1 INT IDENTITY(1,1),
_CourseIDD int null,
_courseName varchar(1000) null,
_PRE INT NULL,
_POST int null,
_Feedback int null,
_final int NULL,
_Per decimal(18,2) null
)

DECLARE @rowCount INT,
@currentRow INT,
@@@CourseID INT,
@@@CourseName varchar (1000) --, @STUDENT_ID int = 1078

DECLARE @tableCourse TABLE
(
RowID INT IDENTITY(1,1),
_COURSE_ID INT NULL,
_COURSE_NAME varchar (1000) NULL
)

INSERT INTO @tableCourse (_COURSE_ID , _COURSE_NAME)
SELECT COURSE_ID,
COURSE_NAME
FROM DLC_COURSE C
WHERE COURSE_ID IN
(
SELECT CS.COURSESHD_COURSE_ID
FROM DLC_COURSE_SCHEDULE CS
INNER JOIN DLC_STUDENT_PAYMENT SP ON
CS.COURSESHD_ID = SP.STUDENT_PAYMENT_COURSESHD_ID
WHERE SP.STUDENT_PAYMENT_STUDENT_ID = @STUDENT_ID
AND CS.COURSESHD_STATUS=1
)
AND C.COURSE_STATUS=1

SELECT @rowCount = @@RowCount,
@currentRow = 1

WHILE @currentRow<=@rowCount
BEGIN
SELECT @@@CourseID = _COURSE_ID,
@@@CourseName = _COURSE_NAME
FROM @tableCourse
WHERE RowID = @currentRow

--do activity
--select @@@CourseID as CID, @@@CourseName as CN
SET @currentRow = @currentRow + 1

DECLARE @@Pre INT = 0,
@@Course INT = 0,
@@Post INT = 0,
@@Feedback INT = 0,
@@Final INT = 0

SELECT @@Pre = Count(*)
FROM DLC_ASSESSMENT_RESULT
WHERE RESULT_STUDENT_ID = @STUDENT_ID
AND RESULT_COURSE_ID = @@@CourseID
AND RESULT_STATUS='Pass'
AND RESULT_ASSESSMENT_TYPE=2

SELECT @@Course = ISNULL(COUNT(C.COURSE_ID),0)
FROM DLC_COURSE C
WHERE COURSE_ID IN
(
SELECT CS.COURSESHD_COUR

To sum up what this script does, just for the sake of simplicity:

-
Declare a result set table variable @tablefinal.

-
Declare variables for a counter, and for courses, and a commented out @STUDENT_ID (likely for testing purposes).

-
Declare another result set table variable @tableCourse to hold course IDs and course names during the transaction. This seems to be to filter out courses with no students or not on the schedule.

-
Set the counter start = 1 and end = total number of rows.

-
Begin to cycle through @tableCourse at row 1 using WHILE. I noticed that @student_id is not declared or set (other than the instance which is commented out) even though it's being used in multiple WHERE clauses. May be worth your while to look into why it is that way.

-
Count records for @@pre

-
Count records for @@courses

-
Count records for @@post

-
Count records for @@feedback

-
Count records for @@final

-
Declare @per for precentage

-
Calculate @per with a simple arithmetic operation

-
Shove everything into your results table @tablefinal and then select from it to see the result set.

To me this really seems like overkill, like a SQL programmer trying to show that he knows how to use variables. I think this could be much more simple, I won't rewrite the whole script but here are some ideas. If you decide to try that approach you will need to start from the ground up, you are welcome to post your new script in a new Code Review if you would like.

DECLARE @pre INT = (Count(*) FROM DLC_ASSESSMENT_RESULT WHERE RESULT_STATUS='Pass'  AND RESULT_ASSESSMENT_TYPE=2);    
-- Repeat DECLARE (Count(*)) for @post, @feedback, @final 

SELECT    
    C.COURSE_ID,     
    C.COURSE_NAME,    
    @pre AS 'Pre',    
    @post AS 'Post',    
    @feedback AS 'Feedback',    
    @final AS 'Final',    
    ((ISNULL(@Pre,0) + ISNULL(@@post,0) + ISNULL(@Final,0) ) / 3.00   * 100) AS 'Pre'

FROM DLC_COURSE C     
WHERE COURSE_ID IN   
-- Taken from the script, seems to be the only important JOIN
(     
    SELECT CS.COURSESHD_COURSE_ID     
    FROM DLC_COURSE_SCHEDULE CS     
    INNER JOIN DLC_STUDENT_PAYMENT SP ON     
        CS.COURSESHD_ID = SP.STUDENT_PAYMENT_COURSESHD_ID     
    WHERE CS.COURSESHD_STATUS=1         
)     
AND C.COURSE_STATUS=1    
-- This will aggregate the result set by course
GROUP BY C.COURSE_ID;

StackExchange Code Review Q#48001, answer score: 3