Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
310 views
in Technique[技术] by (71.8m points)

sql - i want to select by the Nearest Date

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...