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.
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