Select from two tables to find vat rate based on effective date

Question

Hi,

I have this two tables:

mysql> desc vat_rates;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| rate_id     | varchar(5)    | NO   | PRI | NULL    |       |
| name        | varchar(255)  | NO   |     | NULL    |       |
| type        | enum('O','I') | NO   |     | NULL    |       |
| default     | tinyint(1)    | YES  |     | 0       |       |
+-------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> desc vat_rates_details;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| rate_id     | varchar(10) | NO   |     | NULL    |       |
| effect_date | date        | NO   |     | NULL    |       |
| rate        | float       | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

I need a query that gives me vat_rates.name, vat_rates.rate_id, vat_rates_details.rate WHERE the effect_date is MAX() but <= than now().

Simplifying, I need to select all vat_rates with rate field being the last date based on effect_date but not in the future.

Example with data:

vat_rates:
rate_id = 'L9'
name = 'MyVatName'
type = 'O'
default = 1

vat_rates_details (line 1):
rate_id = 'L9'
effect_date = '2000-01-01'
rate = 20

vat_rates_details (line 2):
rate_id = 'L9'
effect_date = '2010-06-01'
rate = 19

vat_rates_details (line 3):
rate_id = 'L9'
effect_date = '2010-07-01'
rate = 21

The expected result:

+-------------+-------------+------+
| rate_id     | name        | rate |
+-------------+-------------+------+
| L9          | MyVatName   | 19   |
+-------------+-------------+------+

Thanks for your time :)

EDIT: Expected rate is 19 not 20. Tks Justin.

Answer 1

select rd.rate_id, r.name, rd.rate
from (
    select rate_id, max(effect_date) as MaxEffectDate
    from vat_rates_details
    where effect_date < now()
    group by rate_id
) rdm
inner join  vat_rates_details rd on rdm.rate_id = rd.rate_id and rdm.MaxEffectDate = rd.effect_date
inner join vat_rates r on rd.rate_id = r.rate_id

Answer 2

Other possible methods:

SELECT
    VR.name,
    VR.rate_id,
    VRD1.rate
FROM
    Vat_Rates VR
INNER JOIN Vat_Rate_Details VRD1 ON
    VRD1.rate_id = VR.rate_id AND
    VRD1.effect_date < NOW()        -- Is this the correct syntax for mySQL?
LEFT OUTER JOIN Vat_Rate_Details VRD2 ON
    VRD2.rate_id = VR.rate_id AND
    VRD2.effect_date < NOW() AND
    VRD2.effect_date > VRD1.effect_date

---

SELECT
    VR.name,
    VR.rate_id,
    VRD1.rate
FROM
    Vat_Rates VR
INNER JOIN Vat_Rate_Details VRD1 ON
    VRD1.rate_id = VR.rate_id AND
    VRD1.effect_date < NOW()
WHERE NOT EXISTS
(
    SELECT *
    FROM Vat_Rate_Details VRD2
    WHERE
        VRD2.rate_id = VR.rate_id AND
        VRD2.effect_date < NOW() AND
        VRD2.effect_date > VRD1.effect_date
)