I am trying to make a query that determines the most favorable, time-dependent prices for a certain period, but also does not exclude objects for which no prices are available for this period.
Since the price periods can be completely flexible, it is not possible to work with larger and smaller queries.
I therefore thought that the smallest difference between start and end should be determined and the smallest price taken (if there are several hits).

Price table

id | oid | start | end | price
----------------------------------------
1 | 1000 | 2024-04-13 | 2025-04-13 | 120
2 | 1001 | 2024-04-13 | 2025-04-13 | 200
3 | 1002 | 2024-05-04 | 2024-08-13 | 1225
4 | 1002 | 2024-08-14 | 2024-10-14 | 1400
5 | 1002 | 2024-10-15 | 2024-12-31 | 200
6 | 1006 | 2024-01-01 | 2024-03-01 | 5250

Object table

id | title | published
----------------------
1000 | object0 | 1
1001 | object1 | 1
1002 | object2 | 1
1006 | object6 | 1

So far I have had this query:

SELECT p.id AS pid
FROM
    prices AS p
    LEFT JOIN objects o ON o.id = p.oid
    INNER JOIN (
        SELECT oid, MIN(
                ABS(
                    DATEDIFF(start, '2024-06-08') + DATEDIFF(end, '2024-06-15')
                )
            ) AS diff
        FROM prices
            LEFT JOIN objects o ON o.id = oid
        WHERE
            o.published = 1
        GROUP BY
            oid
    ) p2 ON p.oid = p2.oid
    AND (
        ABS(
            DATEDIFF(p.start, '2024-06-08') + DATEDIFF(p.end, '2024-06-15')
        )
    ) = p2.diff
WHERE
    o.published = 1

The problem with this query is that it does not return the cheapest price if there is a price with an end date that has a higher negative amount.

So I did that:

SELECT p.id AS pid
FROM
    prices AS p
    LEFT JOIN objects o ON o.id = p.oid
    INNER JOIN (
        SELECT oid, MIN(
                ABS(
                    DATEDIFF(start, '2024-06-08') + DATEDIFF(end, '2024-06-15')
                )
            ) AS diff
        FROM prices
            LEFT JOIN objects o ON o.id = oid
        WHERE
            o.published = 1
            AND DATEDIFF(end, '2024-06-15') >= 0 /* NEW */
        GROUP BY
            oid
    ) p2 ON p.oid = p2.oid
    AND (
        ABS(
            DATEDIFF(p.start, '2024-06-08') + DATEDIFF(p.end, '2024-06-15')
        )
    ) = p2.diff
WHERE
    o.published = 1

This (I believe) always produces the lowest prices, BUT it ignores properties that only have prices in the past. However, these should be taken into account.