Query Question

[udwo]

hello all,

i have a query question. I have 2 tables, userdetails and payments. i have a script that grabs payments for all users based on date range selected:

SELECT payments.userid, payments.amount, payments.date, payments.subscrID, payments.txnid, userdetails.userDeactivate FROM payments LEFT JOIN userdetails ON payments.userid = userdetails.userid WHERE payments.date >= '$dateStart' AND payments.date <= '$dateEnd' ORDER BY $orderBy

i need to change the query to have the same functionality BUT look at last two payments for each userid that had a payment in the selected date range and compare the payments.subscrID if they are the same. if they are, i need to output YES, if they are different, output will be NO in the output.

right now my output is:
userid, amount, date, txnid, subscrID, userDeactivate

and need it to be;
userid, amount, date, txnid, subscrID, userDeactivate, YES/NO


... i can figure out how to do this so ANY help is much appreciated

[hyper]

You can use LIMIT and reverse the sort order.

[udwo]

OK, could you post an example of the LIMIT?


this is a short sample of the output for today:

zlata2 | $ 28.99 | 2020-05-03 08:43:19 | 27K204S | I-KK30 | 2020-06-03
tilke | $ 28.99 | 2020-05-03 08:21:38 | 29C700X | I-2LH6 | 2020-06-03
karinkaduri | $ 28.99 | 2020-05-03 08:16:33 | 88W414 | I-NM97 | 2020-06-03
silvieliberatore | $ 28.99 | 2020-05-03 08:06:57 | 2CS5830X | I-YVL5V | 2020-06-03

so for each userid i would have to look at the last 2 payments (including one showing in my selected dayrange) and show YES/NO at the end of each line if the subscriptionID was same/different

f.e. zlata2 subscriptionID is I-KK30 bit if it was I-KK25 in previous payment, i would show NO at the end

[hyper]

OK, could you post an example of the LIMIT?

Code: Select all

LIMIT 2

So you didn't really mean this then did you?

i need to change the query

[udwo]

the query i have only shows all the payments from the range selected. i don't know how to grab teh dates outside of the range to compare the subscriptionID