0

I'm trying to select different types of data depending on a payment method which is a simple intersection table with columns "id" (INT PK) , "customer_id" (INT FK), "payment_method_id" (INT FK) and "date_changed" (DateTime)

Now, the customer may request the admin to change the payment method at any time, however I need to keep track of the payment method even if it's no longer in use simply because if I change the payment method halfway through the month I'll get invoiced the first half of the month using payment method 1 and the second half using payment method 2.

Now, depending on payment method, I will need to run different queries (using PHP in the backend) however, I need to find out, what payment method to use for a certain period of time.

How could I get to know if there is a payment method more recent, and more outdated, oh and I obviously cannot select the payment methods by month since they may be left unchanged for years on end before seeing a change, and since this is about invoicing my data needs to be trackable for 5 years...

How could I go about selecting the different data based on the methods I get back and their "date_changed" ?

Thanks!

4
  • I'd think about creating a new record each time there is a change and have a column called something like ValidFromDate. Your query would select the row with the maxdate before today. You also get to keep a history of all payment types and changes. Regards, Jul 6, 2016 at 13:03
  • @MichaelVincent That's what I did, each time you change payment methods, it adds a row, with the "changed_date" being the actual ValidFromDate you speak of, and right now I'm trying to figure out how to get the row with the biggest date before the one I selected Jul 6, 2016 at 14:20
  • Sorry - I must've misunderstood what you were describing. Jul 6, 2016 at 15:50
  • Show some sample data, plus the desired output.
    – Rick James
    Jul 12, 2016 at 2:00

2 Answers 2

0

Does using a sub query on the same table, something like this, work for you?

select * from yourtable where storedatetime = (
select max(storedatetime) from yourtable )
1
  • Unfortunately that is not possible as the only thing I have is when that certain payment method started. so subquery should be where subquery < selectedtime, and I also need to get both the dates back, so basically, I need the next change, and if no change it needs to return null or the end of the month... Jul 7, 2016 at 16:46
0

In the end what I did was select multiple times instead of just once, so for each result that I had I selected the next date_changed to get the period of time in between the two rows and when I had all the time periods I used different selects to get the results i needed.

It's messy code, but it works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.