| Author |
 Topic  |
|
|
seireina
Starting Member
Ukraine
7 Posts |
 Posted - 11/26/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/26/2012 : 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
Ukraine
7 Posts |
Posted - 11/26/2012 : 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
Yak Posting Veteran
Romania
85 Posts |
Posted - 11/26/2012 : 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
Ukraine
7 Posts |
Posted - 11/26/2012 : 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
Ukraine
7 Posts |
Posted - 11/26/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/26/2012 : 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
Yak Posting Veteran
Romania
85 Posts |
Posted - 11/27/2012 : 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
Ukraine
7 Posts |
Posted - 11/27/2012 : 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
Ukraine
7 Posts |
Posted - 11/27/2012 : 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. |
Edited by - seireina on 11/27/2012 08:36:50 |
|
|
|
stepson
Yak Posting Veteran
Romania
85 Posts |
Posted - 11/27/2012 : 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 |
Edited by - stepson on 11/27/2012 08:54:18 |
|
|
|
seireina
Starting Member
Ukraine
7 Posts |
Posted - 12/01/2012 : 18:33:30
|
| Working like a charm. Thanks you very much for all your efforts. |
|
|
|
stepson
Yak Posting Veteran
Romania
85 Posts |
Posted - 12/03/2012 : 01:01:42
|
w.welcome
Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb |
|
|
|
patrickjao
Starting Member
Indonesia
24 Posts |
Posted - 12/12/2012 : 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?
|
|
|
| |
Topic |
|
Inventory Balance Calculation FIFO
