sql - i want to select by the Nearest Date

Question

using Oracle I have this Statement to show QtyAvailable of stock in the a specific date

SELECT st.QTYAVAILABLE
FROM RV_STORAGE st
INNER JOIN m_product p
ON ( st.m_product_id =p.m_product_id)
INNER JOIN AD_Org org
ON ( org.ad_org_id          = p.ad_org_id )
WHERE st.DATELASTINVENTORY ?????? '1-AUG-2014'
AND st.M_PRODUCT_ID         = 1003965

but in the table RV_Sorage the qtyAvailable is calculate many time (inventory) so I want to Select the near QtyAvailable to my DATELASTINVENTORY

example

   id    qty    date
1003965   5   30-APR-10
1003965  168  18-DEC-13
1003965   0   31-DEC-08
1003965   0   31-DEC-08
1003965   5   24-JUL-12
1003965   5   30-SEP-13
1003965   0   30-SEP-13
1003965   2   21-MAY-14

result must be 2 from the line with the near date 21-MAY-14

Answer 1

I'm not sure if I understand well your problem. If you want the QTYAvailable for a defined date, here is the query you're looking for:

SELECT S.QTYAVAILABLE
FROM (SELECT S.QTYAVAILABLE
      FROM rv_storage ST
      WHERE ST.m_product_id = 1003965
          AND ST.datelastinventory < '1-AUG-2014'
      ORDER BY ST.datelastinventory DESC) S
WHERE ROWNUM = 1

Hope this will help you, i'm not familiar with Oracle.