Click here to Skip to main content
14,326,246 members
Rate this:
Please Sign up or sign in to vote.
Please assist me I am stuck with this SQL statement. I am a beginner with SQL, I am developing a C# school management software.

This is my Select query:
SELECT distinct RTRIM(Students.StudentNumber),RTRIM(Students.StudentSurname),RTRIM(Students.StudentFirstNames),RTRIM(CourseFeePayment.PaymentDue) from Students,CourseFeePayment,CourseFeePayment_Join where Students.StudentNumber=CourseFeePayment.StudentNumber and CourseFeePayment.CourseFeePaymentID=CourseFeePayment_Join.C_PaymentID and CourseFeePayment.SchoolYear=@d1 and CourseFeePayment.Student_Class=@d2 and CourseFeePayment_Join.Month=@d3 and CourseFeePayment.PaymentDue > 0 order by 2

        cmd.Parameters.AddWithValue("@d1", cmbAcademicYear.Text);
        cmd.Parameters.AddWithValue("@d2", cmbClass.Text);
        cmd.Parameters.AddWithValue("@d3", cmbMonth.Text);
        rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        dgw.Rows.Clear();
        while ((rdr.Read() == true))
         {
         dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4));
         } 


I need this to return a list per row of each students who partially owes from this specific class only for this specific month only. By Partially I mean if the class fee is for example $100 for January, if the student hasn't paid anything (owing full amount) must be ignored and only list students owing from greater than 0 to less than 100 in this case.

This query only works well when I have only one student and return the correct Payment Due. If I have many students in the same month, it returns them but with wrong Payment Due amount. If I have a student who has paid in installments like (paid more than once) but hasn't finished, it returns each installment of the same student on a different line with different Payment Due amount.

What I have tried:

Any help will be appreciated. Here are my tables:
Students Table:
StudentID (primary)
StudentNumber
StudentSurname
StudentFirstNames


CourseFeePayment Table:
CourseFeePaymentID (primary)
PaymentID
StudentNumber
SchoolYear
TotalFee  
PreviousDue
GrandTotal
TotalPaid
PaymentDue
Student_Class

CourseFeePayment_Join Table:
CourseFeePayment_J_ID
C_PaymentID
Fee
Month
Posted
Updated 1-Oct-19 15:05pm
Rate this:
Please Sign up or sign in to vote.

Solution 3

First things first; it is always helpful to post code that has some formatting to it, which makes it a lot easier for people to read what you are trying to do
SELECT distinct
	RTRIM(Students.StudentNumber)
,	RTRIM(Students.StudentSurname)
,	RTRIM(Students.StudentFirstNames)
,	RTRIM(CourseFeePayment.PaymentDue)
from 	Students
,	CourseFeePayment
,	CourseFeePayment_Join 
where	Students.StudentNumber			= CourseFeePayment.StudentNumber 
and	CourseFeePayment.CourseFeePaymentID	= CourseFeePayment_Join.C_PaymentID
and	CourseFeePayment.SchoolYear		= @d1
and	CourseFeePayment.Student_Class	= @d2
and	CourseFeePayment_Join.Month		= @d3
and	CourseFeePayment.PaymentDue		> 0
order by 2
For my next trick.... I'm going to eliminate the RTrims as it does nothing for numbers, and the text should have had it done at the time of insertion. I am also going to use some aliases on the table names, and I'm going to write in actual joins utilizing them.
Notice I can get the whole SELECT list neatly on one line
SELECT DISTINCT s.StudentNumber, s.StudentSurname, s.StudentFirstNames, p.PaymentDue

FROM	Students					s
INNER JOIN CourseFeePayment			p ON s.StudentNumber	= p.StudentNumber 
INNER JOIN CourseFeePayment_Join	j ON p.CourseFeePaymentID= j.C_PaymentID

WHERE	CourseFeePayment.SchoolYear		= @d1
AND		CourseFeePayment.Student_Class	= @d2
AND		CourseFeePayment_Join.Month		= @d3
AND		CourseFeePayment.PaymentDue		> 0

ORDER BY 2
Now I can see a problem forming with this query.... What this is actually going to do is pull a list of students who had a payment due and also made a payment in the selected month.

What we really are going to need to do is aggregate the amounts that are paid, by student, for the selected period
SELECT	p.StudentNumber, SUM(p.TotalPaid)
FROM	CourseFeePayment		p
INNER JOIN CourseFeePayment_Join	j ON p.CourseFeePaymentID = j.C_PaymentID
WHERE	p.StudentClass	= @d2
AND		j.Month			= @d3
GROUP BY p.StudentNumber
I really don't want to go too much further with this; as I do not know any of the data that you do have and what mechanisms are in place to INSERT payment records- this is not a design I would be using. Depending on what is all in going on would tell me if I was going to use this aggregation as a subquery, store it in a #temp table, or use it once via a CTE

What I would recommend is working with this in SSMS or some other SQL client application, as it would be much more versatile and convenient.
I would also recommend that when all is said and done with this query that you just convert it over to a Stored Procedure.
   
Comments
katela 1-Oct-19 22:06pm
   
Many thanks for your insight. I will try to implement it this way and see the results.
Thanks
Maciej Los 2-Oct-19 3:00am
   
Good job!
Rate this:
Please Sign up or sign in to vote.

Solution 1

you need have the sql statment in the execute.
you close the connection after read rows/output

eg

string tempValue;
                   string connStr = "server=localhost;user=root;database=mytestmydb;port=nnnn;password="+ppwd.ToString();
                   MySqlConnection conn = new MySqlConnection(connStr);
                   try
                   {


               DateTime dtnow = DateTime.Now;
               Console.WriteLine("Connecting to MySQL... time: "+ dtnow);
                       conn.Open();

                       string sql = "select * from books;";
                       MySqlCommand cmd = new MySqlCommand(sql, conn);
               MySqlDataReader mmreader = cmd.ExecuteReader();

               ////data rows
               while (mmreader.Read())
               {
                   tempValue = mmreader.GetString("author");

                   Console.WriteLine(tempValue.ToString());
               }






           }
                   catch (Exception ex)
                   {
                       Console.WriteLine(ex.ToString());
                   }

                   conn.Close();
                   Console.WriteLine("Done.");
   
Comments
katela 2-Oct-19 10:03am
   
Thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100