MySQL - search date range and group by lowest difference

user_16166881 0
Viewed 48

I need to find one price of all objects that is as near as possible to a desired date range people are searching for. One object can have multiple prices for different date ranges.

Price table

id objectid price start end
1 21 50 2021-01-01 2021-04-01
2 21 60 2021-04-02 2021-08-01
3 22 30 2021-01-01 2021-04-01
4 23 150 2021-01-01 2021-04-01
5 21 20 2021-08-02 2021-09-01
6 23 120 2021-07-01 2021-08-01

So lets say people are searching between 2021-05-01 and 2021-06-01 the results should be:

id objectid price
2 21 60
3 22 30
6 23 120

I'm working on this query, but somewhere i lost focus.

SELECT p.objectid,
    p.id
    p.start,
    p.end,
    p.price,
    ABS(DATEDIFF(p.start, '2021-05-01') + DATEDIFF(p.end, '2021-06-01')) diff
FROM
    prices AS p1
INNER JOIN(
    SELECT
        p.objectid,
        MIN(
            ABS(
                DATEDIFF(start, '2021-05-01') + DATEDIFF(end, '2021-06-01')
            )
        ) AS diff
    FROM
        prices p
    GROUP BY
        p.objectid
) p2
ON
    p1.objectid = p2.objectid AND diff = p2.diff
datediff join group-by mysql
2 Answers

You can achieve this with self join.
Try this below query

SELECT
    Y.id,
    Y.objectid,
    Y.price
FROM
    (
    SELECT
        *,
        ABS(
            DATEDIFF(`start`, '2021-05-01') + DATEDIFF(`end`, '2021-06-01')
        ) AS `diff`
    FROM
        price
) AS Y
INNER JOIN(
    SELECT
        *,
        MIN(
            ABS(
                DATEDIFF(`start`, '2021-05-01') + DATEDIFF(`end`, '2021-06-01')
            )
        ) AS diff
    FROM
        price
    GROUP BY
        objectid
) AS X
ON
    X.objectid = Y.objectid AND X.diff = Y.diff
ORDER BY
    Y.id

The o/p is this enter image description here

user_65928820

This is a solution that seams to work, but doing a join on the diff could cause blured results i guess.

SELECT p.objectid,
    p.id
    p.start,
    p.end,
    p.price
FROM
    prices AS p1
INNER JOIN(
    SELECT
        p.objectid,
        MIN(
            ABS(
                DATEDIFF(start, '2021-05-01') + DATEDIFF(end, '2021-06-01')
            )
        ) AS diff
    FROM
        prices p
    GROUP BY
        p.objectid
) p2
ON
    p1.objectid = p2.objectid AND (ABS(DATEDIFF(p.start, '2021-05-01') + DATEDIFF(p.end, '2021-06-01'))) = p2.diff
user_161668810
Related