internal group by ???

[m0nkey]

OK...i have a query i was hoping maybe someone can graciously assist me with...the scenario is this...
I have a table that has customers that make regular payments. for example sake lets say that there are 6 columns like this in the payments table

payment_id   customer_id   paymentdate   payment_amount   payment_status   acct_status 
----------   -----------   -----------   --------------   --------------   -----------
123          bcb2100       12/10/2013    100.00           pending          Good
124          bnw8729       12/11/2013    20.00            posted           Paid_in_full
125          heu3926       12/13/2013    200.00           posted           good
126          bcb2100       12/14/2013    100.00           posted           good
127          bnw8729       12/15/2013    50.00            pending          declined

what i was hoping to determine was if the customer had "SKIPPED" payments...as in the above table i would like to return payment_id '123' which shows that customer bcb2100 made a payment that posted on the 14th but they still have a payment that is pending on the 10th and they are in arrears...as it stands now, my application does not identify customers with arrears payments that are still in GOOD status according to the last payments taken but there is a missing payment that needs to be made up for.

Does anyone have any ideas on how to approach this query?
Much appreicated...

 

[jpadie]

there must be more to your question that i have not understood. but would this not work

select distinct(id) from payments_table where payment_status='pending'

but perhaps a structural change would make this clearer? off the top of my head these would be a standard set of tables that would allow reconciliation and output to your accounting system.

users
products
prices (maps to products and has a date identifier so that you track price changes)
purchases (purchaseID, productID, price, vatcode, invoiceID)
invoices (invoiceID, userID, date,status) (status might be draft, issued or cancelled)
payments (paymentID, invoiceID, amount) (can be many payments for one id, if your business model allows)
credits (creditID, invoiceID, amount) (if this needs to be many-to-many you might consider dropping the invoiceID column and either going with a join table or having credits at the user level)
payment logs (log the transaction data posted and received from your merchant acquirer)

in this way you could just do a search of the 'skipped' users like so

select userID
from users u
join invoices i on i.userID = u.userID
join purchases p on i.invoiceID = p.invoiceID
join payments px on i.invoiceID = px.invoiceID
join credits c on i.invoiceID = c.invoiceID
where (sum(p.price) + sum(p.vat)) > (sum(ifnull(px.amount,0)) + sum(ifnull(c.amount,0)))
and i.status != 'cancelled'

more complex solutions might take data from the invoices payments and credits table and move them to purchase, sales and general ledgers with appropriate mapping to account codes. then the query becomes even simpler as you can just sum the general ledger for that client/user.

 

[jpadie]

oops
forgot

group by u.userid

at the end of the query above.

 

[m0nkey]

thanks for the reply...the query is a bit more complex than the original one you posted...sorry i will try to explain a bit more in depth...

i have customers that have a series of payments that are set up in the system...lets say that a person is making 100.00 payments every week on friday...if the customer is in good standing their status is 'GOOD'. (by the way, i am just lumping this all into one table for arguements sake since i dont want to list a buch of tables that are unnecessary...)
if they keep up with the payments that are 'GOOD'...now if they miss a payment, the customer is contacted about the payment and we change the status to 'DECLINED'. we continue to try to contact the customer till it is paid. but there are times when the customer does not make good on the payment and the next payment is here...unfortunately the application we use is proprietory and i cannot change the code for the reps...many times a rep will see a pending payment for today and run it. if the payment goes through, they will change the status from 'DECLINED' to 'GOOD', but what happens is that there was that missed payment...and because of this bad procudure, the payment may be missed for some time since the application does not display a payment history on the payments screen...its a bad layout but one that i have to live with unfortunately...my only recourse is to run some reports like this try and catch it from time to time...

so getting back to the example, i want to identify the missed pending payment...the customer in question can have a total of 10 payments set up...lets say there are 2 that are paid on time, the third is 'skipped' and the fourth is made on time...there are six remaining pending payments as well...so a total of 7 pending payments but one is in the past and i need to identify that past (skipped) payment...

so while i was explaining this i had a bit of an ah-ha moment...
i figured this may work to start...

select distinct(a.filenumber) from payments a
join cm.dbase b on b.filenumber = a.filenumber
where a.paymentstatus='pending'
and statusname = 'good'
and str_to_date(a.paymentdateformat,'%m/%d/%Y') < '2013-12-27'

this will return the accounts that are in arrears in a good status and not give me all the future pending payments...

its a good start...and thanks so much for the help...if you happen to have a better solutuion of addition to this, please throw it out...i wishg i could redo the DB a bit more since it is not very well set up but i am limited and at the mercy of the app developers...

thanks!!!

 

[jpadie]

How about stored triggers? Set a trigger on the table so that whenever the status is changed the nett credit balance is validated for that user and if there is a debit balance the status is returned to pending.

 

[jpadie]

something like this perhaps? then you would not need to monitor the situation actively as the client would never be put into good credit whilst there was outstanding payments.

although i'm not sure i have your full structure as the joins in the above query are redundant unless you are storing the status in the cm.dbase table. if that is correct then you want the triggers to be on that table instead.

test these on a dev version of your databases first as i have not been able to test them without structure and dummy data.

DELIMITER //
CREATE TRIGGER test_pending_update
BEFORE UPDATE ON payments
FOR EACH ROW
BEGIN
    IF  NEW.statusname = 'good' 
        AND 0 < (   
                SELECT COUNT(*) 
                FROM payments 
                WHERE paymentstatus='pending'
                AND filenumber = NEW.filenumber
                )
    THEN
        SET NEW.statusname='declined';
    ELSEIF NEW.statusname='good' and paymentstatus='pending'
    THEN
        SET NEW.statusname='declined';  
    END IF;
END;//

CREATE TRIGGER test_pending_update
BEFORE INSERT ON payments
FOR EACH ROW
BEGIN
    IF  NEW.statusname = 'good' 
        AND 0 < (   
                SELECT COUNT(*) 
                FROM payments 
                WHERE paymentstatus='pending'
                AND filenumber = NEW.filenumber
                )
    THEN
        SET NEW.statusname='declined';
    ELSEIF NEW.statusname='good' and paymentstatus='pending'
    THEN
        SET NEW.statusname='declined';  
    END IF;
END;//
DELIMITER ;