locked
FIFO Inventory Query RRS feed

  • Question

  • I'm struggling trying to write a query to result in a list of purchases and the quantities that remain from them assuming that we always sell the oldest units in stock first.

    My sample date is this list of transactions.

    Item Date Qty
    1 1/5/2001 100
    1 2/9/2002 -10
    1 4/1/2002 50
    1 6/21/2002 -100
    1 10/6/2003 25

    My expected results are:

    Item Date QtyLeft
    1 1/5/2001 0
    1 4/1/2002 40
    1 10/6/2003 25

    Tuesday, July 22, 2014 6:34 PM

Answers

  • If you are guaranteed that you do not have multiple entries for the same date and item, you can do

    Declare @Sample Table(Item int,	Date date,	Qty int);
    Insert @Sample(Item, Date, Qty) Values
    (1, 	'1/5/2001', 	100),
    (1, 	'2/9/2002', 	-10),
    (1, 	'4/1/2002', 	50),
    (1, 	'6/21/2002', 	-100),
    (1, 	'10/6/2003', 	25),
    (2, 	'6/21/2012', 	37),
    (2, 	'6/30/2012', 	-20),
    (2, 	'10/6/2013', 	-8);
    
    ;With cte As
    (Select Item, Date, Qty, Sum(Case When Qty > 0 Then 0 Else -1 * Qty End) Over(Partition By Item Order By Date Desc) As CountDown,
       Sum(Qty) Over(Partition By Item Order By Date) As CountUp
    From @Sample)
    Select Item, Date, Case When CountDown > CountUp Then 0 When CountUp - CountDown < Qty Then CountUp - CountDown Else Qty End As QtyLeft
    From cte
    Where Qty > 0
    Order By Item, Date;
    

    If you can have multiple entries for the same date and item, then you must have a method of ordering the rows with the same date and item.  Usually that would be the primary key of your table.  Then you can do

    Declare @Sample Table(PK int identity primary key, Item int,	Date date,	Qty int);
    Insert @Sample(Item, Date, Qty) Values
    (1, 	'1/5/2001', 	100),
    (1, 	'2/9/2002', 	-10),
    (1, 	'4/1/2002', 	50),
    (1, 	'6/21/2002', 	-100),
    (1, 	'10/6/2003', 	25),
    (2, 	'6/21/2012', 	15),
    (2, 	'6/21/2012', 	37),
    (2, 	'6/21/2012', 	-20),
    (2, 	'10/6/2013', 	-8);
    
    ;With cte As
    (Select PK, Item, Date, Qty, Sum(Case When Qty > 0 Then 0 Else -1 * Qty End) Over(Partition By Item Order By Date Desc, Qty Desc, PK Desc) As CountDown,
       Sum(Qty) Over(Partition By Item Order By Date, Qty, PK) As CountUp
    From @Sample)
    Select PK, Item, Date, Case When CountDown > CountUp Then 0 When CountUp - CountDown < Qty Then CountUp - CountDown Else Qty End As QtyLeft
    From cte
    Where Qty > 0
    Order By Item, Date, PK;
    Tom

    Tuesday, July 22, 2014 7:19 PM

All replies

  • Hi Can you please do a bit explanatory for the example above for source table and expected results. (like how it transforms into expected result). So that it will be easy to help you out.
    Tuesday, July 22, 2014 6:46 PM
  • I am selecting only the purchases (rows with a positive quantity) and not the sales. Then for each of these rows I am selecting the quantity that was purchased in that instance that is still left in inventory today. So, for the first row of 100, 10 were sold on 2/9 leaving 90 left. Then 100 were sold on 6/21. 90 of these 100 came from the original 100 purchased on 1/5, leaving 0 left in stock from that 1/5 purchase. the remaining 10 sold on 6/21 came from the 4/1 purchase of 50 units. This leaves 40 left in stock from that 4/1 purchase. No additional units were sold after the 10/6 purchase, so all 25 of those are still in stock.

    I hope this helps explain the results I'm trying to achieve.

    Tuesday, July 22, 2014 6:54 PM
  • Please, have a look at this great Wiki article written by Naomi :

    T-SQL: FIFO Inventory Problem - Cost of Goods Sold by Naomi N


    Saeid Hasani [sqldevelop]

    Tuesday, July 22, 2014 6:54 PM
  • If you are guaranteed that you do not have multiple entries for the same date and item, you can do

    Declare @Sample Table(Item int,	Date date,	Qty int);
    Insert @Sample(Item, Date, Qty) Values
    (1, 	'1/5/2001', 	100),
    (1, 	'2/9/2002', 	-10),
    (1, 	'4/1/2002', 	50),
    (1, 	'6/21/2002', 	-100),
    (1, 	'10/6/2003', 	25),
    (2, 	'6/21/2012', 	37),
    (2, 	'6/30/2012', 	-20),
    (2, 	'10/6/2013', 	-8);
    
    ;With cte As
    (Select Item, Date, Qty, Sum(Case When Qty > 0 Then 0 Else -1 * Qty End) Over(Partition By Item Order By Date Desc) As CountDown,
       Sum(Qty) Over(Partition By Item Order By Date) As CountUp
    From @Sample)
    Select Item, Date, Case When CountDown > CountUp Then 0 When CountUp - CountDown < Qty Then CountUp - CountDown Else Qty End As QtyLeft
    From cte
    Where Qty > 0
    Order By Item, Date;
    

    If you can have multiple entries for the same date and item, then you must have a method of ordering the rows with the same date and item.  Usually that would be the primary key of your table.  Then you can do

    Declare @Sample Table(PK int identity primary key, Item int,	Date date,	Qty int);
    Insert @Sample(Item, Date, Qty) Values
    (1, 	'1/5/2001', 	100),
    (1, 	'2/9/2002', 	-10),
    (1, 	'4/1/2002', 	50),
    (1, 	'6/21/2002', 	-100),
    (1, 	'10/6/2003', 	25),
    (2, 	'6/21/2012', 	15),
    (2, 	'6/21/2012', 	37),
    (2, 	'6/21/2012', 	-20),
    (2, 	'10/6/2013', 	-8);
    
    ;With cte As
    (Select PK, Item, Date, Qty, Sum(Case When Qty > 0 Then 0 Else -1 * Qty End) Over(Partition By Item Order By Date Desc, Qty Desc, PK Desc) As CountDown,
       Sum(Qty) Over(Partition By Item Order By Date, Qty, PK) As CountUp
    From @Sample)
    Select PK, Item, Date, Case When CountDown > CountUp Then 0 When CountUp - CountDown < Qty Then CountUp - CountDown Else Qty End As QtyLeft
    From cte
    Where Qty > 0
    Order By Item, Date, PK;
    Tom

    Tuesday, July 22, 2014 7:19 PM