Member Avatar for closetosane

Hello,

I need to write a query for MySQL in vbscript that is just not coming to me. I have written it in vbscript with two SQL statements then did a date comparison in vbscript but it just grinds the server to a halt.

What I need to do is run thought a list of clients and determine the last month a full payment was made by the client then dump that info onto a webpage. So if a client has made a last full payment in January they will show up on the February list as a non-payment, but not on March’s or any other later month’s list as a non-payment. Likewise if the last full payment was made in February they need to only show up on the March’s list for owing a payment.

There are three tables in this query
1. Client (C_FName, C_LName, C_SIN(primary key))
2. Payment (P_Type, P_Amount, P_ID(primary key))
3. Payment_Board (B_Month, B_AmountPaid, B_ID(Foreign Key))

I have written some code that does the exact opposite of what I need but am wondering if it is close.

strSQL = “ SELECT  C_LName, C_FName, C_SIN, B_Month "
strSQL = strSQL & " FROM Client, Payment_Board, Payment "
strSQL = strSQL & " WHERE C_No =" & Store_Numb
strSQL = strSQL & " AND C_Delete <>" & 1
strSQL = strSQL & " AND B_Month BETWEEN '" & dtIniDate & "' AND '" & dtEndDate & "'" 
strSQL = strSQL & " AND B_SIN = C_SIN "
strSQL = strSQL & " AND B_ID = P_ID "
strSQL = strSQL & " AND P_Type =" & "'" & SetMonthlyDues & "'"
strSQL = strSQL & " AND B_AmountPaid = P_Amount"
strSQL = strSQL & " ORDER BY C_LName, C_FName, B_Month "
%><!--#include file="../Scripts/DbExe.asp"--><%
Do Until objRS.EOF
strSQL = “ SELECT  C_LName, C_FName, C_SIN, B_Month "
strSQL = strSQL & " FROM Client, Payment_Board, Payment "
‘C_No is the store id being compared to a variable identifying the store that is logged in
strSQL = strSQL & " WHERE C_No =" & Store_Numb
‘C_Delete is to see if the client is still active
strSQL = strSQL & " AND C_Delete <>" & 1
‘Checks to see if there is a record for that month, dtIniDate is first of month and dtEndDate is last day of month
strSQL = strSQL & " AND B_Month BETWEEN '" & dtIniDate & "' AND '" & dtEndDate & "'" 
strSQL = strSQL & " AND B_SIN = C_SIN "
strSQL = strSQL & " AND B_ID = P_ID "
‘Determines the type of payment made
strSQL = strSQL & " AND P_Type =" & "'" & SetMonthlyPayment & "'"
‘Ensures a full payment has been made
strSQL = strSQL & " AND B_AmountPaid = P_Amount"
strSQL = strSQL & " ORDER BY C_LName, C_FName, B_Month "
%><!--#include file="../Scripts/DbExe.asp"--><%
Do Until objRS.EOF

I was thinking of running this query once and creating a temp table or array then running the query again with the next month’s dates for dtIniDate and dtEndDate then comparing the values to see if the client is in the first table but not the second.

All I really need is for MySQL to go through each client’s records and if the last record found is for the month previous of the month I am looking for to put that clients data into the record set.

I was thinking of expanding the values for the BETWEEN statement then doing a comparison in vbscript but that would start to get bulky again, and I’m almost sure this could be done all in SQL.

Thanks in advance for any help with this issue.

  • 1 Contributor
  • 1 Reply
  • 181 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by closetosane

Dani AI

Generated

Nice work, — using an aggregate to capture each client’s most recent full monthly payment removes the expensive VBScript loop and is the right direction. A few practical refinements will make the result both more correct and easier to maintain across MySQL versions.

Prefer grouping by the client primary key (C_SIN) rather than by name fields: names can duplicate and modern MySQL modes (ONLY_FULL_GROUP_BY) require non-aggregated columns to be listed in GROUP BY or wrapped in an aggregate/ANY_VALUE. Calculate "last full payment" once in SQL and then filter that result for the month of interest (previous month) so the application only receives the final list to render.

A compact pattern using a derived table (last full payment per client) then joining back to Client keeps the logic clear and lets the optimizer use indexes:

SELECT c.C_LName, c.C_FName, c.C_SIN, lp.last_month
FROM Client c
JOIN (
  SELECT pb.B_SIN AS sin, MAX(pb.B_Month) AS last_month
  FROM Payment_Board pb
  JOIN Payment p ON pb.B_ID = p.P_ID
  WHERE p.P_Type = 'Monthly' AND pb.B_AmountPaid = p.P_Amount
  GROUP BY pb.B_SIN
) lp ON lp.sin = c.C_SIN
WHERE c.C_Store_No = ? AND lp.last_month = DATE_SUB(:target_month, INTERVAL 1 MONTH);

If running on MySQL 8+, a window-function approach (ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC)) can produce the same result and is often easier to extend (e.g., pick top-N recent payments). Other practical notes: add indexes on the join/date columns (Payment_Board on (B_SIN,B_Month)), store months in DATE/DATE-like types (or use YEAR_MONTH integers) to avoid string comparisons, avoid exact equality on FLOAT amounts (use DECIMAL or an epsilon), and pass dates as parameters rather than concatenating strings in VBScript to reduce injection risk and parsing overhead.

These changes keep the work inside the database, reduce network/CPU churn, and make the output deterministic even as MySQL versions and SQL modes evolve.

Member Avatar for closetosane

Problem Solved:

strSQL = " SELECT C.C_LName, C.C_FName, Max(P.P_Month) AS dtLastPayment "
strSQL = strSQL & " FROM (Client AS C INNER JOIN Payment_Board AS P "
strSQL = strSQL & " ON C.C_SIN = P.P_SIN) "
strSQL = strSQL & " INNER JOIN Pt AS Pt ON P.P_ID = Pt.Pt_ID "
strSQL = strSQL & " WHERE C.C_Store_No =" & Store_Numb
strSQL = strSQL & " AND P.P_Amount_Paid = Pt.Pt_Amount "
strSQL = strSQL & " AND C.C_Delete <>" & 1
strSQL = strSQL & " AND Pt.Pt_Type =" & "'" & SetMonthlyPayment & "'"
strSQL = strSQL & " GROUP BY C.C_LName, C.C_FName "

It's not prefect, but it runs in a fraction of the time and is easy to sort on the webpage. The tablename Pt has been shortened to fit on a single line for this post.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.