CODEWITHSUNDEEP
mysqlsql
MikeP
MikeP

Reputation: 195

MySql JOIN on most recent start_date?

I have two tables, one with transactions (with date). The other with a percentage and date the percentage it went into effect (assume 00:00:00). The percentage remains in effect until a new percent goes into effect. I need to join on the percentage that was in effect when the transaction happened.

transactions_table

event_date     amount
2011-01-01     230
2011-02-18     194
2011-03-22     56
2011-04-30     874

percent_table

effective     percent
2010-12-30    15
2011-03-05    25
2011-04-12    30

The result I'm looking for is:

event_date     amount     percent
2011-01-01     230        15
2011-02-18     194        15
2011-03-22     56         25
2011-04-30     874        30

I've tried:

SELECT t.event_date, t.amount, p.percent 
FROM transactions_table AS t 
LEFT JOIN percent_table AS p ON t.event_date >= p.effective 
ORDER BY `t`.`event_date` DESC LIMIT 0 , 30;

That gives me, seemingly random percentages. It seems to me like I need to get the greatest date >= p.effective, not just any random date >= p.effective.

I tried:

SELECT t.event_date, p.percent 
FROM bedic_sixsummits_transactions AS t 
LEFT JOIN bedic_sixsummits_percent AS p ON MAX(t.event_date >= p.effective) 
ORDER BY `t`.`event_date` DESC LIMIT 0 , 30

but MySQL just laughed at my feeble attempt.

How can I do this?

Upvotes: 1

Views: 102

Answers (2)

Olivier Coilland
Olivier Coilland

Reputation: 3096

Even more simpler and with no subquery:

SELECT event_date, amount, MAX(_percent) as _percent
FROM transactions_table
LEFT JOIN percent_table p1 ON event_date >= effective
GROUP BY event_date, amount
ORDER BY event_date;

http://sqlfiddle.com/#!3/e8ca3/17/0

Note that it is possible because of the business model involved. If you wan't to retrieve other fields of the percent_table it won't be appropriate anymore :/

Upvotes: 1

Fred Sobotka
Fred Sobotka

Reputation: 5332

SELECT t.event_date, t.amount, p.percent
FROM bedic_sixsummits_transactions AS t
LEFT JOIN bedic_sixsummits_percent AS p
ON p.effective = 
   ( SELECT MAX( p2.effective ) FROM bedic_sixsummits_percent AS p2
     WHERE p2.effective <= t.event_date
   )
ORDER BY t.event_date DESC LIMIT 0 , 30

Upvotes: 1

Related Questions