Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    58

    Unanswered: Query help for view

    Hi,
    We have a table that contains the inventory item info including QTY ON HAND etc. Now whenever the QTY ON HAND is updated in that table due to say a purchase order, or a sales order, we change the QTY ON HAND in that main table IMITEMLOC, but we keep a trace of that transaction in a seperate trace table called IMITEMLOC_TRACE. So this table has the following definition
    Name Null? Type
    ------------------------------- -------- ----
    WHICH_USERID VARCHAR2(15)
    USER_ACTION VARCHAR2(20)
    TIMESTAMP DATE
    ITEM VARCHAR2(24)
    OLD_QTYONHAND NUMBER(11,4)
    NEW_QTYONHAND NUMBER(11,4)
    So when running the following query
    SQL> SELECT TIMESTAMP, new_qtyonhand
    2 from imitemloc_trace
    3 where item='ACTO'
    4 and timestamp>sysdate-10;
    I get:
    TIMESTAMP NEW_QTYONHAND
    --------- -------------
    28-SEP-04 1
    28-SEP-04 123
    28-SEP-04 1
    28-SEP-04 123
    01-OCT-04 1

    Now we have a requirement where we need to create a report that shows the last 10 days and the QTY ON HAND on those days. Currently the view being used to do this is the following:
    SELECT
    ITEM,
    TRUNC(TIMESTAMP,'Ddd') month, ROUND(AVG(NEW_QTYONHAND),0) QTY
    FROM
    IMITEMLOC_TRACE
    where timestamp>sysdate-10
    GROUP BY ITEM,TRUNC(TIMESTAMP,'Ddd')
    ORDER BY ITEM

    Problem is that this only shows data for the days a change was made, so for the last ten days this only shows the following:
    SQL> select * from avg_qty_day where item='ACTO';

    ITEM MONTH QTY
    ------------------------ --------- ---------
    ACTO 28-SEP-04 62
    ACTO 01-OCT-04 1

    What I want to do is to change this view so that it shows data for every single day, so if there is no change on 29thsep, and 30 sep, it should just show 62 which is the qty of 28th sep. Basically for the the interval where there is a null data being returned, I wanna just use the previous data. Can someone please help me out with this?

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Interesting. I added another item to the table to make it even more interesting. So now it will look like this:
    Code:
    SQL> select * from imitemloc_trace;
    
    WHICH_USERID    USER_ACTION          TIMESTAMP ITEM                     OLD_QTYONHAND NEW_QTYONHAND
    --------------- -------------------- --------- ------------------------ ------------- -------------
    JMARTINEZ       INSERT               28-SEP-04 ACTO                                 0             1
    JMARTINEZ       INSERT               28-SEP-04 ACTO                                 0           123
    JMARTINEZ       INSERT               28-SEP-04 ACTO                                 0             1
    JMARTINEZ       INSERT               28-SEP-04 ACTO                                 0           123
    JMARTINEZ       INSERT               01-OCT-04 ACTO                                 0             1
    JMARTINEZ       INSERT               28-SEP-04 ACTI                                 0             1
    JMARTINEZ       INSERT               28-SEP-04 ACTI                                 0           123
    JMARTINEZ       INSERT               28-SEP-04 ACTI                                 0             1
    JMARTINEZ       INSERT               28-SEP-04 ACTI                                 0           123
    JMARTINEZ       INSERT               01-OCT-04 ACTI                                 0             1
    
    10 rows selected.
    First, have to get gap days around the values of lowest and highest date per item, and for each day, take the average.
    So this will do:

    Code:
    SQL> select item,
      2         month,
      3         ( select round(avg(new_qtyonhand),0)
      4             from imitemloc_trace
      5            where item = x.item
      6              and timestamp = x.month ) qty
      7    from (
      8  select item,
      9         mindate-1+row_number() over(partition by item order by rownum) month,
     10         maxdate
     11    from (
     12  select item, min(timestamp) mindate, max(timestamp) maxdate
     13    from imitemloc_trace
     14   group by item, cube(1,2,3,4,5,6,7,8,9)
     15         )
     16         ) x
     17   where x.month <= x.maxdate
     18     and x.month > sysdate - 10
     19  /
    
    ITEM                     MONTH            QTY
    ------------------------ --------- ----------
    ACTI                     28-SEP-04         62
    ACTI                     29-SEP-04
    ACTI                     30-SEP-04
    ACTI                     01-OCT-04          1
    ACTO                     28-SEP-04         62
    ACTO                     29-SEP-04
    ACTO                     30-SEP-04
    ACTO                     01-OCT-04          1
    
    8 rows selected.
    And now, using a trick I learned reading AskTom, I span the values over the null ones with this:

    Code:
    SQL> select item,
      2         month,
      3         substr(max(decode(qty,null,null,to_char(month,'yyyymmddhh24miss')||qty))
      4                over(partition by item order by month),15) qty
      5    from (
      6  select item,
      7         month,
      8         ( select round(avg(new_qtyonhand),0)
      9             from imitemloc_trace
     10            where item = x.item
     11              and timestamp = x.month ) qty
     12    from (
     13  select item,
     14         mindate-1+row_number() over(partition by item order by rownum) month,
     15         maxdate
     16    from (
     17  select item, min(timestamp) mindate, max(timestamp) maxdate
     18    from imitemloc_trace
     19   group by item, cube(1,2,3,4,5,6,7,8)
     20         )
     21         ) x
     22   where x.month <= x.maxdate
     23     and x.month > sysdate - 10
     24         )
     25   order by item, month
     26  /
    
    ITEM                     MONTH            QTY
    ------------------------ --------- ----------
    ACTI                     28-SEP-04         62
    ACTI                     29-SEP-04         62
    ACTI                     30-SEP-04         62
    ACTI                     01-OCT-04          1
    ACTO                     28-SEP-04         62
    ACTO                     29-SEP-04         62
    ACTO                     30-SEP-04         62
    ACTO                     01-OCT-04          1
    
    8 rows selected.
    ..and that should get you going. Adding proper indexes on ITEM and TIMESTAMP will pretty much increase performance as well.
    Last edited by JMartinez; 10-02-04 at 11:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •