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&lt;=@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_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 CS.COURSESHD_ENDDATE &lt; GETDATE() 
        AND CS.COURSESHD_COURSE_ID = @@@CourseID   
    ) 
    AND C.COURSE_STATUS=1  

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

    SELECT @@Feedback = Count(*)  
    FROM FEEDBACK_RESULTS  
    WHERE QUESTION_ID IN 
    (
        SELECT QUESTION_ID 
        FROM FEEDBACK_QUESTIONS  
        WHERE QUESTION_COURSE = @@@CourseID 
        AND STATUS_ID = 1
    )  
    AND EMP_ID = @STUDENT_ID  

    SELECT @@Final = COUNT(*) 
    FROM DLC_ASSESSMENT_RESULT   
    WHERE RESULT_STUDENT_ID = @STUDENT_ID 
    AND RESULT_COURSE_ID = @@@CourseID  
    AND RESULT_STATUS = 'Pass'  
    AND RESULT_ASSESSMENT_TYPE = 3  

    --declare @@Pre int= 0, @@Course int = 0 , @@Post int  = 0, @@Feedback int = 0  , @@Final  int  = 0

    DECLARE @per decimal (18,2)   

    --select isnull(@@Pre,0), isnull(@@Post,0) , isnull(@@Final,0)
    SET @per = ((ISNULL(@@Pre,0) + ISNULL(@@Post,0) + ISNULL(@@Final,0) ) / 3.00   * 100)

    INSERT INTO @tablefinal
    SELECT  ISNULL(@@Course,0) AS COURSE,  
            @@@CourseName, 
            ISNULL(@@Pre,0) AS PRE,  
            ISNULL(@@Post,0) AS Post,
            ISNULL(@@Feedback,0) AS Feedback,
            ISNULL(@@Final,0) AS Final, 
            @per    
  END

SELECT * 
FROM @TABLEFINAL
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;