none
SQL FIFO Query RRS feed

  • Question

  • Hello all;

    Like others, I need help developing a SQL query to determine A FIFO Cost for parts. I'll make mine easier that others I have read in this forum by limiting it to one table.

    I have an Inventory Costing Table that contains data like so...

    Part#          Date                 Cost          QuantityProduced

    AA001        01/01/2011        .0158        12500

    AA001        02/15/2011        .0214        7500

    AA001        03/15/2011        .0196        12125

    AA001        04/15/2011        .0211        6300

    What I need to do is write a query that will return the cost depending on the quantity shipped. For example, this table shows the quantity and cost of parts produced - what I need to know is how to extract the cost if say 25000 parts were shipped. Looking at the table (and doing some math), if 25000 parts were shipped then the FIFO cost would be .0196 for 7125 pieces, .0211 for the next 6300 pieces and so forth  - because the running total of the quantity shipped is less than the running total of the quantity produced in that tier.

    How do I write a query that finds the correct Cost if I know the total quantity shipped?

     


    Todd Lerew
    • Moved by Aspen VJ Friday, June 10, 2011 6:43 AM (From:Visual C# General)
    Tuesday, June 7, 2011 5:24 PM

Answers

  • Sorry, I haven't replied to the threads. What I ended up doing was adding columns to the Inventory Costing Table to define the Upper and Lower Limit of the Cost Tier. I write the data to these new columns for every entry in this table - these new columns are basically running totals. LowerLimit = LastUpperLimit +1, UpperLimit = LowerLimit + ProductionQuantity. Once I have these values in this table, I can run a SQL Query in my program that sums TotalPartsSold to date. I then run another query where TotalPartsSold is between the LowerLimit and UpperLimit in this other table and I get the FIFO Cost. This may have not been the best or easier way to do this, but it works good.

    Thanks for everyone's input on this.

     

     


    Todd Lerew
    • Marked as answer by ToddTFPCorp Friday, June 17, 2011 5:25 PM
    Friday, June 17, 2011 5:25 PM

All replies

  • You need store procedure with if statements rather if I were you I will wring the logic in C#.

    chanmm


    chanmm
    Wednesday, June 8, 2011 5:50 AM
  • Hi Todd,

    Based on your description, I think your issue is more appropriate in the Transact-SQL forum. I will move this thread to Transact-SQL forum. Thanks for understanding.


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, June 10, 2011 6:42 AM
  • Hi Todd,

    I think we need to calculate the running total of QuantityProduced and compare it with the quantity shipped. If you are working with SQL Server 2005 or a later version, please refer to the following sample:

    DECLARE @t TABLE (Part VARCHAR(50),DATE DATETIME,Cost DECIMAL(18,4),QuantityProduced INT)
    
    INSERT INTO @t VALUES ('AA001','12/12/2010',0.0200,5000)
    INSERT INTO @t VALUES ('AA001','01/01/2011',0.0158,12500)
    INSERT INTO @t VALUES ('AA001','02/15/2011',0.0214,7500)
    INSERT INTO @t VALUES ('AA001','03/15/2011',0.0196,12125)
    INSERT INTO @t VALUES ('AA001','04/15/2011',0.0211,6300)
    INSERT INTO @t VALUES ('AA002','04/16/2011',0.0210,5000)
    
    DECLARE @Part VARCHAR(50)
    DECLARE @Date DATETIME 
    DECLARE @QuantityShipped INT
     
    SET @Part = 'AA001'
    SET @Date = '02/01/2011'
    SET @QuantityShipped = 25000
    
    SELECT * FROM @t
    
    ;WITH cte1
    AS
    (
    	SELECT Part, DATE, Cost, QuantityProduced
    	FROM @t WHERE Part = @Part AND DATE >= @Date
    )
    ,cte2
    AS
    (
    	SELECT a.Part, a.Cost,a.QuantityProduced,ISNULL(SUM(b.QuantityProduced),0) AS 'RunningTotal'
    	FROM cte1 a LEFT OUTER JOIN cte1 b
    	ON b.Date < a.Date
    	GROUP BY a.Part, a.Cost,a.QuantityProduced
    	
    )
    
    SELECT Part, 
        SUM(COST * CASE 
              WHEN QuantityProduced + RunningTotal <= @QuantityShipped THEN QuantityProduced 
              ELSE @QuantityShipped - RunningTotal 
             END) AS 'CostShipped' 
    FROM cte2 
    GROUP BY Part
    

    If it's not what you want, please show us more sample data and your expected output.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    Friday, June 10, 2011 6:59 AM
  • Hi Todd

     

    You can use the following:

     

    DECLARE @StkBal TABLE (
        PartNo NVARCHAR(25),
        TxDate DATE,
        Cost DECIMAL(19,4),
        Quantity DECIMAL(19,2),
        RunningValue DECIMAL(19,2)
      );
      
    DECLARE @TestData TABLE (
        PartNo NVARCHAR(25),
        TxDate DATE,
        Cost DECIMAL(19,4),
        Quantity DECIMAL(19,2)
      );
      
    INSERT INTO @TestData(PartNo, TxDate, Cost, Quantity) VALUES('AA001', '20110101', 0.0158, 12500);
    INSERT INTO @TestData(PartNo, TxDate, Cost, Quantity) VALUES('AA001', '20110215', 0.0214, 7500);
    INSERT INTO @TestData(PartNo, TxDate, Cost, Quantity) VALUES('AA001', '20110315', 0.0196, 12125);
    INSERT INTO @TestData(PartNo, TxDate, Cost, Quantity) VALUES('AA001', '20110415', 0.0211, 6300);
    INSERT INTO @TestData(PartNo, TxDate, Cost, Quantity) VALUES('AA002', '20110101', 0.0158, 12500);
    
      
    WITH Balance AS (
      SELECT PartNo, TxDate, Cost, Quantity
      FROM @TestData t
    )  
    INSERT INTO @StkBal(PartNo, TxDate, Cost, Quantity, RunningValue)    
    SELECT PartNo, TxDate, Cost, b.Quantity, (SELECT SUM(b1.Quantity) FROM Balance b1 WHERE b1.PartNo =b.PartNo AND b1.TxDate <= b.TxDate) AS RunningValue
    FROM Balance b
    ORDER BY b.PartNo, b.TxDate ASC;
    
          
    DECLARE @Part NVARCHAR(25)
    DECLARE @Date DATETIME;
    DECLARE @QuantityShipped INT;
     
    SET @Part = N'AA001';
    SET @Date = '20110320';
    SET @QuantityShipped = 25000;
    
      
    WITH T1 AS (
      SELECT s.TxDate, s.PartNo, s.Cost, CASE WHEN s.RunningValue <= @QuantityShipped THEN s.Quantity ELSE
      CASE WHEN (RunningValue-s.Quantity) >= @QuantityShipped THEN 0 ELSE @QuantityShipped - (RunningValue - s.Quantity) END END AS QtyShipped
    FROM @StkBal s
    WHERE TxDate <=@Date AND PartNo =@Part
    )
    SELECT PartNo, SUM(QtyShipped) AS QtyShipped, SUM(Cost * QtyShipped) AS CostValue
    FROM T1
    GROUP BY PartNo;
    
    
    


    Please note that the that the table @StkBal is required to allow for the transaction to be sorted in date order when calculating the runningvalue.

     

    Omar

    Friday, June 10, 2011 9:35 AM
  • Thanks for the responses...

    I looked over the code snippets that you posted and I don't understand the first part where you DECLARE the table and INSERT the data. The tables I am working with will possibly have thousands of records. The SQL Query I need to write is working in the backend of a program designed in Visual Basic. At any given point in time, I need to pull the correct FIFO Cost from this table (InventoryCosting) depending on the pieces sold. I use the SUM Function in a Sales Table in my program to calculate the number sold, and based on this amount I need to extract the Cost.

    Example - InventoryCosting Table, which I gave an example of above, will have thousands of records for all parts produced, their quantities, and current cost at the time of production. I need to query the database for a specific part number taking in consideration that if 100,000 pieces were produced, and 60,000 pieces were sold (based on my SUM Query from the Sales table), then I need to return the Inventory Cost from this table of the 60,001 part. Basically, i need to having a running total of the manufactured pieces in this table and be able to determine the cost where the 60,0001 corresponds to.

    Looking at my sample table above, I can see that the 20,001 part sold has a cost of .0196 - this is true for the next 12,124 pieces. The 32,126 piece sold has a cost of .0211, as so on...

    Looking at my table for this specific part number (AA001) -

    Pieces 0-12,150 has a cost of .0158

    Pieces 12,151 - 20,000 has a cost of .0214

    Pieces 20,001 - 32,125 has a cost of .0196

    Pieces 32,126 - 38,425 has a cost of .0211

    If my total sales of this part number is 25,000, I need to be able to determine the cost of the next piece which happens to fall in the .0196 range. But this table will have an indeterminate number of records. How would I write this query???

    Thanks!

     

     

     


    Todd Lerew
    Saturday, June 11, 2011 1:18 PM
  • I looked over the code snippets that you posted and I don't understand the first part where you DECLARE the table and INSERT the data. The tables I am working with will possibly have thousands of records.

    It is customary in this forum to post table DDL and sample data.  You can can substute your actual table name in the scripts Jian and Omar posted and remove the temporary table and data.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, June 11, 2011 3:48 PM
  • Sorry, I haven't replied to the threads. What I ended up doing was adding columns to the Inventory Costing Table to define the Upper and Lower Limit of the Cost Tier. I write the data to these new columns for every entry in this table - these new columns are basically running totals. LowerLimit = LastUpperLimit +1, UpperLimit = LowerLimit + ProductionQuantity. Once I have these values in this table, I can run a SQL Query in my program that sums TotalPartsSold to date. I then run another query where TotalPartsSold is between the LowerLimit and UpperLimit in this other table and I get the FIFO Cost. This may have not been the best or easier way to do this, but it works good.

    Thanks for everyone's input on this.

     

     


    Todd Lerew
    • Marked as answer by ToddTFPCorp Friday, June 17, 2011 5:25 PM
    Friday, June 17, 2011 5:25 PM
  • Can you please elaborate on your solution and if possible, post some code?

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, July 6, 2012 2:26 AM
    Moderator
  • I did elaborate on how I accomplished this in my last post. I did most of the work in SQL. In my Inventory Costing Table, I added columns to calculate upper and lower limits of the part costing, which is basically a running total.

    My original SQL Table looked like this...

    Part#          Date                 Cost          QuantityProduced

    AA001        01/01/2011        .0158        12500

    AA001        02/15/2011        .0214        7500

    AA001        03/15/2011        .0196        12125

    AA001        04/15/2011        .0211        6300

    It now looks like this...

    Part#          Date                 Cost          QuantityProduced          LowerLimit          UpperLimit

    AA001        01/01/2011        .0158        12500                            0                         12500

    AA001        02/15/2011        .0214        7500                              12501                 20000

    AA001        03/15/2011        .0196        12125                            20001                 32125

    AA001        04/15/2011        .0211        6300                              32126                 38425

    This values are calculated at the time that the data is entered in the table. The LowerLimit is the last UpperLimit plus 1. The UpperLimit is the last Upper Limit plus the QuantityProduced.

    Now, when I have to determine FIFO Cost, I run a Sum Query on Shipment Line Quantities for a specific Part Number. Let's say total quantity shipped to date is 29,456. I then query this table to get the cost where TotalQuantityShippedToDate (29,456) is between LowerLimit and UpperLimit. In this instance, the FIFO Cost would be .0196.

    This may not be the best way to do this, but it works fine and I do have a record of the different costing brackets by date (which is nice). After I created the new table, there are a couple processes that have to be done to calculate this, but it runs pretty fast, so I am happy with it.


    Todd Lerew

    Friday, July 6, 2012 2:00 PM
  • Thanks, Todd. In our case we don't store the Lower/Upper limits in the table. Also, we're storing both Purchases (adjustments and positive/negative transfers (analogue of produced)) and Sales (and returns) in the same table.

    I am thinking how can I calculate the cost of goods sold for a few recent transactions.

    I don't want to re-calculate the past inventory every time, but I don't see a way of present to start from the latest unused tier and work my way up. I also not sure if Lower and Upper Limit should account for the sold quantities.

    I also not sure I understand your total calculation. Should be to total cost of that quantity shipped be

    12500 * 0.158 + 7500 * .0214 + 0.0196 * (29.456 - 12500 - 7500) 

    ?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, July 6, 2012 4:02 PM
    Moderator
  • Okay, you are not understanding what I am doing here. I have Sales Order Header/Line Tables, Purchase Order Header/Line Tables, Adjustment Header/Line Tables, Shipping Header/LineTables, Customer Return Header/Line Tables, Vendor Return Header/Line Tables, Production Entry Header/Line Tables - in a combined query, I do the calculation to determine Quantity on Hand. These are separate tables and processes and have nothing to do with how I get FIFO Cost.

    In my program, whenever you have a process that adds to inventory with a cost, it enters the data in the appropriate table (PO table, Production Entry, etc.) and it also makes a line entry into the table described above (Inventory Costing Table). So in a separate table, I have running quantity totals of all part numbers, quantity received/produced, item cost, etc. This all happens before I query the database to retieve FIFO Cost for the current process.

    For example - a week ago, we recieve 10,000 units of part ABC for .0125. It enters the appropriate data into the PO Header Table, PO Line Table, and also the Inventory Costing Table (creating a cost tier). If there is a previous entry for that specific part number in the Inventory Costing Table, it retrieves the last Upper Limit, creates a New Lower Limit and creates a New Upper Limit. At that point I can determine costing for future transactions.

    Now, a week later we have a Shipment for 1,000 units of part number ABC. I need to know the FIFO Cost on these units. My program first determines the FIFO Cost before writing anything to the Shipment Header/Line tables. First it queries the Shipment Line Table for total quantity shipped of part number ABC. For example, maybe the total shipped to date is 7,000 (calculated by a SUM Query). So now I need to know the cost of the next piece shipped, so now I query the InventoryCostingTable to get the cost where TotalQuantityShippedToDate is between the Lower and Upper Limits. This is a simple example because there is only one entry in the InventoryCostingTable for this part, but it would return a cost of .0125. Now I know the FIFO Cost, and can continue with the Shipment Process which will determine the cost of the Shipment at 1,000 * .0125 = $12.50.

    Does this make more sense? I know it's hard to explain in a post. What you said about Upper and Lower not accounting for sold units - it does - this is the purpose of determining TotalQuantityShippedToDate, and then returning the cost from the appropriate tier. Looking at my table from the previous post, if the TotalQuantityShippedToDate is 10,000, then the FIFO Cost for the NEXT piece sold is .0158. If TotalQuantityShippedToDate is 25,000, then the FIFO Cost for the NEXT piece sold is .0196.

    Part#          Date                 Cost          QuantityProduced          LowerLimit          UpperLimit

    AA001        01/01/2011        .0158        12500                            0                         12500

    AA001        02/15/2011        .0214        7500                              12501                 20000

    AA001        03/15/2011        .0196        12125                            20001                 32125

    AA001        04/15/2011        .0211        6300                              32126                 38425

    If I recieve in this part from a Purchase Order, it will create a new cost tier in the table, where the LowerLimit is 38,426 and the new upper limit is 38,425 + Quantity Recieved from the PO. I hope this helps, but I know its hard to explain in a post.


    Todd Lerew

    Friday, July 6, 2012 5:40 PM
  • Todd,

    This is very helpful explanation, but I am also trying to see if I can apply it to my case where we don't calculate LowerLimit / UpperLimit (never did) and store all movements (with the cost for purchases, adjustments, transfers (which may be negative - say, from warehouse to Gift Shop and back to Warehouse) and without the cost for Sales (negative quantity) and Returns (positive quantity) all in one Inventory table.

    Now, I am afraid I can not ask now for changing the structure or introducing the new CostTiers table as the system is designed a long time ago.

    ------------------------------------------------

    In theory this can be done using a trigger on the Inventory table and that I probably can suggest, although this is also a complex process - 

    say, each client runs a job once to generate CostTiers table and doing forward the trigger on the Inventory table populates CostTiers - do you think it's feasible to suggest this approach?

    -----------------------------------------------------

    For now: 

    So, does it mean that every time when I need to find out the cost of, say, last month sales transactions (including returns), I will have to first calculate the tiers. The report I am trying to do is called Profit and Loss and I need to calculate cost of goods sold vs. sold amount (using the actual price).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, July 6, 2012 6:40 PM
    Moderator
  • How about (for your sample): say, the TotalQuantityShippedToDate is 12000 (falls in the first range) and we need to ship 2000. How you're calculating it? You know that your starting cost is 0.0158, but you have only 500 left with that cost.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, July 6, 2012 6:50 PM
    Moderator
  • I don't see how you can do it without doing the tiers. If your QuantityOnHand is 100,000 units, you may have purchased this product hundreds of times all at different costs. How else could you determine FIFO Cost without creating a Cost Tier Table?

    To your inquiry about determining the cost of last month's sales, I don't see any other way to do it if you want true FIFO Cost. You could determine the cost using Average Cost or Weighted Average Cost - if the cost doesnt flucuate that much, it may be okay - but it my case this wasn't an option because our products are manufactured from a commodity (steel), and month to month the price flucuates  a lot.

    Your other option without Cost Tiers is LIFO. Getting LastPurchaseCost is a simple query, but you could be under- or over-valuing your inventory depending if prices are rising or falling.


    Todd Lerew

    Friday, July 6, 2012 7:05 PM
  • Yes, you are correct - if your quantity shipped crosses into the next tier, you would have two different costs for the quantity shipped. My program does check that as well - I did not get into all that because it was already complicated enough trying to explain it in a post.


    Todd Lerew

    Friday, July 6, 2012 7:16 PM
  • Thanks again, Todd. We offer our clients two ways of calculating the inventory (weighted average - although we calculate it wrongly, I sent this morning a letter to my colleagues - perhaps I will get another footprint later to work on this more as well) and FIFO. Since we have clients with lots and lots of inventory movements, I am trying to figure out what is the correct and fastest algorithm of implementation.

    For the total cost of goods on hand we already have Dave Ballantyne algorithm implemented. 



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, July 6, 2012 7:44 PM
    Moderator
  • This is what I wrote, but not sure I am correct. If you think this is OK, I use this SP and also create another SP for SQL 2012 only version (which will be simpler). I then discuss with my colleague who is doing upgrade scripts a possibility of utilizing the SQL 2012 version.

    First of all, does this look correct? (I post sample data and then the implementation):

    DECLARE @dtStart DATETIME
    
    SET @dtStart = '20120627'
    
    IF OBJECT_ID('TempDB..#Inventory', N'U') IS NOT NULL
    	DROP TABLE #Inventory;
    
    CREATE TABLE [dbo].[#Inventory] (
    	[InvNo] [int] NOT NULL
    	,[ref_no] [numeric](17, 0) NOT NULL
    	,[locatn_id] [int] NOT NULL
    	,[date_time] [datetime] NOT NULL
    	,[fifo_rank] [bigint] NULL
    	,[department] [char](10) NOT NULL
    	,[category] [char](10) NOT NULL
    	,[item] [char](10) NOT NULL
    	,[invent_id] [int] NOT NULL
    	,[trans_type] [char](1) NOT NULL
    	,[quantity] [numeric](8, 2) NOT NULL
    	,[unit_cost] [money] NOT NULL
    	);
    
    WITH cte
    AS (
    	SELECT N'25' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-29 16:48:39.000' AS [date_time]
    		,N'1' AS [fifo_rank]
    		,N'1' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'BATT_TEST' AS [item]
    		,N'0' AS [invent_id]
    		,N'P' AS [trans_type]
    		,N'100.00' AS [quantity]
    		,N'1.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'133005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-29 17:00:13.000' AS [date_time]
    		,N'2' AS [fifo_rank]
    		,N'1' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'BATT_TEST' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'-90.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'25' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-29 17:26:47.000' AS [date_time]
    		,N'3' AS [fifo_rank]
    		,N'1' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'BATT_TEST' AS [item]
    		,N'0' AS [invent_id]
    		,N'P' AS [trans_type]
    		,N'100.00' AS [quantity]
    		,N'2.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'135005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-29 17:28:19.000' AS [date_time]
    		,N'4' AS [fifo_rank]
    		,N'1' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'BATT_TEST' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'10.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'0' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-27 11:58:26.000' AS [date_time]
    		,N'1' AS [fifo_rank]
    		,N'2' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'BUG_SPRAY' AS [item]
    		,N'0' AS [invent_id]
    		,N'T' AS [trans_type]
    		,N'10.00' AS [quantity]
    		,N'2.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'129005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-27 13:02:57.000' AS [date_time]
    		,N'2' AS [fifo_rank]
    		,N'2' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'BUG_SPRAY' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'-9.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'0' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-27 13:06:07.000' AS [date_time]
    		,N'3' AS [fifo_rank]
    		,N'2' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'BUG_SPRAY' AS [item]
    		,N'0' AS [invent_id]
    		,N'T' AS [trans_type]
    		,N'10.00' AS [quantity]
    		,N'2.6667' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'130005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-27 13:17:46.000' AS [date_time]
    		,N'4' AS [fifo_rank]
    		,N'2' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'BUG_SPRAY' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'-7.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'131005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-27 13:18:16.000' AS [date_time]
    		,N'5' AS [fifo_rank]
    		,N'2' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'BUG_SPRAY' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'3.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'24' AS [ref_no]
    		,N'3' AS [locatn_id]
    		,N'2012-06-27 11:57:17.000' AS [date_time]
    		,N'1' AS [fifo_rank]
    		,N'3' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'BUG_SPRAY' AS [item]
    		,N'0' AS [invent_id]
    		,N'P' AS [trans_type]
    		,N'20.00' AS [quantity]
    		,N'2.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'0' AS [ref_no]
    		,N'3' AS [locatn_id]
    		,N'2012-06-27 11:58:26.000' AS [date_time]
    		,N'2' AS [fifo_rank]
    		,N'3' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'BUG_SPRAY' AS [item]
    		,N'0' AS [invent_id]
    		,N'T' AS [trans_type]
    		,N'-10.00' AS [quantity]
    		,N'2.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'24' AS [ref_no]
    		,N'3' AS [locatn_id]
    		,N'2012-06-27 13:04:29.000' AS [date_time]
    		,N'3' AS [fifo_rank]
    		,N'3' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'BUG_SPRAY' AS [item]
    		,N'0' AS [invent_id]
    		,N'P' AS [trans_type]
    		,N'20.00' AS [quantity]
    		,N'3.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'0' AS [ref_no]
    		,N'3' AS [locatn_id]
    		,N'2012-06-27 13:06:07.000' AS [date_time]
    		,N'4' AS [fifo_rank]
    		,N'3' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'BUG_SPRAY' AS [item]
    		,N'0' AS [invent_id]
    		,N'T' AS [trans_type]
    		,N'-10.00' AS [quantity]
    		,N'2.6667' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'4' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2011-04-03 18:34:44.000' AS [date_time]
    		,N'1' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'T' AS [trans_type]
    		,N'24.00' AS [quantity]
    		,N'0.75' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'11005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2011-04-07 09:57:51.000' AS [date_time]
    		,N'2' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'-1.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'33005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2011-04-07 10:04:39.000' AS [date_time]
    		,N'3' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'-1.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'103005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2011-07-06 17:55:17.000' AS [date_time]
    		,N'4' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'-1.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'108005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2011-07-06 17:55:47.000' AS [date_time]
    		,N'5' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'-1.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'115005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2011-08-01 17:47:11.000' AS [date_time]
    		,N'6' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'-1.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'41005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2011-09-04 11:24:03.000' AS [date_time]
    		,N'7' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'-2.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'48005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2011-09-04 11:38:31.000' AS [date_time]
    		,N'8' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'-3.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'65005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2011-09-04 11:59:59.000' AS [date_time]
    		,N'9' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'-1.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'1' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-26 17:02:19.000' AS [date_time]
    		,N'10' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'A' AS [trans_type]
    		,N'5.00' AS [quantity]
    		,N'0.75' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'0' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-26 17:09:46.000' AS [date_time]
    		,N'11' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'A' AS [trans_type]
    		,N'5.00' AS [quantity]
    		,N'0.10' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'0' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-26 17:15:05.000' AS [date_time]
    		,N'12' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'T' AS [trans_type]
    		,N'5.00' AS [quantity]
    		,N'0.5469' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'0' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-26 17:15:47.000' AS [date_time]
    		,N'13' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'T' AS [trans_type]
    		,N'5.00' AS [quantity]
    		,N'0.5469' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'125005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-26 18:00:26.000' AS [date_time]
    		,N'14' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'-10.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'126005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-26 18:01:05.000' AS [date_time]
    		,N'15' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'5.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'127005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-26 18:02:07.000' AS [date_time]
    		,N'16' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'-50.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'128005001' AS [ref_no]
    		,N'1' AS [locatn_id]
    		,N'2012-06-26 18:02:51.000' AS [date_time]
    		,N'17' AS [fifo_rank]
    		,N'4' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'S' AS [trans_type]
    		,N'30.00' AS [quantity]
    		,N'0.00' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'5' AS [ref_no]
    		,N'3' AS [locatn_id]
    		,N'2011-04-03 16:41:21.000' AS [date_time]
    		,N'1' AS [fifo_rank]
    		,N'5' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'P' AS [trans_type]
    		,N'60.00' AS [quantity]
    		,N'0.75' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'1' AS [ref_no]
    		,N'3' AS [locatn_id]
    		,N'2011-04-03 17:46:45.000' AS [date_time]
    		,N'2' AS [fifo_rank]
    		,N'5' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'A' AS [trans_type]
    		,N'-2.00' AS [quantity]
    		,N'0.75' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'4' AS [ref_no]
    		,N'3' AS [locatn_id]
    		,N'2011-04-03 18:34:44.000' AS [date_time]
    		,N'3' AS [fifo_rank]
    		,N'5' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'T' AS [trans_type]
    		,N'-24.00' AS [quantity]
    		,N'0.75' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'23' AS [ref_no]
    		,N'3' AS [locatn_id]
    		,N'2012-06-26 17:00:58.000' AS [date_time]
    		,N'4' AS [fifo_rank]
    		,N'5' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'P' AS [trans_type]
    		,N'10.00' AS [quantity]
    		,N'0.75' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'23' AS [ref_no]
    		,N'3' AS [locatn_id]
    		,N'2012-06-26 17:04:59.000' AS [date_time]
    		,N'5' AS [fifo_rank]
    		,N'5' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'P' AS [trans_type]
    		,N'20.00' AS [quantity]
    		,N'0.10' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'0' AS [ref_no]
    		,N'3' AS [locatn_id]
    		,N'2012-06-26 17:15:05.000' AS [date_time]
    		,N'6' AS [fifo_rank]
    		,N'5' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'T' AS [trans_type]
    		,N'-5.00' AS [quantity]
    		,N'0.5469' AS [unit_cost]
    	
    	UNION ALL
    	
    	SELECT N'0' AS [ref_no]
    		,N'3' AS [locatn_id]
    		,N'2012-06-26 17:15:47.000' AS [date_time]
    		,N'7' AS [fifo_rank]
    		,N'5' AS [InvNo]
    		,N'RETAIL' AS [department]
    		,N'SUPPLIES' AS [category]
    		,N'GRANOLABAR' AS [item]
    		,N'0' AS [invent_id]
    		,N'T' AS [trans_type]
    		,N'-5.00' AS [quantity]
    		,N'0.5469' AS [unit_cost]
    	)
    INSERT #Inventory (
    	[ref_no]
    	,[locatn_id]
    	,[date_time]
    	,[fifo_rank]
    	,[InvNo]
    	,[department]
    	,[category]
    	,[item]
    	,[invent_id]
    	,[trans_type]
    	,[quantity]
    	,[unit_cost]
    	)
    SELECT [ref_no]
    	,[locatn_id]
    	,[date_time]
    	,[fifo_rank]
    	,[InvNo]
    	,[department]
    	,[category]
    	,[item]
    	,[invent_id]
    	,[trans_type]
    	,[quantity]
    	,[unit_cost]
    FROM cte
    
    -- I believe this index should be helpful
    CREATE CLUSTERED INDEX idx_Inventory_fifo_rank ON #Inventory (
    	InvNo
    	,fifo_rank
    	);
    
    
    /* Test SQL 2012 for Cost Tiers
    WITH cte
    AS (
    	SELECT I.ref_no AS trans_no
    		,I.date_time
    		,I.InvNo
    		,I.department
    		,I.category
    		,i.item
    		,i.invent_id
    		,i.locatn_id
    		,I.unit_cost
    		,I.trans_type
    		,I.quantity
    		,i.fifo_rank
    		,sum(quantity) OVER (
    			PARTITION BY InvNo ORDER BY fifo_rank ROWS BETWEEN UNBOUNDED Preceding
    					AND CURRENT row
    			) AS UnitsThru
    	FROM #Inventory I
    	WHERE I.trans_type IN (
    			'A'
    			,'P'
    			,'T'
    			)
    	)
    SELECT I.date_time
    	,I.InvNo
    	,I.department
    	,I.category
    	,i.item
    	,i.invent_id
    	,i.locatn_id
    	,I.unit_cost
    	,I.trans_type
    	,I.quantity
    	,i.fifo_rank
    	,UnitsThru - quantity + 1 AS UnitsFrom
    	,UnitsThru
    FROM cte I
    ORDER BY InvNo
    	,fifo_rank;
    */
    
    ;WITH CostTiers
    AS (
    	SELECT ii3.fifo_rank
    		,ii3.InvNo
    		,ii3.unit_cost
    		,SUM(ii4.quantity) - ii3.quantity + 1 AS units_from
    		,SUM(ii4.quantity) AS units_thru
    	FROM #Inventory ii3
    	JOIN #Inventory ii4 ON ii4.InvNo = ii3.InvNo
    	WHERE ii4.Fifo_Rank <= ii3.Fifo_Rank
    		AND ii3.quantity > 0
    		AND ii4.quantity > 0
    		AND ii3.trans_type IN (
    			'P'
    			,'A'
    			,'T'
    			)
    		AND ii4.trans_type IN (
    			'P'
    			,'A'
    			,'T'
    			)
    	GROUP BY ii3.InvNo
    		,ii3.fifo_rank
    		,ii3.quantity
    		,ii3.unit_cost
    	)
    	,Sales
    AS (
    	SELECT I.ref_no AS trans_no
    		,I.date_time AS Sale_Date
    		,I.InvNo
    		,I.department
    		,I.category
    		,I.item
    		,I.invent_id
    		,I.locatn_id
    		,I.unit_cost
    		,I.trans_type
    		,I.fifo_rank
    		,0 - I.quantity AS Sold -- Returned
    		,(0 - SUM(I2.quantity)) + I.quantity AS RemovedPrior
    	FROM #Inventory I
    	JOIN #Inventory I2 ON I2.InvNo = I.InvNo
    	WHERE I2.Fifo_Rank <= I.Fifo_Rank
    		AND I.trans_type = 'S'
    		AND (
    			I2.trans_type = 'S'
    			OR I2.quantity < 0
    			)
    		AND I.date_time >= @dtStart
    	GROUP BY I.ref_no
    		,I.date_time
    		,I.InvNo
    		,I.department
    		,I.category
    		,I.item
    		,I.invent_id
    		,I.locatn_id
    		,I.unit_cost
    		,I.trans_type
    		,I.fifo_rank
    		,I.quantity
    	)
    	,SalesWithCost
    AS (
    	SELECT S.*
    		,S.Sold * CT.unit_cost AS CostOfGoodsSold
    		,CT.units_from
    		,CT.units_thru
    	FROM Sales S
    	LEFT JOIN CostTiers CT ON S.InvNo = CT.InvNo
    		AND (S.RemovedPrior + S.Sold) BETWEEN CT.units_from
    			AND CT.units_thru
    		AND S.fifo_rank > CT.fifo_rank
    	)
    SELECT trans_no
    	,department
    	,category
    	,item
    	,invent_id
    	,locatn_id
    	,cast(Sold AS INT) AS QuantitySold
    	,CAST(CostOfGoodsSold AS MONEY) AS CostOfGoodsSold
    FROM SalesWithCost
    WHERE CostOfGoodsSold IS NOT NULL
    
    UNION ALL
    
    SELECT SW.trans_no
    	,SW.department
    	,SW.category
    	,SW.item
    	,SW.invent_id
    	,SW.locatn_id
    	,CAST(SW.Sold AS INT) AS QuantitySold
    	,CAST(COALESCE(SW.Sold * LastCost.LastCost, 0) AS MONEY) AS CostOfGoodsSold
    FROM SalesWithCost SW
    OUTER APPLY dbo.siriusfn_LastCostUpToDate(SW.department, SW.category, SW.item, SW.invent_id, SW.Sale_Date) LastCost
    WHERE CostOfGoodsSold IS NULL
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, July 6, 2012 10:16 PM
    Moderator
  • Hi Todd,

    Can you explain what happens when the quantity shipped crosses several tiers?

    Also, how do you ensure that no other process except your procedure touches this InventoryCosts table? You need to make sure that no one else adding or removing rows from that table.

    --------------

    Also, if you discontinue a part (e.g. part is deleted from the original Items table), do you remove all related rows from the InventoryCosts also?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, July 9, 2012 3:28 PM
    Moderator
  • Hello Todd,

    we have solved the problem using SSIS. The solution is easy, intuitive for the end user.

    you could contact myself at apoorv@mnnbi.com if interested.

    Monday, September 2, 2013 5:25 PM
  • Dear Sir,

    As much I have studied, this code is amazing.

    But I didn't understand how to implement the same if Quantity Issued Multiple Times In Parrelel Issued Rows ?

    Kindly help me providing the Modified Query for following Issues as follows:

    1st Issued:

    @Part CHAR(5) = 'AA001', @Date DATETIME = '01/13/2010', @QuantityShipped INT = 13000

    2nd Issued:

    @Part CHAR(5) = 'AA001', @Date DATETIME = '03/15/2011', @QuantityShipped INT = 13250

    3rd Issued:

    @Part CHAR(5) = 'AA001', @Date DATETIME = '04/15/2011', @QuantityShipped INT = 50050

    Please advise the same in the format like below:

    Part,	Type,   Date,	      Cost,	QuantityProduced

    'AA001', INN '12/12/2010', 0.0200, 5000,

    'AA001',  OUT   '01/13/2010',   ?,              13250,

    'AA001', INN '01/01/2011', 0.0158, 12500, 'AA001', INN '02/15/2011', 0.0214, 7500, 'AA001', INN '03/15/2011', 0.0196, 12125,

    'AA001', OUT   '03/15/2011',    ?,         13250,

    'AA001', INN '04/15/2011', 0.0211, 6300,

    'AA001', OUT   '04/15/2011',   ?,         50050,

    'AA002', INN '04/16/2011', 0.0210, 5000

    Regards-

    Sanjeeb

    Sunday, February 16, 2014 9:55 AM