I have a table with tax rates. Each tax rate has two date fields: 'valid_from' and 'valid_to'. 'valid_to' can be NULL, then the tax rate is valid until a later tax rate is inserted. Now I want to determine all tax rates valid in a given period with :start_date and :end_date. The problem is that a tax rate without 'valid_to' is valid, viewed individually, as long as its valid_from is not after the given period. So I have to take into account whether such tax rates have been replaced by later tax rates. But it can also be the case that a tax rate with valid_to = NULL falls in part of the given period and then a new tax rate takes over the next part of the period. My approach still has problems with this:
SELECT *
FROM taxes t1
WHERE
t1.valid_from <= '2024-12-31'
AND (t1.valid_to >= '2022-07-01' OR t1.valid_to IS NULL)
AND NOT EXISTS (
SELECT 1
FROM taxes t2
WHERE t2.valid_from > t1.valid_from
AND t2.valid_from <= '2024-12-31'
AND (t2.valid_to IS NULL OR t2.valid_to > '2022-07-01')
AND t2.valid_from <= IFNULL(t1.valid_to, '2024-12-31')
);
CREATE TABLE `taxes` (
`valid_from` date NOT NULL,
`valid_to` date DEFAULT NULL,
`rate` decimal(4,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `taxes` (`valid_from`, `valid_to`, `rate`) VALUES
('2022-01-01', '2022-12-31', 10.00),
('2023-01-01', NULL, 12.00),
('2024-07-01', NULL, 15.00);
ALTER TABLE `taxes`
ADD PRIMARY KEY `valid_from` (`valid_from`);
COMMIT;
Expected result is all three rows, but the valid_from = '2023-01-01' is missing
For MySQL versions < 8.0 you could use a correlated subquery,
SELECT valid_from,
valid_to,
rate
FROM ( SELECT t.valid_from,
COALESCE( t.valid_to, DATE_SUB(( SELECT MIN(t2.valid_from)
FROM taxes t2
WHERE t2.valid_from > t.valid_from
), INTERVAL 1 DAY)
) AS valid_to,
t.rate
FROM taxes t
) tax
WHERE valid_from <= {range end date}
AND (valid_to >= {range start date} OR valid_to IS NULL);