I am trying to write a mysql query where mysql searches for nearest date available if date added is out of range
my query code is
SELECT B.ticker,B.date S_date, B.close S_close, E.date E_date, E.close E_close, ROUND(( E.close -
B.close)/B.close * 100,2) perc FROM ( SELECT ticker, date, close FROM stocks WHERE date =
"2020-09-15") B inner join (SELECT ticker, date, close FROM stocks WHERE date = "2022-10-13" )
E on B.ticker = E.ticker LIMIT 10 OFFSET 0;
In above code I am trying to find dates to get close values
first date is 2020-09-15 and second date is 2022-10-13
I need to get exact dates to get close values to calculate perc
ROUND(( E.close - B.close)/B.close * 100,2) perc
Now, the issue is when the dates is out of range in database.
e.g.
I have data available from 2021-01-01 to 2021-12-31. When user puts dates similar to current sql query where dates are out of range, the mysql is giving empty query result
So, I need to modify my query so that if dates are out of range, then mysql searches for nearest date available instead of giving empty result.
if dates are out of range then 2021-01-01 should be used as b date and 2021-12-31 as e date
mysql table screenshot
please help me.
thanks