Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    12

    Unanswered: Select Query to Calculate FiFO Inventory Aging - No Cursors

    I have to caculate the fifo inventory aging from purchase date, for example I have one item with 100 units in stock, and purchase this month 20 units, and 200 units three month ago, this item has 20 units this month and 80 units from 3 month ago.

    Data is as follows:
    <<<<< ORIGINAL DATA >>>>>>>>>>>>>>>>
    Code:
    Code	        Stock	PO	        Date	            PO_Qty	
    ao0-123	100	OC0010	10-08-2014	    8	        
    ao0-123	100	OC0011	10-08-2014	    4	        
    ao0-123	100	OC0020	11-10-2014	    50	        
    ao0-123	100	OC0021	11-10-2014	    2	        
    ao0-123	100	OC0022	11-10-2014	    20	        
    ao0-123	100	OC0030	12-11-2014	    10	        
    ao0-123	100	OC0031	12-11-2014	    1	        
    ao0-123	100	OC0032	12-11-2014	    20
    And the reuslt must be something like this:
    <<<<< ORIGINAL DATA >>>>>>>>>>>>>>>> <<<<< CALCULATED DATA >>>>>>
    Code:
    Code	        Stock	        PO	                Date	     	   PO_Qty	Days_Old   Qty_From_PO  Residue
    ao0-123	100		OC0010		10-08-2014	    8	        135	        8	            92
    ao0-123	100		OC0011		10-08-2014	    4	        135	        4	            88
    ao0-123	100		OC0020		11-10-2014	    50	        73	        50	            38
    ao0-123	100		OC0021		11-10-2014	    2	        73	        2	            36
    ao0-123	100		OC0022		11-10-2014	    20	        73	        20	            16
    ao0-123	100		OC0030		12-11-2014	    10	        41	        10	            6
    ao0-123	100		OC0031		12-11-2014	    1	        41	        1	            5
    ao0-123	100		OC0032		12-11-2014	    20	        41	        5	            0
    I need to built a select to make this calculation, not using cursors, and build a view, only a basic select.

    Any idea is wellcome,

    Thanks all and Merry christmas
    Alfredo
    Last edited by gvee; 12-29-14 at 06:41. Reason: [code] tags added to retain formatting of data

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This should get you started
    Code:
    DECLARE @t table (
       code        char(7)
     , stock       int
     , po          char(6)
     , the_date    date
     , po_quantity int
    );
    
    INSERT INTO @t (code, stock, po, the_date, po_quantity)
      VALUES ('ao0-123', 100, 'OC0010', '2014-10-08',  8)
           , ('ao0-123', 100, 'OC0011', '2014-10-08',  4)
           , ('ao0-123', 100, 'OC0020', '2014-11-10', 50)
           , ('ao0-123', 100, 'OC0021', '2014-11-10',  2)
           , ('ao0-123', 100, 'OC0022', '2014-11-10', 20)
           , ('ao0-123', 100, 'OC0030', '2014-12-11', 10)
           , ('ao0-123', 100, 'OC0031', '2014-12-11',  1)        
           , ('ao0-123', 100, 'OC0032', '2014-12-11', 20)
    ;
    
    ; WITH cte AS (
      SELECT code
           , stock
           , po
           , the_date
           , po_quantity
           , Row_Number() OVER (PARTITION BY code ORDER BY the_date, po) As sequence
      FROM   @t
    )
    SELECT *
    FROM   cte As curr
     LEFT
      JOIN cte As nxt
        ON nxt.code = curr.code
       AND nxt.sequence = curr.sequence - 1
    ;
    Results:
    Code:
    code    stock       po     the_date   po_quantity sequence             code    stock       po     the_date   po_quantity sequence
    ------- ----------- ------ ---------- ----------- -------------------- ------- ----------- ------ ---------- ----------- ---------
    ao0-123 100         OC0010 2014-10-08 8           1                    NULL    NULL        NULL   NULL       NULL        NULL
    ao0-123 100         OC0011 2014-10-08 4           2                    ao0-123 100         OC0010 2014-10-08 8           1
    ao0-123 100         OC0020 2014-11-10 50          3                    ao0-123 100         OC0011 2014-10-08 4           2
    ao0-123 100         OC0021 2014-11-10 2           4                    ao0-123 100         OC0020 2014-11-10 50          3
    ao0-123 100         OC0022 2014-11-10 20          5                    ao0-123 100         OC0021 2014-11-10 2           4
    ao0-123 100         OC0030 2014-12-11 10          6                    ao0-123 100         OC0022 2014-11-10 20          5
    ao0-123 100         OC0031 2014-12-11 1           7                    ao0-123 100         OC0030 2014-12-11 10          6
    ao0-123 100         OC0032 2014-12-11 20          8                    ao0-123 100         OC0031 2014-12-11 1           7
    Basically we assign a virtual row number for each row and then join it back to itself to get the current row and "next" row alongside each other.

    I've made some assumptions about your DDL as you've not provided any.

    Any questions, just ask.
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2005
    Posts
    12
    Thanks George, starting from your recommendation, now I've the solution for my problem, as follows:

    I've changed the data and naming to be clearer.

    Code:
    DECLARE @Tmp_inventory table (
       SKU	char(7)
     , Stock_level	int
     , PO_Number	char(6)
     , Stocking_Date Date
     , Stocking_Qty int
    );
    INSERT INTO @Tmp_inventory (SKU, Stock_level, PO_Number, Stocking_Date, Stocking_Qty)
    VALUES 
    ('ao0-123',100,'OC0030','2014-12-25',8)
    ,('ao0-123',100,'OC0031','2014-12-28',90)
    ,('ao0-123',100,'OC0011','2014-10-08',20)
    ,('ao0-124',30,'OC0030','2014-12-11',80)
    ,('ao0-125',27,'OC0030','2014-12-11',15)
    ,('ao0-125',27,'OC0031','2014-12-11',30)
    ,('ao0-125',27,'OC0032','2014-12-11',6)
    ,('ao0-126',15,'OC0030','2014-12-11',12)
    ,('ao0-126',15,'OC0022','2014-11-10',27)
    ,('ao0-127',300,'OC0030','2014-12-11',20)
    ,('ao0-127',300,'OC0020','2014-11-10',35)
    ,('ao0-127',300,'OC0021','2014-11-10',40)
    ;
    ;WITH CTE_Inventory
    AS (
    	SELECT SKU
    	, Stock_level
    	, PO_Number
    	, Stocking_Date
    	, Stocking_Qty
    	,Row_Number() OVER (PARTITION BY SKU ORDER BY SKU, Stocking_Date DESC, PO_Number) AS InStock_Sequence
    	, CASE WHEN (Sum(Stocking_Qty) OVER 
    				(PARTITION BY SKU ORDER BY SKU, Stocking_Date DESC, PO_Number)) < Stock_level
    				THEN Stocking_Qty 
    				ELSE Stocking_Qty - (Sum(Stocking_Qty) OVER 
    				(PARTITION BY SKU ORDER BY SKU, Stocking_Date DESC, PO_Number)) + Stock_level 
    				END AS Stock_FromPO
    	,CASE WHEN (Stock_level - (sum(Stocking_Qty) OVER 
    			    (PARTITION BY SKU ORDER BY SKU, Stocking_Date DESC, PO_Number))) > 0 
    				THEN (Stock_level - (	sum(Stocking_Qty) OVER 
    				(PARTITION BY SKU ORDER BY SKU, Stocking_Date DESC, PO_Number))) 
    				ELSE 0 END AS Stock_Residue
    	, DATEDIFF(day, Stocking_Date, getdate()) Inv_Age
    	FROM @Tmp_inventory
    	)
    SELECT 
    CINV.SKU
    , CINV.Stock_level
    , CINV.PO_Number
    , CINV.Stocking_Date
    , CINV.Stocking_Qty
    , CINV.InStock_Sequence
    , CINV.Stock_FromPO
    , CINV.Stock_Residue
    , CINV.Inv_Age
    FROM CTE_Inventory AS CINV
    WHERE CINV.Stock_FromPO > 0;

    Code:
    SKU	Stock_L	PO#     Stocking_Date	SInQty	InSeq	SFromPO	SRes	Inv_Age
    ao0-123	100	OC0031	28-12-2014	90	1	90	10	1
    ao0-123	100	OC0030	25-12-2014	8	2	8	2	4
    ao0-123	100	OC0011	08-10-2014	20	3	2	0	82
    ao0-124	30	OC0030	11-12-2014	80	1	30	0	18
    ao0-125	27	OC0030	11-12-2014	15	1	15	12	18
    ao0-125	27	OC0031	11-12-2014	30	2	12	0	18
    ao0-126	15	OC0030	11-12-2014	12	1	12	3	18
    ao0-126	15	OC0022	10-11-2014	27	2	3	0	49
    ao0-127	300	OC0030	11-12-2014	20	1	20	280	18
    ao0-127	300	OC0020	10-11-2014	35	2	35	245	49
    ao0-127	300	OC0021	10-11-2014	40	3	40	205	49
    Last edited by afrugone; 12-29-14 at 19:27.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I have to caculate the fifo inventory aging from purchase date
    Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. Why do you think that “stocking_date” is clear and precise? That there is only one “<generic magic>_code” in the universe? I will guess it is really a SKU

    You are writing SQL like it was 1960's BASIC, when we used files, never shared data and had only short data element names that were local to file.

    You should follow ISO-8601 rules for displaying temporal data. But instead, you used a local dialect which is ambiguous! We need to know the data types, keys (you showed no key!) and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.


    And you need to read and download the PDF for:
    https://www.simple-talk.com/books/sq...ql-sku-smells/

    Hrfe is my guess at the dates, keys and constraints you did not give us.

    CREATE TABLE Inventory
    (sku CHAR(7) NOT NULL,
    stock_level INTEGER NOT NULL CHECK(stock_level >= 0),
    po_nbr CHAR(6) NOT NULL PRIMARY KEY,
    stocking_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    po_qty INTEGER NOT NULL CHECK (po_qty > 0)
    );

    INSERT INTO Inventory
    VALUES
    ('ao0-123', 100, 'OC0010', '2014-10-08', 8),
    ('ao0-123', 100, 'OC0011', '2014-10-08', 4),
    ('ao0-123', 100, 'OC0020', '2014-11-10', 50),
    ('ao0-123', 100, 'OC0021', '2014-11-10', 2),
    ('ao0-123', 100, 'OC0022', '2014-11-10', 20),
    ('ao0-123', 100, 'OC0030', '2014-12-11', 10),
    ('ao0-123', 100, 'OC0031', '2014-12-11', 1) ,
    ('ao0-123', 100, 'OC0032', '2014-12-11', 20);

    SELECT sku, stock_level, po_nbr, stocking_date, po_qty,
    DATEDIFF (days, current_timestamp, stocking_date)
    AS inventory_age,
    (stock_level -
    SUM(po_qty)
    OVER (PARTITION BY sku ORDER BY po_nbr
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    ) AS onhand_qty
    FROM Inventory;

    Untested. Read up on the window clause for things like this.

  5. #5
    Join Date
    Mar 2005
    Posts
    12
    Thanks Celko,

    I'm sorry I'm not a programer and I'm new here, anyway I've follow your instrucctions, and is very nice and short solution, I write is as follows:

    Code:
    DECLARE @Tmp_inventory table (
       SKU	char(7)
     , Stock_level	int
     , PO_Number	char(6)
     , Stocking_Date Date
     , Stocking_Qty int
    );
    
    INSERT INTO @Tmp_inventory (SKU, Stock_level, PO_Number, Stocking_Date, Stocking_Qty)
    VALUES 
    ('ao0-123',100,'OC0030','2014-12-25',8)
    ,('ao0-123',100,'OC0031','2014-12-28',90)
    ,('ao0-123',100,'OC0011','2014-10-08',20)
    ,('ao0-124',30,'OC0030','2014-12-11',80)
    ,('ao0-125',27,'OC0030','2014-12-11',15)
    ,('ao0-125',27,'OC0031','2014-12-11',30)
    ,('ao0-125',27,'OC0032','2014-12-11',6)
    ,('ao0-126',15,'OC0030','2014-12-11',12)
    ,('ao0-126',15,'OC0022','2014-11-10',27)
    ,('ao0-127',300,'OC0030','2014-12-11',20)
    ,('ao0-127',300,'OC0020','2014-11-10',250)
    ,('ao0-127',300,'OC0021','2014-11-10',400)
    ;
    
    SELECT SKU, Stock_level, PO_Number, Stocking_Date, Stocking_Qty
    		, CASE WHEN (
    		  sum(Stocking_Qty) OVER (PARTITION BY SKU ORDER BY SKU, Stocking_Date DESC 
    				ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) < Stock_level 
    		  THEN Stocking_Qty ELSE Stocking_Qty + Stock_level - sum(Stocking_Qty) 
    			OVER (PARTITION BY SKU ORDER BY SKU, Stocking_Date DESC 
    				ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
    		END AS Stock_FromPO
    		,DateDiff(DD,stocking_date,getdate()) Inventory_Age
    FROM @Tmp_inventory
    Where Stock_FromPO, is the stock that comes from each Purchase Order in a FiFo, it's assumed that the oldest product is consumed first.

    Code:
    SKU		Stock_level	PO_Number	Stocking_Date	Stocking_Qty	Stock_FromPO	Inventory_Age
    ao0-123		100		OC0031		2014-12-28	90		90		2
    ao0-123		100		OC0030		2014-12-25	8		8		5
    ao0-123		100		OC0011		2014-10-08	20		2		83
    ao0-124		30		OC0030		2014-12-11	80		30		19
    ao0-125		27		OC0030		2014-12-11	15		15		19
    ao0-125		27		OC0031		2014-12-11	30		12		19
    ao0-125		27		OC0032		2014-12-11	6		-18		19
    ao0-126		15		OC0030		2014-12-11	12		12		19
    ao0-126		15		OC0022		2014-11-10	27		3		50
    ao0-127		300		OC0030		2014-12-11	20		20		19
    ao0-127		300		OC0020		2014-11-10	250		250		50
    ao0-127		300		OC0021		2014-11-10	400		30		50
    Last edited by afrugone; 12-30-14 at 11:09.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •