sqlsql-date-functionssqldatetime

Is there a way to acess previous specific value in SQL?

I am working in Oracle live SQL with this same table with data for the whole year (in which the intervals can be larger).

This is just an example(part of the whole table):

Example table

I have to add two columns that will show the interval the dates are in(based on min, between and max).

The From column: if the date is labeled as min or both in the Oddo column it should just show that date, if it is labeled as between or max it should show the previous min date.

The To Column: if the date is labeled as max or both in the Oddo column it should just show that date, if it is labeled as between or min it should show the next max date. The end result should look like this:

Final result

Thank you so much for any suggestions.

Solution

  • In Oracle, one method is conditional cumulative aggregation:

    select t.*,
       max(case when odd = 'min' then date end) over (order by date) as min_date,
       min(case when odd = 'max' then date end) over (order by date desc) as max_date
from t;