Recursive calculation // calculation column referring to itself
-
I have following data table with quantities and minimum stocks. I would like to check if the cumulative quantity goes below the minimum stock. If so the column "ProductionProposal" should calculate the quantity that needs to be produced in order to fill up the stock (with consideration of the defined minimum lot size).
My problem is that this production proposal should take into account previous production proposals, as if they were realized. My idea was to solve this with a recursive sum, but I'm quite new to SQL and struggling with this.
Source table:
What it should look like after:
As you can see eg in the second row, theoretically the cumulative quantity is 3000 and I would need to produce 1620 (2x810) to cover the minimum stock of 4050. But since the column "ProuctionProposal" also takes into account the previous production proposal of 810, the correct proposal in line 2 is 810. The column "QuantityCumulativeInclProductionProposal" is purely informative and shows the new stock (and should therefore always be above minimum stock).
In Excel it's very easy to solve this, I attached an example.
Also I found another post with a similar problem, but I could not apply the solution to my code: https://www.sqlservercentral.com/forums/topic/alternative-to-lagsum-can-a-column-reference-the-sum-of-the-values-above-it
This is my current query but of course it's not solving my problem.
Thanks in advance to anyone who takes time to help 🙂
With cte
AS
(
SELECT
Date
,Item
,Quantity
,MinimumStock
,LotSize
,QuantityCumulative
,(CASE
WHEN (QuantityCumulative-MinimumStock)<0 THEN
(MinimumStock-QuantityCumulative)/LotSize)*Lotsize
ELSE null
END) as ProductionProposal
FROM Table1
UNION ALL
SELECT
Date
,Item
,Quantity
,MinimumStock
,LotSize
,QuantityCumulative
,(CASE
WHEN ((QuantityCumulative+ProductionProposal)-MinimumStock)<0 THEN
(MinimumStock-(QuantityCumulative+ProductionProposal))/LotSize)*LotSize
ELSE null
END) as ProductionProposal
FROM cte
)- This topic was modified 1 year, 8 months ago by romeo.
-
Is this the equation? The change in monthly quantity is labelled as delta_q.
quantity_on_hand + delta_q + production_proposal - lot_size - minimum_stock = 0
In month 1:
quantity_on_hand=4,000, delta_q=0, lot_size=810, and minimum_stock=4050
4,000+0+production_proposal-810-4050=0
production_proposal=860
In month 2:
quantity_on_hand=4,050, delta_q=-1000, lot_size=810, and minimum_stock=4050
production_proposal=1810
At the end of each month the quantity is to equal the minimum quantity on hand, no?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Hi Steve,
First of all thank you for taking your time.
The lot size is the minimum production quantity and does not need to be substracted.
The production proposal needs to be dividable by the lot size, eg 810, 1620, 2430 etc...
Therefore the formula would be:
quantity_on_hand + delta_q + production_proposal - minimum_stock > 0
In month 1:
quantity_on_hand=4,000, delta_q=0, and minimum_stock=4050
4,000+0+production_proposal-4050>0
production_proposal=810 (instead of 50, because we need to produce at least 810)
In month 2:
quantity_on_hand=4,810, delta_q=-1000 and minimum_stock=4050
production_proposal=810 (instead of 240, because we need to produce at least 810)
The dates are actually ddmmyy, so it's days and not months, but yes: at the end of each day the quantity should be above the minimum quantity.
-
Thanks for the further explanation. This query takes a number of passes at the data due to multiple uses of windowing functions. It assumes 'Item' to be a PARTITION BY column
drop table if exists #test;
go
create table #test(
dt date not null,
item varchar(100) not null,
q int not null);
insert #test(dt, item, q) values
('2020-08-01', 'Item1', 0),
('2020-08-02', 'Item1', -1000),
('2020-08-03', 'Item1', -1500),
('2020-08-04', 'Item1', 810),
('2020-08-05', 'Item1', -1600),
('2020-08-06', 'Item1', -200),
('2020-08-07', 'Item1', -100);
declare
@quantity_on_hand int=4000,
@minimum_stock int=4050,
@lot_size int=810;
with
cum_cte(dt, item, q, cum_q) as (
select *, @quantity_on_hand+sum(q) over (partition by item order by dt)
from #test),
ceiling_cte(dt, item, q, cum_q, ceiling_q, lag_ceiling_q) as (
select c.*, v.ceiling_q,
lag(v.ceiling_q, 1, 0) over (partition by item order by dt)
from cum_cte c
cross apply (values (ceiling((@minimum_stock-cum_q)/(1.0*@lot_size))*@lot_size)) v(ceiling_q)),
calcs_cte(dt, item, q, cum_q, ceiling_q, lag_ceiling_q, calc_proposal) as (
select *, iif(ceiling_q>=lag_ceiling_q, ceiling_q, lag_ceiling_q)
from ceiling_cte)
select *, calc_proposal-lag(calc_proposal, 1, 0) over (partition by item order by dt) production_proposal
from calcs_cte;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Steve... thank you so so much! It works like a charm 🙂
I'm still trying to fully understand your solution, but it might only take 1 to 3 years or so 😉
One question: is this a rather easy/standard problem to solve or did it take you some time to solve it? Just wondering 🙂
-
Happy it helps! Thanks for the feedback 😊
It was a medium challenge. Your follow up post cleared up the lot size issue. Maybe an hour or so added together. Estimating these things is slippery. At first read it wasn’t clear so I switched to something else. In the past I wouldn’t touch an xls file from the internet. If uploaded to google tho it auto converts to a google sheet.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Alright, thanks for the details and your advice 🙂
Have a nice weekend Steve 🙂
-
Regarding the time it takes different member have different approaches. For me it's usually asap without too much nth degree refactoring. Whatever gets people unstuck. I'm a developer too so I know the frustration. Also, it seems the majority of time is spent writing the forum posts and not the code. Ha explaining code can be tricky and take up hours. The F5 button is an efficient explainer imo 🙂
In this case the code could be refactored. The 3rd CTE is not strictly necessary. The warning sign is the FROM clause lists a single CTE and there is no windowing function in either the SELECT list or ORDER BY clause which would necessitate a full table scan. Also, the version of SQL I'm using offers the (newly available) GREATEST function which could be used instead of "iif(ceiling_q>=lag_ceiling_q, ceiling_q, lag_ceiling_q)". Here's an equivalent version of the query. The column previously labelled as 'calc_proposal' is now more accurately named 'cum_proposal'
with
cum_cte(dt, item, q, cum_q) as (
select *, @quantity_on_hand+sum(q) over (partition by item order by dt)
from #test),
ceiling_cte(dt, item, q, cum_q, ceiling_q, lag_ceiling_q, cum_proposal) as (
select c.*, v.ceiling_q,
lag(v.ceiling_q, 1, 0) over (partition by item order by dt),
greatest(v.ceiling_q, lag(v.ceiling_q, 1, 0) over (partition by item order by dt))
from cum_cte c
cross apply (values (ceiling((@minimum_stock-cum_q)/(1.0*@lot_size))*@lot_size)) v(ceiling_q))
select *, cum_proposal-lag(cum_proposal, 1, 0) over (partition by item order by dt) production_proposal
from ceiling_cte;Also, it seems like this might be a useful solution to the constrained replenishment issues other people might have. Would you mind if I submit this example and code to SSC as a SQL Script? What could a generalized way be to describe it?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Thanks for the further details and explanation.
Of course, I don't mind at all.
I can't come up with a good general description, I think it can be applied to several areas like stockkeeping or production planning.
Something like "automatic replenishment with constrained conditions"...
Filling up/replenish stocks automatically to the minimum quantity with consideration of previous results and specific lot sizes...
Sorry, I'm not good with descriptions 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply