Need to get data for months no payment was made

Reply

•
•
Join Date: Jan 2009
Posts: 12
Reputation: closetosane is an unknown quantity at this point 
Solved Threads: 1
closetosane closetosane is offline Offline
Newbie Poster

Need to get data for months no payment was made

 
0
  #1
Jul 5th, 2009
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.
MySQL Syntax (Toggle Plain Text) 
  1. strSQL = “ SELECT  C_LName, C_FName, C_SIN, B_Month "
  2. strSQL = strSQL & " FROM Client, Payment_Board, Payment "
  3. strSQL = strSQL & " WHERE C_No =" & Store_Numb
  4. strSQL = strSQL & " AND C_Delete <>" & 1
  5. strSQL = strSQL & " AND B_Month BETWEEN '" & dtIniDate & "' AND '" & dtEndDate & "'" 
  6. strSQL = strSQL & " AND B_SIN = C_SIN "
  7. strSQL = strSQL & " AND B_ID = P_ID "
  8. strSQL = strSQL & " AND P_Type =" & "'" & SetMonthlyDues & "'"
  9. strSQL = strSQL & " AND B_AmountPaid = P_Amount"
  10. strSQL = strSQL & " ORDER BY C_LName, C_FName, B_Month "
  11. %><!--#include file="../Scripts/DbExe.asp"--><%
  12. Do Until objRS.EOF
  13.  
MySQL Syntax (Toggle Plain Text) 
  1. strSQL = “ SELECT  C_LName, C_FName, C_SIN, B_Month "
  2. strSQL = strSQL & " FROM Client, Payment_Board, Payment "
  3. ‘C_No is the store id being compared to a variable identifying the store that is logged in
  4. strSQL = strSQL & " WHERE C_No =" & Store_Numb
  5. ‘C_Delete is to see if the client is still active
  6. strSQL = strSQL & " AND C_Delete <>" & 1
  7. ‘Checks to see if there is a record for that month, dtIniDate is first of month and dtEndDate is last day of month
  8. strSQL = strSQL & " AND B_Month BETWEEN '" & dtIniDate & "' AND '" & dtEndDate & "'" 
  9. strSQL = strSQL & " AND B_SIN = C_SIN "
  10. strSQL = strSQL & " AND B_ID = P_ID "
  11. ‘Determines the type of payment made
  12. strSQL = strSQL & " AND P_Type =" & "'" & SetMonthlyPayment & "'"
  13. ‘Ensures a full payment has been made
  14. strSQL = strSQL & " AND B_AmountPaid = P_Amount"
  15. strSQL = strSQL & " ORDER BY C_LName, C_FName, B_Month "
  16. %><!--#include file="../Scripts/DbExe.asp"--><%
  17. Do Until objRS.EOF
  18.  

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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2009
Posts: 12
Reputation: closetosane is an unknown quantity at this point 
Solved Threads: 1
closetosane closetosane is offline Offline
Newbie Poster

Re: Need to get data for months no payment was made

 
0
  #2
Jul 19th, 2009
Problem Solved:
MySQL Syntax (Toggle Plain Text) 
  1. strSQL = " SELECT C.C_LName, C.C_FName, Max(P.P_Month) AS dtLastPayment "
  2. strSQL = strSQL & " FROM (Client AS C INNER JOIN Payment_Board AS P "
  3. strSQL = strSQL & " ON C.C_SIN = P.P_SIN) "
  4. strSQL = strSQL & " INNER JOIN Pt AS Pt ON P.P_ID = Pt.Pt_ID "
  5. strSQL = strSQL & " WHERE C.C_Store_No =" & Store_Numb
  6. strSQL = strSQL & " AND P.P_Amount_Paid = Pt.Pt_Amount "
  7. strSQL = strSQL & " AND C.C_Delete <>" & 1
  8. strSQL = strSQL & " AND Pt.Pt_Type =" & "'" & SetMonthlyPayment & "'"
  9. 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.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MySQL Forum
Thread Tools Search this Thread



Tag cloud for MySQL
"use" 1 agplv3 alfresco amazon api artisticlicense aws bizspark breathalyzer camparingtocolumns changingprices cmg communityjournalism contentmanagement contractors copyright court crm data database design developer development distinct dui eliminate enter enterprise error eudora facebook form foss gartner gnu government gpl greenit groupware hiring hyperic images innerjoins insert ip joebrockmeier join keyword keywords kickfire laptop law legal license licensing linux maintenance managing mariadb matchingcolumns metron microsoft microsoftexchange mindtouch multiple music mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opengovernment opensource operand oracle penelope php priceupdating query referencedesign reorderingcolumns saas search select sharepoint simpledb spotify statement sugarcrm syntax techsupport thunderbird transparency update virtualization
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC