Insert into Column until certain value is reached (FIFO based Insertion)

Question

I am using ms server 2008 and have no clue how to design a FIFO based Insertion into a specific column until a certain amount of an other column is reached.

Basically all I want is just to order the columns by shipment date / item received date, fill the column "goods value received" until the value in the column "price" is reached and insert the remaining value in the column "goods remaining". So I know how much is still remaining. Is it possible?

For example I have a table with goods (id, price, shipment date from, shipment date to). What I want to do is, I would like to order this list by goods_id and the shipment dates. I have a second table that archives all received goods. This second table has a goods id, the value of the goods and the date I received the items.

So what I basically want describes the following example

Goods Table:

|goods id|price|shipment date from| shipment date to|goods value remaining | goods value received|
     1       100   2016-09-26         2016-10-30         NULL                             NULL
     1       200   2016-09-22         2016-10-30         NULL                             NULL
     1       50    2016-09-23         2016-10-30         NULL                             NULL
     2       500   2016-05-10         2016-09-30         NULL                             NULL
     2       20    2016-05-12         2016-09-30         NULL                             NULL
     2       300   2016-05-04         2016-09-30         NULL                             NULL
     3       400   2016-10-12         2016-11-30         NULL                             NULL
     3       300   2016-10-06         2016-11-30         NULL                             NULL 


Goods received Table

|goods id|item_value|item_received_date|

     1        20   2016-09-26       
     1       200   2016-09-22              
     2       500   2016-05-10         
     2       20    2016-05-12        
     2       400   2016-05-04         
     3       400   2016-10-12        
     3       100   2016-10-06        



INSERT into Goods Table with FIFO System:

First step:
order by shipment date:
|goods id|price|shipment date from| shipment date to|goods value remaining | goods value received|
     2       300   2016-05-04         2016-09-30            NULL              NULL
     2       500   2016-05-10         2016-09-30            NULL              NULL
     2       20    2016-05-12         2016-09-30            NULL              NULL
     1       200   2016-09-22         2016-10-30            NULL              NULL
     1       50    2016-09-23         2016-10-30            NULL              NULL
     1       100   2016-09-26         2016-10-30            NULL              NULL
     3       300   2016-10-06         2016-11-30            NULL              NULL 
     3       400   2016-10-12         2016-11-30            NULL              NULL

Second step:
Order the received goods table by "item received date".

     1       200   2016-09-22  
     1        20   2016-09-26  
     2       400   2016-05-04   
     2       500   2016-05-10         
     2       20    2016-05-12       
     3       400   2016-10-06      
     3       100   2016-10-12        



Insert into column (goods value received) with FIFO System until "price" is reached. If the price has rechead, begin to insert into next row until the price of the column has reached:

example:

   |goods id|price|shipment date from| shipment date to| goods value remaining |goods value received|
     2       300   2016-05-04         2016-09-30                                     300    -- 100 left (insert the 100 on the next row and add the remaining value)
     2       500   2016-05-10         2016-09-30                                     500    -- added 100 and insert 400 of 500. 100 are left
     2       20    2016-05-12         2016-09-30                                     20     -- added 20. 80 from last row are left + 20 are left from the received table. finished
     1       200   2016-09-22         2016-10-30                                     200    -- added 200 of the received table. 
     1       50    2016-09-23         2016-10-30                                     20     -- the last 20 added of the received table. 
     1       100   2016-09-26         2016-10-30             130                     0      -- 130 are remaining! This value I need to know!
     3       300   2016-10-06         2016-11-30                                     300    -- added 300 of 400. 100 remaining.
     3       400   2016-10-12         2016-11-30             200                     200    -- added 100 of the last row, added 100 of the received item table. 200 are left. this is the value I need to know.

I would really be grateful if you can help me out with this because I have no idea how to code it.

Thank you, Jinks

Can you please take one goods id and exlplain your requirement — TheGameiswar
I allready explained the requirement int the last step. I also comment every step. — Jinks
I am actually very confused why your id column is not unique. Besides that, as I understand the logic you are trying to build is something I would advice you to do on app level — stb
Thank you for your response. Basically all I want is just to order the columns by shipment date / item received date, fill the column "goods value received" until the value in the column "price" is reached and insert the remaining value in the column "goods remaining". So I know how much is still remaining. Is it possible? — Jinks
It looks like some variation of the running sum/total. In SQL Server 2012+ it can be done efficiently. In 2008 the most efficient way could be a cursor. Or do it on the client. — Vladimir Baranov

Rupesh Rupesh · Accepted Answer · 2016-10-05T13:06:28

You need to create stored procedure for your requirement.

Create a cursor for Goods (say C1) then for C1.goods_id create cursor for Received Goods (say C2).

Create two loops. Loop for cursor C1 is outer and loop for cursor C2 is nested. Loop for C2 always runs for C1.goods_id.

Inside the nested loop process received goods records and update C1.goods_value_received as per your requirement.

Insert into Column until certain value is reached (FIFO based Insertion)

1 Answers