Ближайшая максимальная дата к целевой дате между двумя столбцами, получить связанный элемент с результатом, все ограничены общим местоположением

Question

В таблице оракула, к которой я пытаюсь выполнить запрос, имеется несколько столбцов даты, которые содержат метки времени для активности хранилища, как с элементом, так и с местоположением, связанным с ними.

Я пытаюсь найти предмет, который был в определенном месте. Временные метки действий в этом месте (например, добавление нового продукта, переключение на новый продукт, подсчет и т. Д.) Содержат элемент в списке. Я ищу максимальное значение даты, учитывая, что она ниже определенной цели, чтобы найти элемент, который был последний раз записан там во время отметки времени и моей целевой даты. мой запрос для этого заключается в следующем:

SELECT ITEM,LOCATION,ANVDATE FROM PLATEHISTORY WHERE ANVDATE = (SELECT MAX(ANVDATE) FROM PLATEHISTORY WHERE ANVDATE <= ('3-Jan-18') AND LOCATION in ('4446D01')) AND LOCATION in ('4446D01');

Это все хорошо, но во втором столбце WHENOCCURRED также есть временные метки, которые я хочу, и они не совпадают. Более новое действие с другим элементом могло происходить в WHENOCCURRED, но не отображаться в ANVDATE, и в этом случае я получал бы ложную информацию об элементе в местоположении, если бы я запросил ANVDATE.

вот краткая иллюстрация того, что я имею в виду:

   LOCATION  ITEM        ANVDATE      WHENOCCURRED
    4446D01  MK0C2AM/A  02-JAN-18   06-FEB-18
    4446D01  MP812LL/A  14-DEC-17   31-DEC-17
    4446D01  MP812LL/A  14-DEC-17   18-DEC-17
    4446D01  MP812LL/A  14-DEC-17   26-DEC-17
    4446D01  MK0C2AM/A  02-JAN-18   08-JAN-18

Как вы можете видеть, я ищу элемент "MK0C2AM/A", но он должен быть возвращен через дату до 3 января-18, поэтому попытка сравнения CASE между ANVDATE и WHENOCCURRED для большего из них будет выкинуть запись, которая на самом деле имеет предмет, который я хочу.

Как проверить наличие последних обновлений как ANVDATE, так и WHENOCCURRED перед целевым временем, выбрать только самое близкое и получить из него ПУНКТ и РАСПОЛОЖЕНИЕ?

Answer 1

SQL> create table t ( ANVDATE date , WHENOCCURRED date , location varchar2(10), PLATEHISTORY int);

Table created.

SQL>
SQL> select *
  2  from (
  3    SELECT x.*, least(x.ANVDATE,x.WHENOCCURRED) dt
  4    FROM
  5    ( select t.*,
  6             row_number() over ( order by ANVDATE ) as r1,
  7             row_number() over ( order by WHENOCCURRED ) as r2
  8      from   t
  9      WHERE  LOCATION in ('4446D01')
 10    ) x
 11    where r1 = 1 or r2 = 2
 12    order by dt
 13  )
 14  where rownum = 1;

должен заставить вас идти.