Need to get data for months no payment was made

[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.

[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.