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.
[code=sql]SELECT RTRIM (tbl_Students.I d_No) AS IdNo, RTRIM(LastName) + ', ' + RTRIM(FirstName ) + ' ' + LEFT(MiddleName ,1) AS Names,
RTRIM(tbl_Cours es.Abbreviation ) + '/' + RTRIM(tbl_Enrol lStatus.YearLev el) + '/' + RTRIM(tbl_Enrol lStatus.Section ) AS CrsYrSec,
((SUM(ISNULL(As sessedAmount, 0)) + SUM(ISNULL(tbl_ AssessedFees.Ad justDebit,0))) - SUM(ISNULL(tbl_ AssessedFees.Ad justCredit, 0))) AS AssessedAmt,
SUM(ISNULL(tbl_ Payments.Amount , 0)) AS PaymentAmt,
((SUM(ISNULL(As sessedAmount, 0)) + SUM(ISNULL(tbl_ AssessedFees.Ad justDebit,0))) - (SUM(ISNULL(tbl _AssessedFees.A djustCredit, 0)) + SUM(ISNULL(tbl_ Payments.Amount , 0)))) AS RemainingBalanc e
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_Cod e = tbl_Curriculum. Curriculum_Code
INNER JOIN tbl_Courses ON tbl_Curriculum. Course_Code = tbl_Courses.Cou rse_Code
INNER JOIN tbl_EnrollStatu s ON tbl_CourseTaken .Taken_Code = tbl_EnrollStatu s.Taken_Code
INNER JOIN tbl_Enrollees ON tbl_EnrollStatu s.Enroll_Code = tbl_Enrollees.E nroll_Code
INNER JOIN tbl_AssessedFee s ON tbl_EnrollStatu s.Enroll_Code = tbl_AssessedFee s.Enroll_Code
LEFT JOIN tbl_Payments ON tbl_EnrollStatu s.Enroll_Code = tbl_Payments.En roll_Code
INNER JOIN tbl_ReceiptNo ON tbl_Payments.Re ceipt_Code = tbl_ReceiptNo.R eceipt_Code
WHERE tbl_Enrollees.S chedule_Code = @schedCode AND
tbl_ReceiptNo.P ayStatus = 'Valid'
GROUP BY tbl_Students.Id _No, LastName, FirstName, MiddleName, tbl_Courses.Abb reviation. tbl_EnrollStatu s.YearLevel,
tbl_EnrollStatu s.Section
ORDER BY tbl_Names ASC[/code]
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.
[code=sql]SELECT RTRIM (tbl_Students.I d_No) AS IdNo, RTRIM(LastName) + ', ' + RTRIM(FirstName ) + ' ' + LEFT(MiddleName ,1) AS Names,
RTRIM(tbl_Cours es.Abbreviation ) + '/' + RTRIM(tbl_Enrol lStatus.YearLev el) + '/' + RTRIM(tbl_Enrol lStatus.Section ) AS CrsYrSec,
((SUM(ISNULL(As sessedAmount, 0)) + SUM(ISNULL(tbl_ AssessedFees.Ad justDebit,0))) - SUM(ISNULL(tbl_ AssessedFees.Ad justCredit, 0))) AS AssessedAmt,
SUM(ISNULL(tbl_ Payments.Amount , 0)) AS PaymentAmt,
((SUM(ISNULL(As sessedAmount, 0)) + SUM(ISNULL(tbl_ AssessedFees.Ad justDebit,0))) - (SUM(ISNULL(tbl _AssessedFees.A djustCredit, 0)) + SUM(ISNULL(tbl_ Payments.Amount , 0)))) AS RemainingBalanc e
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_Cod e = tbl_Curriculum. Curriculum_Code
INNER JOIN tbl_Courses ON tbl_Curriculum. Course_Code = tbl_Courses.Cou rse_Code
INNER JOIN tbl_EnrollStatu s ON tbl_CourseTaken .Taken_Code = tbl_EnrollStatu s.Taken_Code
INNER JOIN tbl_Enrollees ON tbl_EnrollStatu s.Enroll_Code = tbl_Enrollees.E nroll_Code
INNER JOIN tbl_AssessedFee s ON tbl_EnrollStatu s.Enroll_Code = tbl_AssessedFee s.Enroll_Code
LEFT JOIN tbl_Payments ON tbl_EnrollStatu s.Enroll_Code = tbl_Payments.En roll_Code
INNER JOIN tbl_ReceiptNo ON tbl_Payments.Re ceipt_Code = tbl_ReceiptNo.R eceipt_Code
WHERE tbl_Enrollees.S chedule_Code = @schedCode AND
tbl_ReceiptNo.P ayStatus = 'Valid'
GROUP BY tbl_Students.Id _No, LastName, FirstName, MiddleName, tbl_Courses.Abb reviation. tbl_EnrollStatu s.YearLevel,
tbl_EnrollStatu s.Section
ORDER BY tbl_Names ASC[/code]
Comment