Leaderboard

On further experimentation, a hybrid method, which only calls on the function when the conventional query fails to find a matching date, seems to be the most efficient option. In my tests, this method was 30 times faster (but that would depend on how many unmatched dates there were) SELECT a.symbol , a.price as today , CASE WHEN b.price IS NOT NULL THEN b.price ELSE ( SELECT price FROM sampletable WHERE symbol = a.symbol AND date = nearestDate2(a.symbol, CURDATE() - interval 7 day) ) END as last_wk , CASE WHEN c.price IS NOT NULL THEN c.price ELSE ( SELECT price FROM sampletable WHERE symbol = a.symbol AND date = nearestDate2(a.symbol, CURDATE() - interval 1 month) ) END as last_mth , CASE WHEN d.price IS NOT NULL THEN d.price ELSE ( SELECT price FROM sampletable WHERE symbol = a.symbol AND date = nearestDate2(a.symbol, CURDATE() - interval 1 year) ) END as last_yr FROM sampletable a LEFT JOIN sampletable b ON a.symbol = b.symbol AND b.date = a.date - INTERVAL 7 DAY LEFT JOIN sampletable c ON a.symbol = c.symbol AND c.date = a.date - INTERVAL 1 MONTH LEFT JOIN sampletable d ON a.symbol = d.symbol AND d.date = a.date - INTERVAL 1 YEAR WHERE a.date = CURDATE(); +--------+-------+---------+----------+---------+ | symbol | today | last_wk | last_mth | last_yr | +--------+-------+---------+----------+---------+ | ABC | 19.24 | 5.96 | 5.30 | 3.86 | | DEF | 19.52 | 10.15 | 7.61 | 16.59 | | XYZ | 15.00 | 2.87 | 15.11 | 4.20 | +--------+-------+---------+----------+---------+

If you had data for every day for symbol it would be relatively simple SELECT a.symbol , a.date as today , a.price as today_price , b.date as weekago , b.price as last_wk_price , c.date as monthago , c.price as last_mth_price , d.date as yearago , d.price as last_yr_price FROM sampletable a LEFT JOIN sampletable b ON a.symbol = b.symbol AND b.date = a.date - INTERVAL 7 DAY LEFT JOIN sampletable c ON a.symbol = c.symbol AND c.date = a.date - INTERVAL 1 MONTH LEFT JOIN sampletable d ON a.symbol = d.symbol AND d.date = a.date - INTERVAL 1 YEAR WHERE a.date = CURDATE(); which gives ++--------+------------+-------------+------------+---------------+------------+----------------+------------+---------------+ | symbol | today | today_price | weekago | last_wk_price | monthago | last_mth_price | yearago | last_yr_price | +--------+------------+-------------+------------+---------------+------------+----------------+------------+---------------+ | ABC | 2022-09-30 | 19.24 | 2022-09-23 | 5.96 | 2022-08-30 | 5.30 | 2021-09-30 | 3.86 | | DEF | 2022-09-30 | 19.52 | 2022-09-23 | 10.15 | 2022-08-30 | 7.61 | 2021-09-30 | 16.59 | | XYZ | 2022-09-30 | 15.00 | | | 2022-08-30 | 15.11 | | | +--------+------------+-------------+------------+---------------+------------+----------------+------------+---------------+ ABC and DEF have daily data whereas XYZ on ly has 1 or 2 per week so, as you see, some results are missing. The tricky part is finding the closest date in the table to the required date. My solution was to use a MySQL user defined function. CREATE FUNCTION `nearestDate`(sym varchar(20), thedate date) RETURNS date READS SQL DATA BEGIN DECLARE v_closest DATE; DECLARE v_mindiff INT; SELECT date INTO v_closest # do we have the exact date? FROM sampletable WHERE symbol = sym AND date = thedate LIMIT 1; IF v_closest IS NOT NULL THEN # if we do, return it RETURN v_closest; END IF; SELECT y.date INTO v_closest # find smallest date difference FROM ( SELECT symbol , MIN(abs(datediff(date, thedate))) as mindiff FROM sampletable WHERE symbol = sym ) x JOIN # and match to find the date ( SELECT date FROM sampletable WHERE symbol = sym ) y ON abs(datediff(y.date, thedate)) = x.mindiff; RETURN v_closest; # return found date END The query using that function becomes SELECT a.symbol , a.date as today , a.price as today_price , b.date as d7 , b.price as last_wk_price , c.date as d30 , c.price as last_mth_price , d.date as d365 , d.price as last_yr_price FROM sampletable a LEFT JOIN sampletable b ON a.symbol = b.symbol AND b.date = nearestDate(a.symbol, curdate()-interval 7 day) LEFT JOIN sampletable c ON a.symbol = c.symbol AND c.date = nearestDate(a.symbol, curdate()-interval 1 month) LEFT JOIN sampletable d ON a.symbol = d.symbol AND d.date = nearestDate(a.symbol, curdate()-interval 1 year) WHERE a.date = curdate(); now giving +--------+------------+-------------+------------+---------------+------------+----------------+------------+---------------+ | symbol | today | today_price | d7 | last_wk_price | d30 | last_mth_price | d365 | last_yr_price | +--------+------------+-------------+------------+---------------+------------+----------------+------------+---------------+ | ABC | 2022-09-30 | 19.24 | 2022-09-23 | 5.96 | 2022-08-30 | 5.30 | 2021-09-30 | 3.86 | | DEF | 2022-09-30 | 19.52 | 2022-09-23 | 10.15 | 2022-08-30 | 7.61 | 2021-09-30 | 16.59 | | XYZ | 2022-09-30 | 15.00 | 2022-09-25 | 2.87 | 2022-08-30 | 15.11 | 2021-09-29 | 4.20 | +--------+------------+-------------+------------+---------------+------------+----------------+------------+---------------+

Famous last words... 😆