SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Inventory Balance Calculation FIFO
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

seireina
Starting Member

Ukraine
7 Posts

Posted - 11/26/2012 :  08:12:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/26/2012 :  08:35:14  Show Profile  Reply with Quote
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?
Go to Top of Page

seireina
Starting Member

Ukraine
7 Posts

Posted - 11/26/2012 :  08:51:03  Show Profile  Reply with Quote
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.

Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
431 Posts

Posted - 11/26/2012 :  08:51:45  Show Profile  Reply with Quote

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
Go to Top of Page

seireina
Starting Member

Ukraine
7 Posts

Posted - 11/26/2012 :  11:32:11  Show Profile  Reply with Quote
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?
Go to Top of Page

seireina
Starting Member

Ukraine
7 Posts

Posted - 11/26/2012 :  11:36:06  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/26/2012 :  12:24:48  Show Profile  Reply with Quote
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
.....
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
431 Posts

Posted - 11/27/2012 :  01:31:13  Show Profile  Reply with Quote
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
Go to Top of Page

seireina
Starting Member

Ukraine
7 Posts

Posted - 11/27/2012 :  07:39:53  Show Profile  Reply with Quote
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.
Go to Top of Page

seireina
Starting Member

Ukraine
7 Posts

Posted - 11/27/2012 :  08:09:06  Show Profile  Reply with Quote
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.

Edited by - seireina on 11/27/2012 08:36:50
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
431 Posts

Posted - 11/27/2012 :  08:52:04  Show Profile  Reply with Quote
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

Edited by - stepson on 11/27/2012 08:54:18
Go to Top of Page

seireina
Starting Member

Ukraine
7 Posts

Posted - 12/01/2012 :  18:33:30  Show Profile  Reply with Quote
Working like a charm. Thanks you very much for all your efforts.
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
431 Posts

Posted - 12/03/2012 :  01:01:42  Show Profile  Reply with Quote
w.welcome

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

patrickjao
Starting Member

Indonesia
24 Posts

Posted - 12/12/2012 :  06:26:57  Show Profile  Reply with Quote
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?


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000