Need help on my query(sql statement)...

[josh2007]

hi all,

I'm having a hard time producing the correct sql statement for my tables.
below is my sql statement but i'm getting incorrect result. This particular query returns the sum of all the value of a particular field and it seems that it is ignoring the parameter which is entirely wrong.

Hope somebody can help me...thank you in advance.

SELECT RTRIM (tbl_Students.Id_No) AS IdNo, RTRIM(LastName) + ', ' + RTRIM(FirstName) + ' ' + LEFT(MiddleName,1) AS Names, 
                 RTRIM(tbl_Courses.Abbreviation) + '/' + RTRIM(tbl_EnrollStatus.YearLevel) + '/' + RTRIM(tbl_EnrollStatus.Section) AS CrsYrSec, 
                 ((SUM(ISNULL(AssessedAmount, 0)) + SUM(ISNULL(tbl_AssessedFees.AdjustDebit,0))) - SUM(ISNULL(tbl_AssessedFees.AdjustCredit, 0))) AS AssessedAmt,
                 SUM(ISNULL(tbl_Payments.Amount, 0)) AS PaymentAmt, 
                 ((SUM(ISNULL(AssessedAmount, 0)) + SUM(ISNULL(tbl_AssessedFees.AdjustDebit,0))) - (SUM(ISNULL(tbl_AssessedFees.AdjustCredit, 0)) + SUM(ISNULL(tbl_Payments.Amount, 0)))) AS RemainingBalance  
                 FROM tbl_Students.Id_No INNER JOIN tbl_CourseTaken ON tbl_Students.ID_No = tbl_CourseTaken.Id_No 
                     INNER JOIN tbl_Curriculum ON tbl_CourseTaken.Curriculum_Code = tbl_Curriculum.Curriculum_Code 
                      INNER JOIN tbl_Courses ON tbl_Curriculum.Course_Code = tbl_Courses.Course_Code 
                      INNER JOIN tbl_EnrollStatus ON tbl_CourseTaken.Taken_Code = tbl_EnrollStatus.Taken_Code 
                      INNER JOIN tbl_Enrollees ON tbl_EnrollStatus.Enroll_Code = tbl_Enrollees.Enroll_Code 
                      INNER JOIN tbl_AssessedFees ON tbl_EnrollStatus.Enroll_Code = tbl_AssessedFees.Enroll_Code 
                      LEFT JOIN tbl_Payments ON tbl_EnrollStatus.Enroll_Code = tbl_Payments.Enroll_Code 
                     INNER JOIN tbl_ReceiptNo ON tbl_Payments.Receipt_Code = tbl_ReceiptNo.Receipt_Code 
                 WHERE tbl_Enrollees.Schedule_Code = @schedCode AND 
                       tbl_ReceiptNo.PayStatus = 'Valid' 
                 GROUP BY tbl_Students.Id_No, LastName, FirstName, MiddleName, tbl_Courses.Abbreviation. tbl_EnrollStatus.YearLevel, 
                       tbl_EnrollStatus.Section 
                ORDER BY tbl_Names ASC

[debasisdas]

Are you sure the table contains data for desired output . Can you please post your table structures.