SELECT multiple times based on changed dates

Question

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!

• Michael Vincent: 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,
• Florian Humblot: @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
• Michael Vincent: Sorry - I must've misunderstood what you were describing.
• Rick James: Show some sample data, plus the desired output.

Answer 1

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 )

• Florian Humblot: 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...

Answer 2

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.