Post reply

Help with query to calculate inventory ageing - FIFO

  • December 16, 2015 at 7:38 am

    #314251

    I'm trying to write a query to calculate my inventory ageing but am struggling to get it to work.

    Appreciate any help.

    This is my example with inventory going in and out of a warehouse:

    itemid | date | qty

    1 | 2009-06-01 | 10

    1 | 2009-07-01 | -5

    1 | 2009-08-01 | 5

    1 | 2009-08-01 | -5

    1 | 2009-09-01 | 10

    1 | 2009-09-01 | -2

    The result set should show:

    The sale on 2009-07-01 was allocated to the purchase dated 2009-06-01

    The sale on 2009-08-01 was allocated to the purchase dated 2009-06-01

    This removes all stock from that date so it can be ignored now.

    The sale on 2009-09-01 was allocated to the purchase dated 2009-08-01

    So the ageing for the remaining inventory balance as of today should be.

    date qtybal

    2009-08-01 3

    2009-09-01 10

    I believe I need to use the row_number () over partition statement but have had no luck so far.

    USE [tempdb]

    GO

    drop table InvAge

    CREATE TABLE [dbo].[InvAge](

    [Itemid] int NOT NULL,

    [Doctype] varchar (10) NOT NULL,

    [Location] varchar (10) NOT NULL,

    [Dates] datetime NOT NULL,

    [QTY] int NOT NULL

    ) ON [PRIMARY]

    GO

    INSERT INTO [InvAge]

    SELECT 1, 'Receipt', '10GEI', '2009-06-01', 10 UNION ALL

    SELECT 1, 'Sale','10GEI','2009-07-01', -5 UNION ALL

    SELECT 1, 'Transfer', '10GEI','2009-08-01', 5 UNION ALL

    SELECT 1, 'Sale', '10GEI','2009-08-01', -5 UNION ALL

    SELECT 1, 'Receipt', '10GEI','2009-09-01', 10 UNION ALL

    SELECT 1, 'Sale', '10GEI','2009-09-01', -2;

    with cte as(

    select itemid, location, qty, dates, row_number() over (partition by itemid, location order by itemid, location) as rank from invage

    )

    SELECT a.rank, a.itemid, a.location, a.qty, a.dates from cte a

    left join cte b on a.itemid=b.itemid and a.location = b.location and a.rank = b.rank-1;

  • December 16, 2015 at 8:46 am

    #1846333

    This looks remarkably similar to:

    https://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/

  • December 16, 2015 at 9:10 am

    #1846348

    Thanks.

    I saw this post as well.

    It is similiar as it demonstrates what the value of stock is using FIFO.

    I need to know the ageing of my remaining stock balance.

    With a balance of 5 items today, I need to know which ones are up to 30 days old and which ones are older etc.

  • December 22, 2015 at 6:58 am

    #1847429

    Have it sorted now with the example from the above link.

  • September 10, 2017 at 3:56 pm

    #1958873

    boettger.andreas - Tuesday, December 22, 2015 6:58 AM

    Have it sorted now with the example from the above link.

    Do you mind sharing your query that got this to work?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply