0

The oracle table that I'm trying to query has multiple date columns that give timestamps for warehouse activity, both with an item and location associated with them.

I'm trying to find the item that was in a location at a certain date. Activity timestamps of actions in that location (such as adding more product, switching with a new product, counting, etc) have the item listed. I'm looking up the max value of a date given that it's below a certain target, in order to find the item that was last recorded there within the time of the timestamp and my target date. my query for this is as follows:

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');

This is all fine and good, but that second column WHENOCCURRED also has timestamps that I want, and they don't match up. A newer activity with a different item might have occurred in WHENOCCURRED, but not shown up in ANVDATE, in which case I would be getting false information about the item in the location if I query ANVDATE.

here's a brief illustration of what I mean:

   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

As you can see, I'm looking to get Item "MK0C2AM/A", but It has to be returned via a date before 3-Jan-18, so trying to do a CASE comparison between ANVDATE and WHENOCCURRED for the greater one would chuck out the entry that actually has the item I want.

How do I check both ANVDATE and WHENOCCURRED for the latest update before a target time, choose only the closest one, and get the ITEM and LOCATION from it?

Warehouse-Data-Monkey
  • 1
  • 1

1 Answers1

0
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;

should get you going.

Connor McDonald
  • 10,418
  • 1
  • 11
  • 16