Sql statement to return a list of partial dues

Question

0.00/5 (No votes)

See more:

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 1-Oct-19 12:55pm

katela

Updated 1-Oct-19 15:05pm

Add a Solution

2 solutions

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.");

Posted 1-Oct-19 14:01pm

rmpbklynnyc

Comments

katela 2-Oct-19 10:03am

Thanks

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

MadMyche · Accepted Answer · 2019-10-01T15:05:00

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

SQL

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

SQL

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

SQL

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.