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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ
 All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Inventory Balance Calculation FIFO Reply to Topic  Printer Friendly
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:DI have one table like belowInventory_Code | Trans_Date | Quantity1000 2012-01-01 1001000 2012-01-03 -201000 2012-01-07 801000 2012-01-09 -1201000 2012-01-13 -20Desired Result Set According to on the base date of 2012-01-13Inventory_Code | Trans_Date | Quantity_left | Shelf_waiting Time1000 2012-01-01 0 01000 2012-01-07 20 6Another Example of Desired result on diffrenet base date 2012-01-06Inventory_Code | Trans_Date | Quantity_left | Shelf_waiting Time1000 2012-01-01 80 5So 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 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 incorrectCan 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-01I have +100 quantity of inventory 1000 in this date.Result set:Inventory_Code | Trans_Date | Quantity_left | 1000 2012-01-01 100 *2012-01-03I 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-07I 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-09I 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-13I 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 20Note: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

Romania
545 Posts

 Posted - 11/26/2012 :  08:51:45 declare @d1 as datetimeset @d1 ='20120106';with Aas(select 1000 as Inventory_Code, '2012-01-01' as Trans_Date, 100 as Quantity union allselect 1000,'2012-01-03',-20 union allselect 1000,'2012-01-07',80 union allselect 1000,'2012-01-09',-120 union allselect 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 Diferfrom 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 ) SSwhere Quantity >0 and AA.trans_date<=@d1 order by Trans_DateCe-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb

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.00000000A051AK013.A03.COY101_400.0.2 2012-01-01 00:00:00.000 1.00000000A051AK013.A03.COY106_001.0.1 2012-01-01 00:00:00.000 1.00000000A051AK013.A03.7502_200.0.2 2012-01-17 00:00:00.000 5.00000000A051AK013.A03.COK106_001.0.1 2012-01-17 00:00:00.000 10.00000000A051AK013.A03.COK106_001.0.2 2012-01-17 00:00:00.000 11.00000000A051AK013.A03.COK106_200.0.1 2012-01-17 00:00:00.000 11.00000000A051AK013.A03.COK106_200.0.2 2012-01-17 00:00:00.000 9.00000000A051AK013.A03.COY101_001.0.2 2012-01-17 00:00:00.000 5.00000000A051AK013.A03.COY101_100.0.2 2012-01-17 00:00:00.000 15.00000000A051AK013.A03.COY101_200.0.2 2012-01-17 00:00:00.000 4.00000000A051AK013.A03.COY101_400.0.2 2012-01-17 00:00:00.000 1.00000000A051AK013.A03.COY106_001.0.1 2012-01-17 00:00:00.000 3.00000000A051AK013.A03.COY106_001.0.2 2012-01-17 00:00:00.000 17.00000000A051AK013.A03.COY101_001.0.2 2012-02-03 00:00:00.000 2.00000000A051AK013.A03.COY101_100.0.1 2012-02-03 00:00:00.000 2.00000000A051AK013.A03.COY101_100.0.2 2012-02-03 00:00:00.000 5.00000000A051AK013.A03.COY106_001.0.2 2012-02-03 00:00:00.000 3.00000000A051AK013.A03.MEK001_370.0.0 2012-02-03 00:00:00.000 2.00000000A051AK013.A03.COY101_100.0.1 2012-03-21 00:00:00.000 1.00000000A051AK013.A03.IPY210_001.0.1 2012-09-18 00:00:00.000 15.00000000A051AK013.A03.IPY210_001.0.2 2012-09-18 00:00:00.000 15.00000000A051AK013.A03.COY101_400.0.2 2012-01-03 00:00:00.000 -1.00000000A051AK013.A03.COY106_001.0.1 2012-01-03 00:00:00.000 -1.00000000A051AK013.A03.COK106_200.0.1 2012-01-05 00:00:00.000 -1.00000000A051AK013.A03.7502_200.0.2 2012-01-19 00:00:00.000 -2.00000000A051AK013.A03.COY106_001.0.1 2012-01-23 00:00:00.000 -1.00000000A051AK013.A03.COK106_001.0.2 2012-02-04 00:00:00.000 -1.00000000A051AK013.A03.COY101_001.0.2 2012-02-04 00:00:00.000 -1.00000000A051AK013.A03.COK106_200.0.1 2012-02-11 00:00:00.000 -1.00000000A051AK013.A03.COY101_200.0.2 2012-02-19 00:00:00.000 -1.00000000A051AK013.A03.COY101_001.0.2 2012-02-21 00:00:00.000 -4.00000000A051AK013.A03.MEK001_370.0.0 2012-02-21 00:00:00.000 -1.00000000A051AK013.A03.COK106_001.0.1 2012-02-22 00:00:00.000 -1.00000000A051AK013.A03.COY101_001.0.2 2012-02-22 00:00:00.000 -1.00000000A051AK013.A03.COY101_001.0.2 2012-02-23 00:00:00.000 -1.00000000A051AK013.A03.COY101_400.0.2 2012-02-29 00:00:00.000 -1.00000000A051AK013.A03.COY101_200.0.2 2012-03-12 00:00:00.000 -1.00000000A051AK013.A03.COY106_001.0.1 2012-03-18 00:00:00.000 -1.00000000A051AK013.A03.COY101_100.0.1 2012-03-21 00:00:00.000 -2.00000000A051AK013.A03.COY101_100.0.2 2012-03-22 00:00:00.000 -2.00000000A051AK013.A03.COY101_200.0.2 2012-03-22 00:00:00.000 -2.00000000A051AK013.A03.COY106_001.0.2 2012-03-25 00:00:00.000 -1.00000000A051AK013.A03.MEK001_370.0.0 2012-03-25 00:00:00.000 -1.00000000A051AK013.A03.COY101_100.0.2 2012-04-06 00:00:00.000 -1.00000000A051AK013.A03.COK106_200.0.1 2012-04-14 00:00:00.000 -1.00000000A051AK013.A03.COY101_100.0.2 2012-04-19 00:00:00.000 -1.00000000A051AK013.A03.COY101_100.0.1 2012-04-22 00:00:00.000 -1.00000000A051AK013.A03.COY101_100.0.2 2012-06-02 00:00:00.000 -1.00000000A051AK013.A03.7502_200.0.2 2012-06-20 00:00:00.000 -3.00000000A051AK013.A03.COY101_100.0.2 2012-07-07 00:00:00.000 -1.00000000A051AK013.A03.COY106_001.0.2 2012-07-12 00:00:00.000 -1.00000000A051AK013.A03.COK106_001.0.1 2012-08-25 00:00:00.000 -1.00000000A051AK013.A03.COK106_200.0.1 2012-08-25 00:00:00.000 -1.00000000A051AK013.A03.COK106_001.0.2 2012-09-02 00:00:00.000 -1.00000000A051AK013.A03.COK106_200.0.2 2012-09-02 00:00:00.000 -1.00000000A051AK013.A03.COY101_100.0.2 2012-09-02 00:00:00.000 -2.00000000A051AK013.A03.COK106_001.0.1 2012-09-04 00:00:00.000 -3.00000000A051AK013.A03.COY101_100.0.2 2012-09-08 00:00:00.000 -1.00000000A051AK013.A03.COK106_001.0.2 2012-09-09 00:00:00.000 -1.00000000A051AK013.A03.COY106_001.0.1 2012-09-12 00:00:00.000 -1.00000000A051AK013.A03.COK106_001.0.2 2012-09-16 00:00:00.000 -1.00000000A051AK013.A03.COK106_200.0.1 2012-09-16 00:00:00.000 -1.00000000A051AK013.A03.IPY210_001.0.1 2012-09-21 00:00:00.000 -1.00000000A051AK013.A03.IPY210_001.0.2 2012-09-21 00:00:00.000 -1.00000000A051AK013.A03.COK106_200.0.2 2012-09-22 00:00:00.000 -1.00000000A051AK013.A03.COY101_100.0.2 2012-09-22 00:00:00.000 -1.00000000A051AK013.A03.IPY210_001.0.2 2012-09-26 00:00:00.000 -2.00000000A051AK013.A03.COK106_001.0.1 2012-09-27 00:00:00.000 -1.00000000A051AK013.A03.IPY210_001.0.1 2012-09-27 00:00:00.000 -1.00000000A051AK013.A03.COK106_001.0.1 2012-09-28 00:00:00.000 -1.00000000A051AK013.A03.COK106_001.0.1 2012-10-02 00:00:00.000 -1.00000000A051AK013.A03.COK106_200.0.1 2012-10-02 00:00:00.000 -1.00000000A051AK013.A03.IPY210_001.0.1 2012-10-02 00:00:00.000 -3.00000000A051AK013.A03.IPY210_001.0.2 2012-10-05 00:00:00.000 -1.00000000A051AK013.A03.COK106_001.0.1 2012-10-07 00:00:00.000 -1.00000000A051AK013.A03.COK106_200.0.1 2012-10-07 00:00:00.000 -1.00000000A051AK013.A03.IPY210_001.0.2 2012-10-11 00:00:00.000 -4.00000000A051AK013.A03.COK106_001.0.2 2012-10-13 00:00:00.000 -1.00000000A051AK013.A03.COK106_200.0.1 2012-10-13 00:00:00.000 -3.00000000A051AK013.A03.IPY210_001.0.1 2012-10-22 00:00:00.000 -1.00000000A051AK013.A03.COK106_001.0.1 2012-10-26 00:00:00.000 -1.00000000A051AK013.A03.COK106_200.0.1 2012-10-26 00:00:00.000 -1.00000000A051AK013.A03.IPY210_001.0.1 2012-10-27 00:00:00.000 -2.00000000A051AK013.A03.IPY210_001.0.1 2012-11-10 00:00:00.000 -1.00000000A051AK013.A03.COK106_200.0.1 2012-11-11 00:00:00.000 -1.00000000A051AK013.A03.IPY210_001.0.2 2012-11-15 00:00:00.000 -1.00000000A051AK013.A03.IPY210_001.0.1 2012-11-18 00:00:00.000 -1.00000000

sunitabeck
Flowing Fount of Yak Knowledge

5155 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
Aged Yak Warrior

Romania
545 Posts

 Posted - 11/27/2012 :  01:31:13 Hi,Yes, as Sunitabeck said, filter also after inventory codeTell us, how it worksCe-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb

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 datetimeset @d1 ='20121212';with Aas(select'ABC' Inventory_Code,'2012-01-01' Trans_Date, 1 Quantity union allselect'ABC' ,'2012-01-17', 11 union allselect 'ABC','2012-01-05', -1 union allselect 'ABC','2012-02-11', -1 union allselect 'ABC','2012-04-14', -1 union allselect 'ABC','2012-08-25', -1 union allselect 'ABC','2012-09-16', -1 union allselect 'ABC','2012-10-02', -1 union allselect 'ABC','2012-10-07', -1 union allselect 'ABC','2012-10-13', -3 union allselect 'ABC','2012-10-26', -1 union allselect '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 Diferfrom A as AAouter apply (select sum(Quantity) as Q from A where AA.Trans_date< A.trans_date and Quantity < 0 and A.trans_date<=@d1AND a.Inventory_Code = AA.Inventory_Code) Souter 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) SSwhere Quantity >0and AA.trans_date<=@d1 order by AA.Inventory_CodeResulting Set:ABC 2012-01-01 1 0 0ABC 2012-01-17 11 1 330But 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 0ABC 2012-01-17 11 0 0Any ideas Stepson?Thanks again. Edited by - seireina on 11/27/2012 08:36:50

stepson
Aged Yak Warrior

Romania
545 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<=@d1AND a.Inventory_Code = AA.Inventory_Code) SCe-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb 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
Aged Yak Warrior

Romania
545 Posts

 Posted - 12/03/2012 :  01:01:42 w.welcomeCe-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb

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 sold1000 2012-01-01 100 01000 2012-01-03 0 201000 2012-01-07 80 01000 2012-01-09 0 -1201000 2012-01-13 0 -20How to Create a Qty Remaining Field and the table becomeInventory_Code |Trans_Date | Qty recd | Qty sold | Qty Remaining 1000 2012-01-01 100 0 1001000 2012-01-03 0 20 801000 2012-01-07 80 0 1601000 2012-01-09 0 -120 401000 2012-01-13 0 -20 20quote:Originally posted by sunitabeckIf 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 incorrectCan 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
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC