1

I'm dealing with this particularly complicated (to me at least, if you're a math geek, don't you judge me!) issue.

I've essentially got two things a record of what current stock levels are and a list of all movements of stock in or out of a store. I'm trying to combine the two parts to give me the ability to understand the stock levels of a particular object at any point in the last year.

The first part of SQL combines all stock movements over the last year for all objects in a specific store and the current stock level:

SELECT OBJINCDE, 
       STOREINCDE, 
       TRUNC(STKMVTDTE) AS MOVEMENT_DATE, 
       --This CASE statement tells me if the stock was moved in or out
       CASE WHEN STKMVTINCDE IN (1, 2, 3, 5, 6, 8, 9, 11)  THEN 1 ELSE -1 END AS MOVEMENT

    FROM H_STK

    WHERE TRUNC(STKMVTDTE, 'MM') >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12)  --in the last year
    AND STOREINCDE = 615  --A particular store

UNION ALL

--This statement looks at current levels and combines it with movements as a movement in on the date that the statement was run

SELECT OBJINCDE, 
       STOREINCDE, 
       TRUNC(SYSDATE) AS MOVEMENT_DATE, 
       STKQTY AS MOVEMENT                             

    FROM P_OBJSTORE                                

    WHERE (STKBRKQTY > 0 OR STKMAXQTY > 0)  --This just only picks objects that have a maximum or minimum listed don't judge the stock system either, I can't change that
    AND STOREINCDE = 615

So that returns essentially a list of all stock movements and their date which I then use in this statement:

SELECT TO_CHAR(y.EACH_DAY, 'DD/MM/YYYY') AS EACH_DAY,
       x.OBJINCDE AS OBJINCDE,
       NVL(x.MOVEMENT, 0) AS MOVEMENT,
       SUM(NVL(x.MOVEMENT, 0)) OVER ( ORDER BY y.EACH_DAY DESC) AS STOCK_LEVEL  --Oracle analytics to put together a running total

FROM (SELECT OBJINCDE, MOVEMENT_DATE, SUM(MOVEMENT) AS MOVEMENT             
          FROM W_MIN_MAX_MOVEMENTS  
          WHERE OBJINCDE = 14419  --This is my selection of a particular object
          GROUP BY OBJINCDE, MOVEMENT_DATE
          HAVING SUM(MOVEMENT) <> 0
          ORDER BY MOVEMENT_DATE) x,
     (SELECT TRUNC(SYSDATE) - 365 + LEVEL AS EACH_DAY  --Just brings in each day for the last 365 days
          FROM DUAL
          WHERE ROWNUM <= 365
          CONNECT BY LEVEL = ROWNUM) y

WHERE x.MOVEMENT_DATE (+) = y.EACH_DAY

ORDER BY y.EACH_DAY DESC

So after that I have a few issues that I can't seem to wrap my head around.

First - In the second statement it returns a list of 365 days, the movement of a selected object on that day and it's historical stock level, I can't seem to get the object ID to appear in every row.

Second - I would love to be able to run this so that I got 365 days of movements for every object with it s corresponding stock level for that day. I think this would have to include a better understanding of Oracle Analytics than I currently posses.

Any help would be greatly appreciated.

Improve this question

1 Answer 1

Reset to default
0

You need a list of objects, I'll use a distinct on the stock view but you probably have something better, another parent table probably:

SELECT to_char(cal.each_day, 'dd/mm/yyyy') AS each_day,
       obj.objincde AS objincde,
       nvl(sto.movement, 0) AS movement,
       SUM(nvl(sto.movement, 0)) over(ORDER BY cal.each_day DESC) 
         AS stock_level --oracle analytics to put together a running total
  FROM (SELECT DISTINCT objincde
          FROM w_min_max_movements
         WHERE objincde = 14419 --this is my selection of a particular object
        ) obj
 CROSS JOIN (SELECT trunc(SYSDATE) - 365 + LEVEL 
                    AS each_day --just brings in each day for the last 365 days
               FROM dual
              WHERE rownum <= 365
             CONNECT BY LEVEL = rownum) cal
  LEFT JOIN (SELECT objincde, movement_date, SUM(movement) AS movement
               FROM w_min_max_movements
              GROUP BY objincde, movement_date
             HAVING SUM(movement) <> 0) sto 
         ON sto.movement_date = cal.each_day
        AND sto.objincde = obj.objincde 
 ORDER BY cal.each_day DESC

This will make your object id appear on every lines.

By the way you should try to work with ANSI joins from now on: the old style joins are harder to read, have extra limitations and are only supported for legacy code. All new code should use the ANSI joins.

Now if you want to show all dates for all objects, just remove the WHERE clause and add a PARTITION clause to your analytic function:

SELECT to_char(cal.each_day, 'dd/mm/yyyy') AS each_day,
       obj.objincde AS objincde,
       nvl(sto.movement, 0) AS movement,
       SUM(nvl(sto.movement, 0)) 
         over(PARTITION BY obj.objincde ORDER BY cal.each_day DESC) 
         AS stock_level --oracle analytics to put together a running total
  FROM (SELECT DISTINCT objincde
          FROM w_min_max_movements) obj
 CROSS JOIN (SELECT trunc(SYSDATE) - 365 + LEVEL 
                    AS each_day --just brings in each day for the last 365 days
               FROM dual
              WHERE rownum <= 365
             CONNECT BY LEVEL = rownum) cal
  LEFT JOIN (SELECT objincde, movement_date, SUM(movement) AS movement
               FROM w_min_max_movements
              GROUP BY objincde, movement_date
             HAVING SUM(movement) <> 0) sto 
         ON sto.movement_date = cal.each_day
        AND sto.objincde = obj.objincde 
 ORDER BY cal.each_day DESC
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.