Stock Days Cover calc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Stock Days Cover calc

Author

Topic

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-10-18 : 15:41:42

Hi,

I have the Forecast data in 2 possible formats:

Product, Day1,Day2,Day3,...
ProductA,QTY,QTY,QTY

or in the following format:

Product, Date, QTY
ProductA,Day1,qty
ProductA,Day2,qty
ProductA,Day3,qty

I want to write script to take my current Stock on Hand figure and see how much STock Days Cover it is.Example:

I have 100 Units Stock on Hand
day1 forecast= 50 (50 Cumalative)
day2 forecast= 20 (70 Cumalative)
Day3 forecast= 30 (100) Therefore the 100 units is 3 days cover
Day4 forecast= 10

So in effect the script must sum the days forecast day by day till the cumalitive forecast is = to the SOH figure...is this a loop?If so how does it work???I dont want to take the 4 days of forecast and average it out, I want to take it into consideration day by day...

Really hope someone can help!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 05:21:31

seem like what you need is quirky update kind of logic. see example below

http://visakhm.blogspot.com/2010/03/using-quirky-updates-to-develop-well.html

other way is to use cursor or looping logic

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-10-24 : 09:19:06

Hi Vsiakhm,

I tried to do the quirky update but cant seem to get it to do what I want...can you please assist by using my exmaple above to calculate it.

Product, Day1,Day2,Day3,...
ProductA,QTY,QTY,QTY

or in the following format:

Product, Date, QTY
ProductA,Day1,qty
ProductA,Day2,qty
ProductA,Day3,qty

I want to write script to take my current Stock on Hand figure and see how much STock Days Cover it is.Example:

I have 100 Units Stock on Hand
day1 forecast= 50 (50 Cumalative)
day2 forecast= 20 (70 Cumalative)
Day3 forecast= 35 (100) Therefore the 100 units is 3.4 days cover
Day4 forecast= 10

So in effect the script must sum the days forecast day by day till the cumalitive forecast is = to the SOH figure...is this a loop?If so how does it work???I dont want to take the 4 days of forecast and average it out, I want to take it into consideration day by day...and it must for exmplae say 1.56 days of cover

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 09:21:32

ok. i got you. but can you explain how you get 3.4 ie decimal part?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-10-24 : 09:32:25

:) Sorry that was just and exmple to show you that it show show decimal...therefore not just a rowid, like 4 days or 3 days...but if needed 4.x days. Depening on how how far into that days Cumulative FC quantity the SOH goes.Hope this helps?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 09:38:34

thats ok. my question was what was logic you applied to get .4 part of results. from table you can only make out whole number dayids rght?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-10-24 : 09:52:39

This is what I get at the moment:

I do a While loop: But it bring back only the Rowid once the While rule has been satified...but what I want is the it must add every day's FC quantity line by line till it gets to the SOH quantity. And then be able to sy for example.

I have 100 Units Stock on Hand
rowid, Day, qty
1,day1 forecast= 50 (50 Cumalative)
2,day2 forecast= 20 (70 Cumalative)
3,day3 forecast= 10 (80 Cumalative)
4,Day4 forecast= 30 ((Cumalative at day 4) = 110-100 (SOH)= therefore 100 soh cover 3 full days of FC and 10 qty of the 30 qty on the 4th day) Therefore the 100 units doesn't cover 4 days of stock but 3.x days.
Therefore bring back 3 to say it is 3 days cover but even more detail and say it cover...more tahn 3 days

This is what I have done:
drop table test

Create Table Test

(

rowid int

,product nvarchar (50)

,period datetime

,Qty float

)

insert into Test

select 1, 'a', '2011-01-01 00:00:00.000', 2

union

select

2 ,'a' ,'2011-01-02 00:00:00.000', 3

union

select

3, 'a' ,'2011-01-03 00:00:00.000' ,5

union

select

4, 'a' ,'2011-01-04 00:00:00.000' ,7

union

select

5, 'a' ,'2011-01-05 00:00:00.000' ,8

union

select

6, 'a' ,'2011-01-06 00:00:00.000' ,9

union

select

7, 'a' ,'2011-01-07 00:00:00.000' ,20

Declare @iSOH int

SET @iSOH = 40

Declare @iStart int

Set @iStart= (select top 1 Qty from Test order by period asc)

--print @istart

While @iSOH>=@iStart

BEGIN

If @iSOH=@iStart

Begin

Break

end

DELETE FROM Test

WHERE period IN (SELECT top 1 period FROM Test ORDER BY period asc)

Set @iStart= @iStart+(select top 1 Qty from Test order by period asc)

END

if (Select top 1 rowid as DaysCover from Test) is null

Begin

Select 'More SOH than FC'

End

Else

Select top 1 rowid as DaysCover from Test

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-10-24 : 10:21:58

Sorry I meant in the example...
I have 100 Units Stock on Hand
rowid, Day, qty
1,day1 forecast= 50 (50 Cumalative)
2,day2 forecast= 20 (70 Cumalative)
3,day3 forecast= 10 (80 Cumalative)
4,Day4 forecast= 30 ((Cumalative at day 4) = 110-100 (SOH)= therefore 100 soh cover 3 full days of FC and 20 qty of the 30 qty on the 4th day) Therefore the 100 units doesn't cover 4 days of stock but 3.x days.

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-10-25 : 01:02:25

Please let me know if there is any other info you need...

quote:
Originally posted by visakh16

thats ok. my question was what was logic you applied to get .4 part of results. from table you can only make out whole number dayids rght?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources