I want to grab the first returned record and previous record based on a user input date.
CustNo Food Date 1 Red-Apple 7/5/22 1 Red-Apple 7/5/22 1 Red-Apple 7/11/22 1 Red-Cherry 5/20/22 1 Blue-Muffin 4/1/22 1 Blue-Berry 3/16/22 1 Orange-Persimmon 2/8/22 1 Red-Apple 1/23/22 1 Blue-Berry 12/4/21 1 Yellow-Banana 11/27/21
Example, I put in 7/5/22, and I want to grab that date’s food value and somehow include the record previous to that, so output would be:
1 Red-Apple 7/5/22 1 Red-Apple 7/5/22
If I put in 3/16/22, then I want my output to be:
1 Blue-Berry 3/16/22 1 Orange-Persimmon 2/8/22
My totally wrong code:
select CustNo, Food, prevDate from ( select CustNo, Food, Date lag(Date) over (partition by CustNo order by Date) as prevDate, max(Date) over (partition by CustNo) as maxDate from tableZ ) where maxDate = Date and CustNo = 1 and Date = &user_input_date;
Advertisement
Answer
Simply select the rows where the date is equal to or less than the decided date, read descending, fetch 2 rows only.
select CustNo, Food, Date from tableZ where Date <= &user_input_date order by Date desc fetch first 2 rows only
Note: Date
is an Oracle reserved word (https://en.wikipedia.org/wiki/SQL_reserved_words), so it needs to be delimited as "Date"
.