Inventory Balance Calculation FIFO

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 2005 Forums

Transact-SQL (2005)

Inventory Balance Calculation FIFO

Author

Topic

seireina
Starting Member

7 Posts

Posted - 2012-11-26 : 08:12:54

Hi all,

I have one problem like following. I will be really gratefull if somoone will show me a way out of it:D

I have one table like below

Inventory_Code | Trans_Date | Quantity

1000 2012-01-01 100
1000 2012-01-03 -20
1000 2012-01-07 80
1000 2012-01-09 -120
1000 2012-01-13 -20

Desired Result Set According to on the base date of 2012-01-13

Inventory_Code | Trans_Date | Quantity_left | Shelf_waiting Time

1000 2012-01-01 0 0
1000 2012-01-07 20 6

Another Example of Desired result on diffrenet base date 2012-01-06

Inventory_Code | Trans_Date | Quantity_left | Shelf_waiting Time
1000 2012-01-01 80 5

So what i want to get is decrease my inventories according to fifo for a given base date and find their shelf-life. Any ideas?

Thanks again

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-26 : 08:35:14

If I can reorganize your table based on my understanding, it would be like this:


Inventory_Code | Trans_Date      | Qty recd/sold   Qty remaining
1000            2012-01-01           100            100
1000            2012-01-03           -20             80
1000            2012-01-07            80            160
1000            2012-01-09          -120             40
1000            2012-01-13           -20             20

Based on your sample results, my interpretation is incorrect

Can you describe the rule to be used to arrive at Quantity_Left = 20 on 2012-01-07 and Quantity_Left = 80 on 2012-01-01?

seireina
Starting Member

7 Posts

Posted - 2012-11-26 : 08:51:03

Of course, the logic behind the result sets are:
(first example)
Lets assume Our base date is <=2012-01-13
*2012-01-01
I have +100 quantity of inventory 1000 in this date.
Result set:
Inventory_Code | Trans_Date | Quantity_left |
1000 2012-01-01 100

*2012-01-03
I have -20 quantity of inventory 1000 in this date so FIFO decrease inventory from lowest date. So 2012-01-01 quantity becomes 80.
Result set:
Inventory_Code | Trans_Date | Quantity_left |
1000 2012-01-01 80

*2012-01-07
I have +80 quantity of inventory 1000 in this date. So my result set becomes:
Inventory_Code | Trans_Date | Quantity_left |
1000 2012-01-01 80
1000 2012-01-07 80

*2012-01-09
I have -120 quantity of inventory 1000 in this date.
Result set:
Inventory_Code | Trans_Date | Quantity_left |
1000 2012-01-01 0
1000 2012-01-07 40

*2012-01-13
I have -20 quantity of inventory 1000 in this date.
Result set:
Inventory_Code | Trans_Date | Quantity_left |
1000 2012-01-01 0
1000 2012-01-07 20

Note:I didn't write shelf life calculation in this explanation cause it is just an easy mathematical formula.(Days the inventory stayed without being sold)

So what i want to accomplish is to decrease my sales from the first balance that i have from that inventory. If this balance is not enough plus use the other nearest balance of that inventory..(and goes on like this) (First in first out)

I hope i can explain this time myself. (My english is not so much high level really sorry.)
Please ask me anything that's in my knowledge to answer.

Thanks again.

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-11-26 : 08:51:45

declare @d1 as datetime
set @d1 ='20120106'

;with A
as(
select 1000 as Inventory_Code, '2012-01-01' as Trans_Date, 100 as Quantity union all
select 1000,'2012-01-03',-20 union all
select 1000,'2012-01-07',80 union all
select 1000,'2012-01-09',-120 union all
select 1000,'2012-01-13',-20

)

select AA.Inventory_Code ,AA.Trans_Date,AA.Quantity,
case when coalesce(SS.Qp,0)< abs(coalesce(S.Q,0)) then 0 else coalesce(SS.QP,0)+ coalesce(S.Q,0) end as Stock,
case when coalesce(SS.Qp,0)< abs(coalesce(S.Q,0)) then 0 else datediff(d,AA.Trans_Date,@d1) end as Difer
from A as AA
outer apply (select sum(Quantity) as Q from A
where AA.Trans_date< A.trans_date and Quantity < 0 and A.trans_date<=@d1) S
outer apply (select sum(Quantity) as QP from A
where AA.Trans_date>= A.trans_date and Quantity > 0 and A.trans_date<=@d1 ) SS
where Quantity >0
and AA.trans_date<=@d1
order by Trans_Date

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

seireina
Starting Member

7 Posts

Posted - 2012-11-26 : 11:32:11

Hi Stepson,

You solution is working with limited number of data. I really do not understand why but when i am increasing to dataset to thousands the query is giving me some weird values in the stock part.(My data consists of many inventory_codes)

If i will apply your solution to only one inventory_code it is working like a charm.

What can be the reason?

seireina
Starting Member

7 Posts

Posted - 2012-11-26 : 11:36:06

For example i tried with this dataset. Totaly wrong numbers are going out:(

But thanks anyway i learned apply command in SQL today by the help of your query.
Hope you can help me again and sort out the problem. Thanks again.
A051AK013.A03.COK106_200.0.1 2012-01-01 00:00:00.000 1.00000000
A051AK013.A03.COY101_400.0.2 2012-01-01 00:00:00.000 1.00000000
A051AK013.A03.COY106_001.0.1 2012-01-01 00:00:00.000 1.00000000
A051AK013.A03.7502_200.0.2 2012-01-17 00:00:00.000 5.00000000
A051AK013.A03.COK106_001.0.1 2012-01-17 00:00:00.000 10.00000000
A051AK013.A03.COK106_001.0.2 2012-01-17 00:00:00.000 11.00000000
A051AK013.A03.COK106_200.0.1 2012-01-17 00:00:00.000 11.00000000
A051AK013.A03.COK106_200.0.2 2012-01-17 00:00:00.000 9.00000000
A051AK013.A03.COY101_001.0.2 2012-01-17 00:00:00.000 5.00000000
A051AK013.A03.COY101_100.0.2 2012-01-17 00:00:00.000 15.00000000
A051AK013.A03.COY101_200.0.2 2012-01-17 00:00:00.000 4.00000000
A051AK013.A03.COY101_400.0.2 2012-01-17 00:00:00.000 1.00000000
A051AK013.A03.COY106_001.0.1 2012-01-17 00:00:00.000 3.00000000
A051AK013.A03.COY106_001.0.2 2012-01-17 00:00:00.000 17.00000000
A051AK013.A03.COY101_001.0.2 2012-02-03 00:00:00.000 2.00000000
A051AK013.A03.COY101_100.0.1 2012-02-03 00:00:00.000 2.00000000
A051AK013.A03.COY101_100.0.2 2012-02-03 00:00:00.000 5.00000000
A051AK013.A03.COY106_001.0.2 2012-02-03 00:00:00.000 3.00000000
A051AK013.A03.MEK001_370.0.0 2012-02-03 00:00:00.000 2.00000000
A051AK013.A03.COY101_100.0.1 2012-03-21 00:00:00.000 1.00000000
A051AK013.A03.IPY210_001.0.1 2012-09-18 00:00:00.000 15.00000000
A051AK013.A03.IPY210_001.0.2 2012-09-18 00:00:00.000 15.00000000
A051AK013.A03.COY101_400.0.2 2012-01-03 00:00:00.000 -1.00000000
A051AK013.A03.COY106_001.0.1 2012-01-03 00:00:00.000 -1.00000000
A051AK013.A03.COK106_200.0.1 2012-01-05 00:00:00.000 -1.00000000
A051AK013.A03.7502_200.0.2 2012-01-19 00:00:00.000 -2.00000000
A051AK013.A03.COY106_001.0.1 2012-01-23 00:00:00.000 -1.00000000
A051AK013.A03.COK106_001.0.2 2012-02-04 00:00:00.000 -1.00000000
A051AK013.A03.COY101_001.0.2 2012-02-04 00:00:00.000 -1.00000000
A051AK013.A03.COK106_200.0.1 2012-02-11 00:00:00.000 -1.00000000
A051AK013.A03.COY101_200.0.2 2012-02-19 00:00:00.000 -1.00000000
A051AK013.A03.COY101_001.0.2 2012-02-21 00:00:00.000 -4.00000000
A051AK013.A03.MEK001_370.0.0 2012-02-21 00:00:00.000 -1.00000000
A051AK013.A03.COK106_001.0.1 2012-02-22 00:00:00.000 -1.00000000
A051AK013.A03.COY101_001.0.2 2012-02-22 00:00:00.000 -1.00000000
A051AK013.A03.COY101_001.0.2 2012-02-23 00:00:00.000 -1.00000000
A051AK013.A03.COY101_400.0.2 2012-02-29 00:00:00.000 -1.00000000
A051AK013.A03.COY101_200.0.2 2012-03-12 00:00:00.000 -1.00000000
A051AK013.A03.COY106_001.0.1 2012-03-18 00:00:00.000 -1.00000000
A051AK013.A03.COY101_100.0.1 2012-03-21 00:00:00.000 -2.00000000
A051AK013.A03.COY101_100.0.2 2012-03-22 00:00:00.000 -2.00000000
A051AK013.A03.COY101_200.0.2 2012-03-22 00:00:00.000 -2.00000000
A051AK013.A03.COY106_001.0.2 2012-03-25 00:00:00.000 -1.00000000
A051AK013.A03.MEK001_370.0.0 2012-03-25 00:00:00.000 -1.00000000
A051AK013.A03.COY101_100.0.2 2012-04-06 00:00:00.000 -1.00000000
A051AK013.A03.COK106_200.0.1 2012-04-14 00:00:00.000 -1.00000000
A051AK013.A03.COY101_100.0.2 2012-04-19 00:00:00.000 -1.00000000
A051AK013.A03.COY101_100.0.1 2012-04-22 00:00:00.000 -1.00000000
A051AK013.A03.COY101_100.0.2 2012-06-02 00:00:00.000 -1.00000000
A051AK013.A03.7502_200.0.2 2012-06-20 00:00:00.000 -3.00000000
A051AK013.A03.COY101_100.0.2 2012-07-07 00:00:00.000 -1.00000000
A051AK013.A03.COY106_001.0.2 2012-07-12 00:00:00.000 -1.00000000
A051AK013.A03.COK106_001.0.1 2012-08-25 00:00:00.000 -1.00000000
A051AK013.A03.COK106_200.0.1 2012-08-25 00:00:00.000 -1.00000000
A051AK013.A03.COK106_001.0.2 2012-09-02 00:00:00.000 -1.00000000
A051AK013.A03.COK106_200.0.2 2012-09-02 00:00:00.000 -1.00000000
A051AK013.A03.COY101_100.0.2 2012-09-02 00:00:00.000 -2.00000000
A051AK013.A03.COK106_001.0.1 2012-09-04 00:00:00.000 -3.00000000
A051AK013.A03.COY101_100.0.2 2012-09-08 00:00:00.000 -1.00000000
A051AK013.A03.COK106_001.0.2 2012-09-09 00:00:00.000 -1.00000000
A051AK013.A03.COY106_001.0.1 2012-09-12 00:00:00.000 -1.00000000
A051AK013.A03.COK106_001.0.2 2012-09-16 00:00:00.000 -1.00000000
A051AK013.A03.COK106_200.0.1 2012-09-16 00:00:00.000 -1.00000000
A051AK013.A03.IPY210_001.0.1 2012-09-21 00:00:00.000 -1.00000000
A051AK013.A03.IPY210_001.0.2 2012-09-21 00:00:00.000 -1.00000000
A051AK013.A03.COK106_200.0.2 2012-09-22 00:00:00.000 -1.00000000
A051AK013.A03.COY101_100.0.2 2012-09-22 00:00:00.000 -1.00000000
A051AK013.A03.IPY210_001.0.2 2012-09-26 00:00:00.000 -2.00000000
A051AK013.A03.COK106_001.0.1 2012-09-27 00:00:00.000 -1.00000000
A051AK013.A03.IPY210_001.0.1 2012-09-27 00:00:00.000 -1.00000000
A051AK013.A03.COK106_001.0.1 2012-09-28 00:00:00.000 -1.00000000
A051AK013.A03.COK106_001.0.1 2012-10-02 00:00:00.000 -1.00000000
A051AK013.A03.COK106_200.0.1 2012-10-02 00:00:00.000 -1.00000000
A051AK013.A03.IPY210_001.0.1 2012-10-02 00:00:00.000 -3.00000000
A051AK013.A03.IPY210_001.0.2 2012-10-05 00:00:00.000 -1.00000000
A051AK013.A03.COK106_001.0.1 2012-10-07 00:00:00.000 -1.00000000
A051AK013.A03.COK106_200.0.1 2012-10-07 00:00:00.000 -1.00000000
A051AK013.A03.IPY210_001.0.2 2012-10-11 00:00:00.000 -4.00000000
A051AK013.A03.COK106_001.0.2 2012-10-13 00:00:00.000 -1.00000000
A051AK013.A03.COK106_200.0.1 2012-10-13 00:00:00.000 -3.00000000
A051AK013.A03.IPY210_001.0.1 2012-10-22 00:00:00.000 -1.00000000
A051AK013.A03.COK106_001.0.1 2012-10-26 00:00:00.000 -1.00000000
A051AK013.A03.COK106_200.0.1 2012-10-26 00:00:00.000 -1.00000000
A051AK013.A03.IPY210_001.0.1 2012-10-27 00:00:00.000 -2.00000000
A051AK013.A03.IPY210_001.0.1 2012-11-10 00:00:00.000 -1.00000000
A051AK013.A03.COK106_200.0.1 2012-11-11 00:00:00.000 -1.00000000
A051AK013.A03.IPY210_001.0.2 2012-11-15 00:00:00.000 -1.00000000
A051AK013.A03.IPY210_001.0.1 2012-11-18 00:00:00.000 -1.00000000

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-26 : 12:24:48

In stepson's code, in the two apply queries, add also inventory code into the where clause as shown in red below

....
FROM   A    AS AA
OUTER APPLY (
    SELECT SUM(Quantity) AS Q
    FROM   A
    WHERE  AA.Trans_date < A.trans_date
           AND Quantity < 0
           AND A.trans_date <= @d1
           AND a.Inventory_Code = AA.Inventory_Code
) S
OUTER APPLY (
    SELECT SUM(Quantity) AS QP
    FROM   A
    WHERE  AA.Trans_date >= A.trans_date
           AND Quantity > 0
           AND A.trans_date <= @d1
           AND a.Inventory_Code = AA.Inventory_Code
) SS
.....

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-11-27 : 01:31:13

Hi,
Yes, as Sunitabeck said, filter also after inventory code

Tell us, how it works

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

seireina
Starting Member

7 Posts

Posted - 2012-11-27 : 07:39:53

Thanks for the quick responses but unfortunately there still seems some problem in the query. For some records it is not working as it should be working. Now i will try to describe in an example, let me try to undertsand and check if i am doing something wrong.

Thanks again.

seireina
Starting Member

7 Posts

Posted - 2012-11-27 : 08:09:06

Ok found the problem. I will try to describe it with an example:

Lets suppose this query(Added filters to Inventory_code as stated)

declare @d1 as datetime
set @d1 ='20121212'
;with A
as(
select'ABC' Inventory_Code,'2012-01-01' Trans_Date, 1 Quantity union all
select'ABC' ,'2012-01-17', 11 union all
select 'ABC','2012-01-05', -1 union all
select 'ABC','2012-02-11', -1 union all
select 'ABC','2012-04-14', -1 union all
select 'ABC','2012-08-25', -1 union all
select 'ABC','2012-09-16', -1 union all
select 'ABC','2012-10-02', -1 union all
select 'ABC','2012-10-07', -1 union all
select 'ABC','2012-10-13', -3 union all
select 'ABC','2012-10-26', -1 union all
select 'ABC','2012-11-11', -1
)
select AA.Inventory_Code ,AA.Trans_Date,AA.Quantity,
case when coalesce(SS.Qp,0)< abs(coalesce(S.Q,0)) then 0 else coalesce(SS.QP,0)+ coalesce(S.Q,0) end as Stock,
case when coalesce(SS.Qp,0)< abs(coalesce(S.Q,0)) then 0 else datediff(d,AA.Trans_Date,@d1) end as Difer
from A as AA
outer apply (select sum(Quantity) as Q from A
where AA.Trans_date< A.trans_date and Quantity < 0 and A.trans_date<=@d1
AND a.Inventory_Code = AA.Inventory_Code) S
outer apply (select sum(Quantity) as QP from A
where AA.Trans_date>= A.trans_date and Quantity > 0 and A.trans_date<=@d1
AND a.Inventory_Code = AA.Inventory_Code) SS
where Quantity >0
and AA.trans_date<=@d1
order by AA.Inventory_Code

Resulting Set:
ABC 2012-01-01 1 0 0
ABC 2012-01-17 11 1 330

But this is wrong hence i have total 12 INs and 12 OUTs from this stock as in the initial dataset.
The correct Resulting Set should be:
ABC 2012-01-01 1 0 0
ABC 2012-01-17 11 0 0

Any ideas Stepson?

Thanks again.

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-11-27 : 08:52:04

Try to comment from first outer apply this condition /*AA.Trans_date< A.trans_date and*/

outer apply (select sum(Quantity) as Q from A
where /*AA.Trans_date< A.trans_date and*/ Quantity < 0 and A.trans_date<=@d1
AND a.Inventory_Code = AA.Inventory_Code) S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

seireina
Starting Member

7 Posts

Posted - 2012-12-01 : 18:33:30

Working like a charm. Thanks you very much for all your efforts.

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-12-03 : 01:01:42

w.welcome

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

patrickjao
Starting Member

24 Posts

Posted - 2012-12-12 : 06:26:57

If my table as follow :
Inventory_Code | Trans_Date | Qty recd | Qty sold
1000 2012-01-01 100 0
1000 2012-01-03 0 20
1000 2012-01-07 80 0
1000 2012-01-09 0 -120
1000 2012-01-13 0 -20

How to Create a Qty Remaining Field and the table become
Inventory_Code |Trans_Date | Qty recd | Qty sold | Qty Remaining
1000 2012-01-01 100 0 100
1000 2012-01-03 0 20 80
1000 2012-01-07 80 0 160
1000 2012-01-09 0 -120 40
1000 2012-01-13 0 -20 20

quote:
Originally posted by sunitabeck

If I can reorganize your table based on my understanding, it would be like this:

Inventory_Code | Trans_Date | Qty recd/sold Qty remaining
1000 2012-01-01 100 100
1000 2012-01-03 -20 80
1000 2012-01-07 80 160
1000 2012-01-09 -120 40
1000 2012-01-13 -20 20

Based on your sample results, my interpretation is incorrect

Can you describe the rule to be used to arrive at Quantity_Left = 20 on 2012-01-07 and Quantity_Left = 80 on 2012-01-01?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources