Doing all the use of the LAG function
I wonder if anyone can help me calculate the running amount.
I am converting this from an existing excel solution so I know what I was aiming for.
I am trying to use LAG to get the values from the previous line, but the calculation is not consistent with my purpose. I think I need to use the result from the previous line in the "lag" column, but this is not possible.
Any help was appreciated.
use tempdb;
--Create Temp Table
IF OBJECT_ID('tempdb..#WareHouseData') IS NOT NULL DROP TABLE #WareHouseData
CREATE TABLE #WareHouseData
(
ItemId INT,
DateID INT,
OpenningWareHouseUnits INT,
FcastSales INT,
GoodsIncoming INT,
TargetRunningStock INT
);
--Fill It With example Data
--OpenningWareHouseUnits only exists in the first week
--Fcast sales can be in any week though normally all weeks
--Goods Incoming can be in any weeks
INSERT INTO #WareHouseData
([ItemId],[DateID],[OpenningWareHouseUnits],[FcastSales],[GoodsIncoming],[TargetRunningStock])
VALUES
(987654,201450,200,10,NULL,190),
(987654,201451,NULL,20,NULL,170),
(987654,201452,NULL,30,NULL,140),
(987654,201501,NULL,20,NULL,120),
(987654,201502,NULL,10,NULL,110),
(987654,201503,NULL,50,NULL,60),
(987654,201504,NULL,60,NULL,0),
(987654,201505,NULL,70,100,30),
(987654,201506,NULL,70,80,40),
(987654,201507,NULL,80,100,60),
(987654,201508,NULL,30,NULL,30),
(987654,201509,NULL,20,NULL,10),
(987654,201510,NULL,20,NULL,0),
(123456,201450,300,50,NULL,250),
(123456,201451,NULL,60,NULL,190),
(123456,201452,NULL,70,100,220),
(123456,201501,NULL,80,NULL,140),
(123456,201502,NULL,100,100,140),
(123456,201503,NULL,105,NULL,35),
(123456,201504,NULL,100,100,35),
(123456,201505,NULL,95,NULL,0),
(123456,201506,NULL,30,100,70),
(123456,201507,NULL,20,NULL,50),
(123456,201508,NULL,5,NULL,45),
(123456,201509,NULL,5,NULL,40),
(123456,201510,NULL,5,NULL,35),
(369258,201450,1000,100,NULL,900),
(369258,201451,NULL,100,NULL,800),
(369258,201452,NULL,100,NULL,700),
(369258,201501,NULL,100,NULL,600),
(369258,201502,NULL,100,NULL,500),
(369258,201503,NULL,100,NULL,400),
(369258,201504,NULL,100,NULL,300),
(369258,201505,NULL,100,NULL,200),
(369258,201506,NULL,100,NULL,100),
(369258,201507,NULL,100,500,500),
(369258,201508,NULL,100,NULL,400),
(369258,201509,NULL,100,NULL,300),
(369258,201510,NULL,100,NULL,200);
;
--Match The Target Runing Stock Total
--I need to match the TargetRunningStock Totals
--This can be recreated in excel by pasting the columns
--{ItemId DateID OpenningWareHouseUnits FcastSales GoodsIncoming}
--Into cell A1 with headers, and pasting this formula
-- =IF(C2="",IF((F1-D2+E2)<0,0,(F1-D2+E2)),(C2-D2+E2)) into cell F2
SELECT w.ItemId
, w.DateID
, w.OpenningWareHouseUnits
, w.FcastSales
, w.GoodsIncoming
, w.TargetRunningStock
, CASE WHEN w.OpenningWareHouseUnits IS NOT NULL
THEN (ISNULL(w.OpenningWareHouseUnits,0) - ISNULL(w.FcastSales,0) + ISNULL(w.GoodsIncoming,0))
ELSE CASE WHEN ((((LAG(ISNULL(w.OpenningWareHouseUnits,0),1) OVER (PARTITION BY w.ItemId ORDER BY w.ItemId,w.DateID))-
(LAG(ISNULL(w.FcastSales,0),1) OVER (PARTITION BY w.ItemId ORDER BY w.ItemId,w.DateID)) +
(LAG(ISNULL(w.GoodsIncoming,0),1) OVER (PARTITION BY w.ItemId ORDER BY w.ItemId,w.DateID)))) -
ISNULL(w.FcastSales,0) + ISNULL(w.GoodsIncoming,0)) < 0
THEN 0
ELSE ((((LAG(ISNULL(w.OpenningWareHouseUnits,0),1) OVER (PARTITION BY w.ItemId ORDER BY w.ItemId,w.DateID))-
(LAG(ISNULL(w.FcastSales,0),1) OVER (PARTITION BY w.ItemId ORDER BY w.ItemId,w.DateID)) +
(LAG(ISNULL(w.GoodsIncoming,0),1) OVER (PARTITION BY w.ItemId ORDER BY w.ItemId,w.DateID)))) -
ISNULL(w.FcastSales,0) + ISNULL(w.GoodsIncoming,0))
END
END CalculatedRunningStock
FROM #WareHouseData w
ORDER BY w.ItemId
, w.DateID
source to share
Ignoring most of the calculation logic for simplicity (and time), you almost certainly want sum() over (partition by ... order by...)
.
select ItemId, DateId, TargetRunningStock,
sum(TargetRunningStock) over (partition by itemid order by dateid)
from WarehouseData
order by ItemId, DateId;
ItemId DateId TargetRunningStock Sum - 123456 201450 250 250 123456 201451 190 440 123456 201452 220 660 ... 987654 201507 60 920 987654 201508 30 950 987654 201509 10 960 987654 201510 0 960
Since you are trying to reproduce the results from a spreadsheet, you may need to wrap something like this around some computed columns that use lag (). I haven't looked that deeply at your spreadsheet.
source to share
The basic syntax for running amount is to use order by
in a clause partition
for a window function sum()
:
SELECT w.ItemId, w.DateID, w.OpenningWareHouseUnits, w.FcastSales,
w.GoodsIncoming, w.TargetRunningStock,
SUM(OpenningWareHouseUnits) OVER (PARTITION BY w.ItemId, w.DateId)
FROM #WareHouseData w
ORDER BY w.ItemId, w.DateID ;
I am a bit unclear how to apply this to your formula. Sample data and desired results will be of great help.
source to share