MySQL – SELECT Multiple Times Based on Changed Dates

MySQLselect

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!

Best Answer

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 )

Related Solutions

Mysql – How to optimize indexes on MySQL query with various sorts

WHERE playable_character = 0 AND
    date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now()

Start with the "=" item, then do the range:

INDEX(playable_character, date_published);

"Pagination", a la ORDER BY rating DESC LIMIT 4000, 1000; is best done by remember where you "left off". That way, you don't have scan over the 4000 records that you don't need.

Mysql – Slow subquery in SELECT statement

Try rewriting the sub-query as a join:

SELECT YEAR(b1.checkin) as year
     , MONTH(b1.checkin) as monthnum,
     , SUM(b2.total)
FROM bookings as b1
JOIN bookings as b2
    ON YEAR(b2.checkin) = YEAR(b1.checkin) 
   AND MONTH(b2.checkin) <= MONTH(b1.monthnum)
GROUP BY YEAR(b1.checkin),MONTH(b1.checkin)
ORDER BY YEAR(b2.checkin) ASC, MONTH(b2.checkin) ASC

I also removed CONCAT(UCASE(LEFT(MONTHNAME(checkin), 1)),SUBSTRING(MONTHNAME(checkin), 2)) as month, I don't think that will matter but it might be beneficial to do this once, and add it to the report in the application-layer.

You don't mention version of MySQL, but later versions have generated columns. You can try generating columns for YEAR and MONTH:

CREATE TABLE IF NOT EXISTS bookings 
( DBID int unsigned NOT NULL AUTO_INCREMENT
, checkin date NOT NULL
, checkout date NOT NULL
, total int NOT NULL

, checkin_year SMALLINT as (YEAR(checkin))
, checkin_month SMALLINT as (MONTH(checkin)) 

,    PRIMARY KEY (DBID, checkin)
,    KEY total (total)

,    KEY (checkin_year, checkin_month)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11171 ;

and then use those columns in the query:

SELECT b1.checkin_year
     , b1.checkin_month,
     , SUM(b2.total)
FROM bookings as b1
JOIN bookings as b2
    ON b2.checkin_year = b1.checkin_year 
   AND b2.checkin_month <= b1.checkin_month
GROUP BY b1.checkin_year, b1.checkin_month
ORDER BY b1.checkin_year, b1.checkin_month

BTW, what is the purpose of the index on TOTAL?

Related Question