Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get all tax rates within a given date range

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

Tags:

2

Answers


  1. 0 
    SELECT `valid_from`, 
       COALESCE(`valid_to`, 
                MIN(`valid_from`) OVER (ORDER BY `valid_from` 
                                        ROWS BETWEEN 1 FOLLOWING
                                             AND UNBOUNDED FOLLOWING) - INTERVAL 1 DAY) AS `valid_to`,
       `rate`
FROM taxes

    fiddle

    The query assumes that there is no overlapped ranges.

    If you need in the output for definite range then select only those rows which overlaps with this range (add proper WHERE).

    Login or Signup to reply.
  2. 0

    For MySQL versions < 8.0 you could use a correlated subquery,

    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;

    See example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search