Stock Aging part 4 Test cases

Different test cases:

1 supply , no consumption

2. 1 supply , 1 consumption equal

3. 1 supply , 1 consumption, (partial)

4 1 supply, 2,3 consumptions (partial)

5. 1 suoply , 2,3 consumption , no stock

6, 2,3 supplies , 1 consumption , 0 stock

7 2,3 supplies, 2,3 consumption, with stock

8 2,3 supplies, 2,3 items, 2,3 consumptions

S

Stock Aging part 3

Now , we have the years in a interval table, the raw table prepared in a source table.

Next we have to place a record in the right interval.
A record is in the X interval if:

• Aprov date is between date start and end date or
• Consum date is between start date and end date or
• aprov date is less then start date and consum date si greater then end date

So , the final select will look like this:

So.for this sample, the output will look like this:

S

Stock Aging part 2

Now , that we have the raw table, we can go next.
We have to prepare the source so that we have how we desired, so that we have to show , on years the stock aging, starting with year 2017 till 2022.

For this , we create the list of years, with date start been January 1, and end date been December 31.

select
number,
DATEADD(Year,number,’20170101′) as dataStart,
DATEADD(DAY,-1,DATEADD(Year,number + 1 ,’20170101′)) as dataEnd,
DATEADD(Year,number + 1 ,’20170101′) as dataEndNextYear
into #tmpInterval
from master.dbo.spt_values as v
where v.type =’p’
and number <= 5

Number	DataStart	DataEnd	DataEndNextYear
0	2017-01-01	2017-12-31	2018-01-01
1	2018-01-01	2018-12-31	2019-01-01
2	2019-01-01	2019-12-31	2020-01-01
3	2020-01-01	2020-12-31	2021-01-01
4	2021-01-01	2021-12-31	2022-01-01
5	2022-01-01	2022-12-31	2023-01-01

Next , we prepare the raw table for better use, and here is the point, to split the supply by it’s consumptions +

in case of remaining stock available .

The output will look like this:

The final select, that will generate the desired output, will be in the next post.

S

Stock Aging part 1

If we have some supplies and consumptions than what is the stock aging
So we have to raw table with all the tradings.

+Qty is the supply
-Qty is the consumption
IdRowMySelf is the idRow of the supply row, in case of the consumption
it is not null only in case of the consumption
For the moment, the value it’s not taken in account just for the simplicity.

idRow	DATE TRANSACTION	ITEMID	QTY	IDROWMYSELF
|1	2018-02-02	a1	25	NULL
2	2019-02-03	a1	10	1
3	2020-02-03	a1	3	1

CREATE TABLE dbo.rawMaterials
(
idRow int not null primary key clustered,
dateTransaction date not null,
itemID varchar(50) not null,
Qty int not null,
idRowMySelf int null
);

INSERT INTO dbo.rawMaterials(idRow,dateTransaction,itemID,Qty,idRowMySelf)
VALUES(1,’20180202′,’a1′,25,NULL),
(2,’20190203′,’a1′,10,1),
(3,’20200203′,’a1′,3,1);

SELECT *
FROM dbo.rawMaterials;