January 12, 2015 at 4:13 am
I wonder if someone could help me calculate a running total.
I am converting this from an existing excel solution so i know what i am aiming for.
I am trying to use LAG to get the values from the previous row butt he calculation in nat matching my target. I think i need to use the result from the previous row in the lag column but that doesnt look possible.
Any help apreciated.
use tempdb;
--Create Temp Table
IF OBJECT_ID('tempdb..#WareHouseData') IS NOT NULL DROP TABLE #WareHouseData
CREATE TABLE #WareHouseData
(
ItemIdINT,
DateIDINT,
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
--{ItemIdDateIDOpenningWareHouseUnitsFcastSalesGoodsIncoming}
--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
January 12, 2015 at 4:49 am
Can you try this version and see if this works for you?
SELECT w.ItemId
,w.DateID
,w.OpenningWareHouseUnits
,w.FcastSales
,w.GoodsIncoming
,w.TargetRunningStock
, SUM(TargetRunningStock) over (partition by ItemId order by DateID ROWS UNBOUNDED PRECEDING) as RunningTotal
FROM #WareHouseData w
ORDER BY w.ItemId
,w.DateID
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 12, 2015 at 5:30 am
Sorry i should have been more clear
This problem is coming from an excel based solution that is now being converted to SQLServer, And the TargetRunningStock was calculated in excel and imported into the temp table here just to help confirm i am getting the right results in the CalculatedRunningStock.
January 12, 2015 at 6:11 am
Your statement of the problem, even with a second posting, isn't making sense to me, especially given the data you have provided. I/we need one more thing: expected output for the given input.
I also HIGHLY recommend this post from Aaron Bertrand: http://sqlperformance.com/2012/07/t-sql-queries/running-totals
Fabiano Amorim has a 3-part series on Windowing Functions on Simple-Talk.com that is a great help too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 12, 2015 at 6:41 am
Quick partial solution, missing the handling of negative stock count though.
😎
SELECT w.ItemId
,w.DateID
,w.OpenningWareHouseUnits
,w.FcastSales
,w.GoodsIncoming
,w.TargetRunningStock
, SUM((ISNULL(w.OpenningWareHouseUnits,0) + ISNULL(w.GoodsIncoming,0)) - w.FcastSales) OVER
(
PARTITION BY w.ItemId
ORDER BY w.DateID
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS ITEM_RT_STOCK
FROM #WareHouseData w
ORDER BY w.ItemId
,w.DateID;
Results
ItemId DateID OpenningWareHouseUnits FcastSales GoodsIncoming TargetRunningStock ITEM_RT_STOCK
----------- ----------- ---------------------- ----------- ------------- ------------------ -------------
123456 201450 300 50 NULL 250 250
123456 201451 NULL 60 NULL 190 190
123456 201452 NULL 70 100 220 220
123456 201501 NULL 80 NULL 140 140
123456 201502 NULL 100 100 140 140
123456 201503 NULL 105 NULL 35 35
123456 201504 NULL 100 100 35 35
123456 201505 NULL 95 NULL 0 -60
123456 201506 NULL 30 100 70 10
123456 201507 NULL 20 NULL 50 -10
123456 201508 NULL 5 NULL 45 -15
123456 201509 NULL 5 NULL 40 -20
123456 201510 NULL 5 NULL 35 -25
369258 201450 1000 100 NULL 900 900
369258 201451 NULL 100 NULL 800 800
369258 201452 NULL 100 NULL 700 700
369258 201501 NULL 100 NULL 600 600
369258 201502 NULL 100 NULL 500 500
369258 201503 NULL 100 NULL 400 400
369258 201504 NULL 100 NULL 300 300
369258 201505 NULL 100 NULL 200 200
369258 201506 NULL 100 NULL 100 100
369258 201507 NULL 100 500 500 500
369258 201508 NULL 100 NULL 400 400
369258 201509 NULL 100 NULL 300 300
369258 201510 NULL 100 NULL 200 200
987654 201450 200 10 NULL 190 190
987654 201451 NULL 20 NULL 170 170
987654 201452 NULL 30 NULL 140 140
987654 201501 NULL 20 NULL 120 120
987654 201502 NULL 10 NULL 110 110
987654 201503 NULL 50 NULL 60 60
987654 201504 NULL 60 NULL 0 0
987654 201505 NULL 70 100 30 30
987654 201506 NULL 70 80 40 40
987654 201507 NULL 80 100 60 60
987654 201508 NULL 30 NULL 30 30
987654 201509 NULL 20 NULL 10 10
987654 201510 NULL 20 NULL 0 -10
January 12, 2015 at 6:50 am
I will take a look at the Running total link you have sent
but just to make things clear the TargetRunningStock is the expected output i am trying to get in the CalculatedRunningStock column.
The data is a small sample of what typically is about 1000 items over a 52 weeks period.
The excel solution this is coming from is going to be replaced by a TSQl solution and the running Stock needs to be calculated as it was in the excel solution.
I can get the solution using cursors
--CUROSR METHOD
DECLARE @rt INT, @ItemId INT, @DateID INT, @OpenningWareHouseUnits INT, @FcastSalesINT, @GoodsIncoming INT, @CalculatedRunningStock INT;
SET @rt = 0;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT w.ItemId
,w.DateID
,ISNULL(w.OpenningWareHouseUnits,0) as OpenningWareHouseUnits
,ISNULL(w.FcastSales,0) as FcastSales
,ISNULL(w.GoodsIncoming,0) as GoodsIncoming
,w.CalculatedRunningStock
FROM #WareHouseData w
ORDER BY w.ItemId
,w.DateID;
OPEN c;
FETCH c INTO @ItemId, @DateID, @OpenningWareHouseUnits, @FcastSales, @GoodsIncoming, @CalculatedRunningStock;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rt = CASE WHEN CASE WHEN @OpenningWareHouseUnits > 0
THEN (@OpenningWareHouseUnits - @FcastSales + @GoodsIncoming)
ELSE (@rt - @FcastSales + @GoodsIncoming)
END < 0
THEN 0
ELSE CASE WHEN @OpenningWareHouseUnits > 0
THEN (@OpenningWareHouseUnits - @FcastSales + @GoodsIncoming)
ELSE (@rt - @FcastSales + @GoodsIncoming)
END
END
UPDATE #WareHouseData
SET CalculatedRunningStock = @rt
WHERE ItemId = @ItemId AND DateiD = @DateID
FETCH c INTO @ItemId, @DateID, @OpenningWareHouseUnits, @FcastSales, @GoodsIncoming, @CalculatedRunningStock;
END
CLOSE c; DEALLOCATE c;
SELECTw.ItemId
,w.DateID
,w.OpenningWareHouseUnits
,w.FcastSales
,w.GoodsIncoming
,w.TargetRunningStock
,w.CalculatedRunningStock
FROM #WareHouseData w
ORDER BY w.ItemId
,w.DateID;
but this is not ideal (due to scaling) and the LAG functions look promising but they fail after the first couple of rows. It looks like i would need to reference the previous row of the the CalculatedRunningStock but i dont believe its possible to use LAG to self reference.
January 12, 2015 at 7:02 am
ThanKS Eirikur Eiriksson thats close, ill see if i can get your version to match the expected output even on the negatives.
January 12, 2015 at 1:11 pm
January 13, 2015 at 10:44 am
What is the reporting or presentation tool that is replacing Excel? Something like Crystal or SSRS?
Don Simpson
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply