1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query to get stock till date

Discussion in 'SQL PL/SQL' started by mukulverma2408, Sep 20, 2015.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi,

    I have a table as given below :

    ITEM TILL_DATE STOCK
    -------------------- --------- --------------------
    A 10-FEB-15 20

    B 15-FEB-15 30

    C 17-FEB-15 25

    A 20-FEB-15 40

    D 22-FEB-15 50

    E 24-FEB-15 30

    B 27-FEB-15 30

    I have this requirement to write two different query that will give me stock till date
    example : executing this query for 16th feb will give me output like :

    A 10-FEB-15 20
    B 15-FEB-15 30

    executing the query for 21-feb will give me o/p like :

    ITEM TILL_DATE STOCK
    -------------------- --------- --------------------
    B 15-FEB-15 30

    C 17-FEB-15 25

    A 20-FEB-15 40
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Not clear. Provide table scripts and insert scripts and then explain the requirement clearly.

    On what basis you need those rows only?
     
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    As Jagadekara indicated, you didn't really provide enough data to be positive what logic you are looking for. The following test case returns the data you requested, but that does not necessarily mean it is what you were looking for:

    Code (Text):

    CREATE TABLE tmp_stock (
    item       VARCHAR2(2),
    till_date  DATE,
    stock      NUMBER);

    INSERT INTO tmp_stock VALUES ('A','10-FEB-15',20);
    INSERT INTO tmp_stock VALUES ('B','15-FEB-15',30);
    INSERT INTO tmp_stock VALUES ('C','17-FEB-15',25);
    INSERT INTO tmp_stock VALUES ('A','20-FEB-15',40);
    INSERT INTO tmp_stock VALUES ('D','22-FEB-15',50);
    INSERT INTO tmp_stock VALUES ('E','24-FEB-15',30);
    INSERT INTO tmp_stock VALUES ('B','27-FEB-15',30);


    SELECT item, till_date, stock
    FROM   tmp_stock
    WHERE  till_date < TO_DATE('17-FEB-15', 'DD-MON-RR')
    AND    (till_date, item) IN (SELECT MAX(till_date), item
                                 FROM   tmp_stock
                                 WHERE  till_date < TO_DATE('17-FEB-15', 'DD-MON-RR')
                                 GROUP BY item);

    ITEM TILL_DATE      STOCK
    ---- --------- ----------
    A    10-FEB-15         20
    B    15-FEB-15         30


    SELECT item, till_date, stock
    FROM   tmp_stock
    WHERE  till_date < TO_DATE('21-FEB-15', 'DD-MON-RR')
    AND    (till_date, item) IN (SELECT MAX(till_date), item
                                 FROM   tmp_stock
                                 WHERE  till_date < TO_DATE('21-FEB-15', 'DD-MON-RR')
                                 GROUP BY item);

    ITEM TILL_DATE      STOCK
    ---- --------- ----------
    B    15-FEB-15         30
    C    17-FEB-15         25
    A    20-FEB-15         40
     
     
  4. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Let me try to explain it :

    I have inventory table that holds information about various items in my warehouse.
    Whenever there is change in stock, instead of updating the table i make a new entry in table with updated stock value.
    Now at any point of time i want to know the status of all unique items available with their most recent stock value from inventory table.

    Hope i am clear this time.