Calculating Weeks on Hand for an item based on subsequent weeks of forecast

Options

Hi,

 

I have the following dataset:

 

Item

Date

Projected Inventory(PE)

Forecast(FC)

A

Jan 28, 2019

604

50

A

Feb 04, 2019

630

52

A

Feb 11, 2019

500

49

A

Feb 18, 2019

450

46

A

Feb 25, 2019

1176

51

A

Mar 04, 2019

1136

55

A

Mar 11, 2019

1083

53

A

Mar 18, 2019

1030

53

A

Mar 25, 2019

980

50

A

Apr 01, 2019

900

65

A

Apr 08, 2019

853

62

A

Apr 15, 2019

944

59

A

Apr 22, 2019

1303

49

 

The Desired O/ P is:

Item

Date

Weeks On Hand

A

Jan 28, 2019

11

A

Feb 04, 2019

12

A

Feb 11, 2019

9

A

Feb 18, 2019

8

A

Feb 25, 2019

0 (Because there are less than 10 weeks of FC available for Item A)

A

Mar 04, 2019

0

A

...

0

 

The logic is as follows:

 

I need to calculate the number of weeks on hand for each item for that particular week based on forecast for several weeks going forward.

So  for week of Jan 28, 2019 , the Projected Inventory is 604.

 

In order to calculate the Weeks On hand of Item A for week of Jan 28, 2019 the calculation is as follows:

1)FOR WEEK OF JAN 28, 2019:

 

PE = 604

 

Iteration 1:

604 – 50(FC for week 1) = 554

Weeks on Hand(WOH)  = 0 + 1 = 1

 

Iteration 2:

554 – 52(FC for week 2) = 502

WOH = 1 + 1 = 2

 

Iteration 3:

502 – 49 (FC for week 3) = 453

WOH = 2 + 1 = 3

 

And so on…

Finally we get WOH = 11  for Item A for week of Jan 28, 2019.

 

1)FOR WEEK OF FEB 04, 2019:

 

PE  = 630

 

Iteration 1:

630 – 52(FC for week 2) = 578

Weeks on Hand(WOH)  = 0 + 1 = 1

 

Iteration 2:

578 – 49(FC for week 3) = 529

WOH = 1 + 1 = 2

 

Iteration 3:

529 – 46 (FC for week 4) = 483

WOH = 2 + 1 = 3

 

And so on…

Finally we get WOH = 12  for Item A for week of Feb 04, 2019.

 

Similarly I need to calculate WOH for different items for subsequent weeks.

NOTE: there should be at least 10 weeks of Forecast for an item , if not the WOH = 0.

 

How to achieve this result in My SQL?

I am thinking we should use Stored Procedure and Cursors for this calculation. Is Stored Procedure the way to go or is there a simpler solution ?

I am not very familiar with My SQL procedure.

Can someone help me out?

 

Thanks in advance.


Prajwal