Logo

SQL Server FIFOdet Function

Updated: 13 Jul 2023

Description

Use the SQL Server table-valued function FIFOdet to calculate FIFO (First In, First Out) values in an ordered resultant table. FIFOdet calculates balances for each value from the first value to the last value in the ordered group or partition. FIFOdet returns:

  • the transaction identifier
  • output row number
  • transaction quantity
  • transaction unit price
  • transaction extended price
  • inventory on hand including the current transaction
  • inventory cost including the current transaction
  • cost of goods sold
  • gross margin on sale
  • the transaction identifier of the purchase from which the sale transaction was filled
  • the quantity from the purchase transaction identifier
  • the unit price from the purchase transaction identifier

FIFOdet assumes that the quantity (i.e., the number of units) of the transaction and the monetary value of the transaction have the same sign. Purchase, or additions to inventory, are positive and sales, or withdrawals from inventory, are negative.

FIFOdet will match the first sale transaction with the first purchase transaction. If the sale quantity is less than the purchased quantity, then the remaining balance on the purchase transaction will be applied to the next sale transaction. If the sale quantity is greater than the purchased quantity then FIFOdet will get the next purchase transaction, repeating this process until the remaining balance on the sales transaction is zero or there is no more inventory.

In the event that amount sold is greater than the amount purchased, then the inventory on hand will continue to be updated, but the inventory cost will be zero.

Syntax

SELECT * FROM [wct].[FIFOdet] (
   <@DataQuery, nvarchar(max),>)

Arguments

@DataQuery

An SQL statement which returns a resultant table containing the unique transaction identifier, quantity and amount.

Return Type

RETURNS TABLE (
   [ID] [sql_variant] NULL,
   [RN] [int] NULL,
   [QTY] [float] NULL,
   [UNITPRICE] [float] NULL,
   [EXPRICE] [float] NULL,
   [INVONHAND] [float] NULL,
   [INVCOST] [float] NULL,
   [COGS] [float] NULL,
   [GM] [float] NULL,
   [PURID] [sql_variant] NULL,
   [PURQTY] [float] NULL,
   [PURPRICE] [float] NULL
)
ColumnDescription
IDunique transaction identifier
RNoutput row number
QTYtransaction quantity
UNITPRICEtransaction unit price
EXPRICEtransaction extended price
INVONHANDthe sum of QTY for the current and all preceding rows
INVCOSTthe inventory cost from the preceding row plus the costs of good sold from the current row
COGSthe cost of goods sold for the current row
GMthe gross margin on sales for the current row
PURIDthe transaction identifier of the purchase transaction used in the calculation of the cost of goods sold
PURQTYthe quantity used from purchase transaction
PURPRICEthe unit price from the purchase transaction

Remarks

  • For purchase transactions i.e., where QTY > 0, COGS, GM, PURID, PURQTY and PURPRICE will be NULL.
  • If INVONHAND <= 0 then INVCOST = 0.
  • The number of rows returned by the function may be greater than the number of input rows. The RN column in the resultant table should be used in your SQL to return the output rows in the order in which they were processed by the function.

Examples

Example #1

This is a very simple example showing the movements for one product over the month of June. Since it only involves one product, there is no product identifier in the SQL. The input had been put in date order and then in quantity descending order within date.

SET NOCOUNT ON;

DROP TABLE IF EXISTS #t; --SQL Server 2016 and above
SELECT *
INTO #t
FROM
(
    VALUES
        ('20230615001', '2023-06-15', -411, 11.86)
      , ('20230625002', '2023-06-25', -921, 11.9)
      , ('20230628003', '2023-06-28', -178, 11.77)
      , ('20230622004', '2023-06-22', 742, 10.75)
      , ('20230629005', '2023-06-29', -537, 11.91)
      , ('20230627006', '2023-06-27', 639, 10.59)
      , ('20230606007', '2023-06-06', 199, 10.71)
      , ('20230607008', '2023-06-07', -215, 11.9)
      , ('20230623009', '2023-06-23', -540, 11.95)
      , ('20230615010', '2023-06-15', 699, 10.64)
      , ('20230630011', '2023-06-30', 478, 10.64)
      , ('20230605012', '2023-06-05', 658, 10.65)
      , ('20230612013', '2023-06-12', -974, 11.87)
      , ('20230603014', '2023-06-03', -704, 11.96)
      , ('20230603015', '2023-06-03', -497, 11.89)
      , ('20230606016', '2023-06-06', 231, 10.52)
      , ('20230627017', '2023-06-27', 96, 10.73)
      , ('20230604018', '2023-06-04', 860, 10.51)
      , ('20230624019', '2023-06-24', -323, 11.94)
      , ('20230606020', '2023-06-06', 762, 10.7)
      , ('20230624021', '2023-06-24', -211, 11.8)
      , ('20230628022', '2023-06-28', -195, 11.8)
      , ('20230617023', '2023-06-17', 480, 10.75)
      , ('20230629024', '2023-06-29', -950, 12)
      , ('20230621025', '2023-06-21', 703, 10.72)
      , ('20230629026', '2023-06-29', 551, 10.7)
      , ('20230628027', '2023-06-28', -631, 11.81)
      , ('20230608028', '2023-06-08', 448, 10.7)
      , ('20230613029', '2023-06-13', -909, 11.97)
      , ('20230621030', '2023-06-21', 762, 10.56)
) n (ordno, orddate, qty, price)

SELECT CAST(k.ID as CHAR(13))    as ID
     , t.orddate                 as orddate
     , ROUND(k.QTY, 4)           as QTY
     , ROUND(k.UNITPRICE, 4)     as UNITPRICE
     , ROUND(k.EXPRICE, 4)       as EXPRICE
     , ROUND(k.INVONHAND, 4)     as INVONHAND
     , ROUND(k.INVCOST, 4)       as INVCOST
     , ROUND(k.COGS, 4)          as COGS
     , ROUND(k.GM, 4)            as GM
     , CAST(k.PURID as CHAR(13)) as PURID
     , ROUND(k.PURQTY, 4)        as PURQTY
     , ROUND(k.PURPRICE, 4)      as PURPRICE
FROM wct.FIFOdet('SELECT ordno, qty, qty * price FROM #t ORDER BY ORDDATE ASC, qty DESC') k
    INNER JOIN #t t                                                                         
        ON k.ID = t.ordno
ORDER BY k.RN

This produces the following result.

IDorddateQTYUNITPRICEEXPRICEINVONHANDINVCOSTCOGSGMPURIDPURQTYPURPRICE
20230603015 2023-06-03-49711.89-5909.33-4970-5223.47685.8620230604018 49710.51
20230603014 2023-06-03-36311.96-4341.48-8600-3815.13526.3520230604018 36310.51
20230603014 2023-06-03-34111.96-4078.36-12010-3631.65446.7120230605012 34110.65
20230604018 2023-06-0486010.519038.6-3410
20230605012 2023-06-0565810.657007.73173376.05
20230606020 2023-06-0676210.78153.4107911529.45
20230606016 2023-06-0623110.522430.12131013959.57
20230606007 2023-06-0619910.712131.29150916090.86
20230607008 2023-06-07-21511.9-2558.5129413801.11-2289.75268.7520230605012 21510.65
20230608028 2023-06-0844810.74793.6174218594.71
20230612013 2023-06-12-10211.87-1210.74164017508.41-1086.3124.4420230605012 10210.65
20230612013 2023-06-12-76211.87-9044.948789355.01-8153.4891.5420230606020 76210.7
20230612013 2023-06-12-11011.87-1305.77688197.81-1157.2148.520230606016 11010.52
20230613029 2023-06-13-12111.97-1448.376476924.89-1272.92175.4520230606016 12110.52
20230613029 2023-06-13-19911.97-2382.034484793.6-2131.29250.7420230606007 19910.71
20230613029 2023-06-13-44811.97-5362.5600-4793.6568.9620230608028 44810.7
20230613029 2023-06-13-14111.97-1687.77-1410-1500.24187.5320230615010 14110.64
20230615010 2023-06-1569910.647437.365585937.12
20230615001 2023-06-15-41111.86-4874.461471564.08-4373.04501.4220230615010 41110.64
20230617023 2023-06-1748010.7551606276724.08
20230621030 2023-06-2176210.568046.72138914770.8
20230621025 2023-06-2170310.727536.16209222306.96
20230622004 2023-06-2274210.757976.5283430283.46
20230623009 2023-06-23-14711.95-1756.65268728719.38-1564.08192.5720230615010 14710.64
20230623009 2023-06-23-39311.95-4696.35229424494.63-4224.75471.620230617023 39310.75
20230624021 2023-06-24-8711.8-1026.6220723559.38-935.2591.3520230617023 8710.75
20230624021 2023-06-24-12411.8-1463.2208322249.94-1309.44153.7620230621030 12410.56
20230624019 2023-06-24-32311.94-3856.62176018839.06-3410.88445.7420230621030 32310.56
20230625002 2023-06-25-31511.9-3748.5144515512.66-3326.4422.120230621030 31510.56
20230625002 2023-06-25-60611.9-7211.48399016.34-6496.32715.0820230621025 60610.72
20230627006 2023-06-2763910.596767.01147815783.35
20230627017 2023-06-279610.731030.08157416813.43
20230628003 2023-06-28-9711.77-1141.69147715773.59-1039.84101.8520230621025 9710.72
20230628003 2023-06-28-8111.77-953.37139614902.84-870.7582.6220230622004 8110.75
20230628022 2023-06-28-19511.8-2301120112806.59-2096.25204.7520230622004 19510.75
20230628027 2023-06-28-46611.81-5503.467357797.09-5009.5493.9620230622004 46610.75
20230628027 2023-06-28-16511.81-1948.655706049.74-1747.35201.320230627006 16510.59
20230629026 2023-06-2955110.75895.7112111945.44
20230629005 2023-06-29-47411.91-5645.346476925.78-5019.66625.6820230627006 47410.59
20230629005 2023-06-29-6311.91-750.335846249.79-675.9974.3420230627017 6310.73
20230629024 2023-06-29-3312-3965515895.7-354.0941.9120230627017 3310.73
20230629024 2023-06-29-55112-661200-5895.7716.320230629026 55110.7
20230629024 2023-06-29-36612-4392-3660-3894.24497.7620230630011 36610.64
20230630011 2023-06-3047810.645085.921121191.68

Note that there were 30 input rows and 44 output rows. You should also make note of the fact that the first purchase transaction occurs after the first sale transaction.

Example #2

In this example, we calculate the FIFO balances for multiple products. Doing so requires the use of a CROSS APPLY. In addition to the CROSS APPLY we JOIN to the source table to include the product identifier (and any other data that might be of interest) in the resultant table.

SET NOCOUNT ON;

DROP TABLE IF EXISTS #t; --SQL Server 2016 and above

SELECT *
INTO #t
FROM
(
    VALUES
        ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230601001', '2023-06-01', -791, 11.81)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230619002', '2023-06-19', -914, 11.8)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230603003', '2023-06-03', -728, 11.8)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230612004', '2023-06-12', -792, 11.93)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230611005', '2023-06-11', -926, 11.85)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230614006', '2023-06-14', 295, 10.75)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230619007', '2023-06-19', -13, 11.86)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230604008', '2023-06-04', 435, 10.55)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230616009', '2023-06-16', 314, 10.72)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230605010', '2023-06-05', 72, 10.62)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230614011', '2023-06-14', 271, 10.72)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230610012', '2023-06-10', 486, 10.51)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230608013', '2023-06-08', 394, 10.56)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230630014', '2023-06-30', 634, 10.71)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230616015', '2023-06-16', -201, 11.92)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230622016', '2023-06-22', 646, 10.75)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230601017', '2023-06-01', 139, 10.62)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230626018', '2023-06-26', -892, 11.77)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230604019', '2023-06-04', 925, 10.56)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230621020', '2023-06-21', 87, 10.74)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230625021', '2023-06-25', -560, 12)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230601022', '2023-06-01', -124, 11.99)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230601023', '2023-06-01', -760, 11.86)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230619024', '2023-06-19', 664, 10.56)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230602025', '2023-06-02', -527, 11.78)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230623026', '2023-06-23', -749, 11.79)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230617027', '2023-06-17', 724, 10.72)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230629028', '2023-06-29', -691, 11.86)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230608029', '2023-06-08', 124, 10.59)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230613030', '2023-06-13', -970, 11.86)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230615032', '2023-06-15', -225, 119.08)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230627033', '2023-06-27', 453, 107.14)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230625034', '2023-06-25', -475, 118.07)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230613035', '2023-06-13', -443, 118.19)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230620036', '2023-06-20', -480, 118.63)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230618037', '2023-06-18', -352, 118.84)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230603038', '2023-06-03', 345, 106.01)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230623039', '2023-06-23', -126, 118.62)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230610040', '2023-06-10', 317, 107.23)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230603041', '2023-06-03', 265, 105.35)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230615042', '2023-06-15', -278, 119.92)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230623043', '2023-06-23', 44, 105.1)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230619044', '2023-06-19', 429, 105.14)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230614045', '2023-06-14', -419, 119.83)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230606046', '2023-06-06', 100, 107.35)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230615047', '2023-06-15', 358, 105.51)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230601048', '2023-06-01', 351, 106.45)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230624049', '2023-06-24', -26, 119.94)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230615050', '2023-06-15', 25, 106.49)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230629051', '2023-06-29', 352, 106.48)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230611052', '2023-06-11', 238, 106.6)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230630053', '2023-06-30', 144, 107.36)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230602054', '2023-06-02', 316, 105.77)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230606055', '2023-06-06', 48, 107.02)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230624056', '2023-06-24', 398, 106.14)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230627057', '2023-06-27', 279, 106.63)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230616058', '2023-06-16', -427, 117.55)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230624059', '2023-06-24', 375, 106.75)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230630060', '2023-06-30', 17, 105.13)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230601061', '2023-06-01', -92, 119.49)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230630063', '2023-06-30', -122, 10.53)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230604064', '2023-06-04', 153, 11.86)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230629065', '2023-06-29', 183, 12)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230620066', '2023-06-20', -144, 10.65)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230607067', '2023-06-07', -36, 10.51)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230603068', '2023-06-03', 70, 11.93)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230616069', '2023-06-16', -136, 10.73)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230608070', '2023-06-08', 171, 11.86)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230608071', '2023-06-08', 166, 11.75)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230629072', '2023-06-29', 0, 10.74)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230622073', '2023-06-22', 79, 11.86)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230605074', '2023-06-05', -115, 10.52)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230617075', '2023-06-17', -112, 10.58)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230606076', '2023-06-06', 182, 11.93)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230630077', '2023-06-30', 29, 11.99)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230602078', '2023-06-02', 6, 11.78)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230620079', '2023-06-20', -51, 10.51)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230619080', '2023-06-19', -107, 10.57)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230618081', '2023-06-18', -110, 10.66)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230629082', '2023-06-29', 198, 11.84)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230607083', '2023-06-07', -48, 10.61)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230627084', '2023-06-27', -118, 10.56)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230612085', '2023-06-12', 35, 11.87)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230622086', '2023-06-22', 76, 11.93)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230617087', '2023-06-17', -63, 10.58)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230621088', '2023-06-21', 33, 12)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230605089', '2023-06-05', -23, 10.71)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230617090', '2023-06-17', 19, 11.9)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230622091', '2023-06-22', 121, 11.84)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230629092', '2023-06-29', 172, 11.96)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230624094', '2023-06-24', -41, 106.62)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230622095', '2023-06-22', -19, 107.12)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230619096', '2023-06-19', 23, 119.33)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230630097', '2023-06-30', 62, 119.9)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230618098', '2023-06-18', 52, 119.01)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230618099', '2023-06-18', -70, 105.3)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230607100', '2023-06-07', 47, 118.46)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230613101', '2023-06-13', -13, 105.71)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230622102', '2023-06-22', -99, 105.99)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230608103', '2023-06-08', 8, 118.3)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230619104', '2023-06-19', 22, 118.98)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230629105', '2023-06-29', 88, 118.06)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230616106', '2023-06-16', -8, 106.11)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230612107', '2023-06-12', 92, 117.63)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230607108', '2023-06-07', -37, 106.05)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230603109', '2023-06-03', 85, 118.03)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230605110', '2023-06-05', 41, 119.04)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230628111', '2023-06-28', -22, 107.41)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230617112', '2023-06-17', -91, 105.54)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230605113', '2023-06-05', -20, 106.43)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230612114', '2023-06-12', -39, 107.2)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230629115', '2023-06-29', 37, 117.58)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230605116', '2023-06-05', 15, 117.66)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230630117', '2023-06-30', 43, 117.65)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230611118', '2023-06-11', -33, 106.14)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230624119', '2023-06-24', -47, 105.1)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230614120', '2023-06-14', -17, 105.41)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230602121', '2023-06-02', -93, 107.36)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230621122', '2023-06-21', -90, 105.91)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230612123', '2023-06-12', 18, 119.58)
) n (id_item, ordno, orddate, qty, price);

SELECT a.id_item
     , CAST(k.ID as CHAR(36))    as ID
     , t.orddate                 as orddate
     , ROUND(k.QTY, 4)           as QTY
     , ROUND(k.UNITPRICE, 4)     as UNITPRICE
     , ROUND(k.EXPRICE, 4)       as EXPRICE
     , ROUND(k.INVONHAND, 4)     as INVONHAND
     , ROUND(k.INVCOST, 4)       as INVCOST
     , ROUND(k.COGS, 4)          as COGS
     , ROUND(k.GM, 4)            as GM
     , CAST(k.PURID as CHAR(36)) as PURID
     , ROUND(k.PURQTY, 4)        as PURQTY
     , ROUND(k.PURPRICE, 4)      as PURPRICE
FROM
(SELECT DISTINCT id_item FROM #t) a
    CROSS APPLY wct.FIFOdet('SELECT ordno, qty, qty * price FROM #t WHERE id_item = ''' + a.id_item
                            + ''' ORDER BY ORDDATE ASC, qty DESC'
                           )      k
    INNER JOIN #t t
        ON k.ID = t.ordno
ORDER BY a.id_item
       , k.RN
IDorddateQTYUNITPRICEEXPRICEINVONHANDINVCOSTCOGSGMPURIDPURQTYPURPRICE

Example #3

In this example, there is a separate table (#i) for all the inventory items and a table for the transactions (#t). This SQL will calculate the FIFO values without having to get the distinct product identifiers from the transaction table.

SET NOCOUNT ON;

DROP TABLE IF EXISTS #t; --SQL Server 2016 and above
DROP TABLE IF EXISTS #i; --SQL Server 2016 and above

SELECT *
INTO #i
FROM
(
    VALUES
        ('24202201-1770-0D9D-3543-CD78799A9415')
      , ('5FCDA993-9C22-5B41-95DD-58993D627827')
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55')
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF')
      , ('C85837C2-69EF-5A55-0BF3-8D48269B237D')
      , ('388E9D84-6256-0E51-8DA1-0923AA739232')
      , ('4C54AC48-2109-3E57-2D65-EA43A2B64FFE')
      , ('BF97105A-35FA-7E42-6F30-A3C908A12203')
) n (id)
SELECT *
INTO #t
FROM
(
    VALUES
        ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230601001', '2023-06-01', -791, 11.81)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230619002', '2023-06-19', -914, 11.8)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230603003', '2023-06-03', -728, 11.8)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230612004', '2023-06-12', -792, 11.93)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230611005', '2023-06-11', -926, 11.85)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230614006', '2023-06-14', 295, 10.75)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230619007', '2023-06-19', -13, 11.86)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230604008', '2023-06-04', 435, 10.55)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230616009', '2023-06-16', 314, 10.72)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230605010', '2023-06-05', 72, 10.62)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230614011', '2023-06-14', 271, 10.72)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230610012', '2023-06-10', 486, 10.51)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230608013', '2023-06-08', 394, 10.56)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230630014', '2023-06-30', 634, 10.71)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230616015', '2023-06-16', -201, 11.92)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230622016', '2023-06-22', 646, 10.75)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230601017', '2023-06-01', 139, 10.62)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230626018', '2023-06-26', -892, 11.77)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230604019', '2023-06-04', 925, 10.56)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230621020', '2023-06-21', 87, 10.74)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230625021', '2023-06-25', -560, 12)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230601022', '2023-06-01', -124, 11.99)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230601023', '2023-06-01', -760, 11.86)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230619024', '2023-06-19', 664, 10.56)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230602025', '2023-06-02', -527, 11.78)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230623026', '2023-06-23', -749, 11.79)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230617027', '2023-06-17', 724, 10.72)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230629028', '2023-06-29', -691, 11.86)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230608029', '2023-06-08', 124, 10.59)
      , ('5FCDA993-9C22-5B41-95DD-58993D627827', '20230613030', '2023-06-13', -970, 11.86)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230615032', '2023-06-15', -225, 119.08)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230627033', '2023-06-27', 453, 107.14)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230625034', '2023-06-25', -475, 118.07)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230613035', '2023-06-13', -443, 118.19)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230620036', '2023-06-20', -480, 118.63)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230618037', '2023-06-18', -352, 118.84)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230603038', '2023-06-03', 345, 106.01)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230623039', '2023-06-23', -126, 118.62)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230610040', '2023-06-10', 317, 107.23)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230603041', '2023-06-03', 265, 105.35)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230615042', '2023-06-15', -278, 119.92)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230623043', '2023-06-23', 44, 105.1)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230619044', '2023-06-19', 429, 105.14)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230614045', '2023-06-14', -419, 119.83)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230606046', '2023-06-06', 100, 107.35)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230615047', '2023-06-15', 358, 105.51)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230601048', '2023-06-01', 351, 106.45)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230624049', '2023-06-24', -26, 119.94)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230615050', '2023-06-15', 25, 106.49)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230629051', '2023-06-29', 352, 106.48)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230611052', '2023-06-11', 238, 106.6)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230630053', '2023-06-30', 144, 107.36)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230602054', '2023-06-02', 316, 105.77)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230606055', '2023-06-06', 48, 107.02)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230624056', '2023-06-24', 398, 106.14)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230627057', '2023-06-27', 279, 106.63)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230616058', '2023-06-16', -427, 117.55)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230624059', '2023-06-24', 375, 106.75)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230630060', '2023-06-30', 17, 105.13)
      , ('E412D80E-6993-43D8-38BB-E61EAFD158FF', '20230601061', '2023-06-01', -92, 119.49)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230630063', '2023-06-30', -122, 10.53)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230604064', '2023-06-04', 153, 11.86)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230629065', '2023-06-29', 183, 12)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230620066', '2023-06-20', -144, 10.65)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230607067', '2023-06-07', -36, 10.51)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230603068', '2023-06-03', 70, 11.93)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230616069', '2023-06-16', -136, 10.73)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230608070', '2023-06-08', 171, 11.86)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230608071', '2023-06-08', 166, 11.75)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230629072', '2023-06-29', 0, 10.74)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230622073', '2023-06-22', 79, 11.86)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230605074', '2023-06-05', -115, 10.52)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230617075', '2023-06-17', -112, 10.58)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230606076', '2023-06-06', 182, 11.93)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230630077', '2023-06-30', 29, 11.99)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230602078', '2023-06-02', 6, 11.78)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230620079', '2023-06-20', -51, 10.51)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230619080', '2023-06-19', -107, 10.57)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230618081', '2023-06-18', -110, 10.66)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230629082', '2023-06-29', 198, 11.84)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230607083', '2023-06-07', -48, 10.61)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230627084', '2023-06-27', -118, 10.56)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230612085', '2023-06-12', 35, 11.87)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230622086', '2023-06-22', 76, 11.93)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230617087', '2023-06-17', -63, 10.58)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230621088', '2023-06-21', 33, 12)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230605089', '2023-06-05', -23, 10.71)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230617090', '2023-06-17', 19, 11.9)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230622091', '2023-06-22', 121, 11.84)
      , ('E3FDD639-540F-0CE1-4C04-ED48E7F44D55', '20230629092', '2023-06-29', 172, 11.96)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230624094', '2023-06-24', -41, 106.62)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230622095', '2023-06-22', -19, 107.12)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230619096', '2023-06-19', 23, 119.33)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230630097', '2023-06-30', 62, 119.9)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230618098', '2023-06-18', 52, 119.01)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230618099', '2023-06-18', -70, 105.3)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230607100', '2023-06-07', 47, 118.46)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230613101', '2023-06-13', -13, 105.71)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230622102', '2023-06-22', -99, 105.99)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230608103', '2023-06-08', 8, 118.3)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230619104', '2023-06-19', 22, 118.98)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230629105', '2023-06-29', 88, 118.06)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230616106', '2023-06-16', -8, 106.11)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230612107', '2023-06-12', 92, 117.63)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230607108', '2023-06-07', -37, 106.05)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230603109', '2023-06-03', 85, 118.03)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230605110', '2023-06-05', 41, 119.04)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230628111', '2023-06-28', -22, 107.41)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230617112', '2023-06-17', -91, 105.54)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230605113', '2023-06-05', -20, 106.43)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230612114', '2023-06-12', -39, 107.2)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230629115', '2023-06-29', 37, 117.58)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230605116', '2023-06-05', 15, 117.66)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230630117', '2023-06-30', 43, 117.65)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230611118', '2023-06-11', -33, 106.14)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230624119', '2023-06-24', -47, 105.1)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230614120', '2023-06-14', -17, 105.41)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230602121', '2023-06-02', -93, 107.36)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230621122', '2023-06-21', -90, 105.91)
      , ('24202201-1770-0D9D-3543-CD78799A9415', '20230612123', '2023-06-12', 18, 119.58)
) n (id_item, ordno, orddate, qty, price)
SELECT a.id_item
     , CAST(k.ID as CHAR(11))    as ID
     , t.orddate                 as orddate
     , ROUND(k.QTY, 4)           as QTY
     , ROUND(k.UNITPRICE, 4)     as UNITPRICE
     , ROUND(k.EXPRICE, 4)       as EXPRICE
     , ROUND(k.INVONHAND, 4)     as INVONHAND
     , ROUND(k.INVCOST, 4)       as INVCOST
     , ROUND(k.COGS, 4)          as COGS
     , ROUND(k.GM, 4)            as GM
     , CAST(k.PURID as CHAR(36)) as PURID
     , ROUND(k.PURQTY, 4)        as PURQTY
     , ROUND(k.PURPRICE, 4)      as PURPRICE
FROM
(SELECT id as id_item FROM #i) a
    CROSS APPLY wct.FIFOdet('SELECT ordno, qty, qty * price FROM #t WHERE id_item = ''' + a.id_item
                            + ''' ORDER BY ORDDATE ASC, qty DESC'
                           )   k
    INNER JOIN #t t
        ON k.ID = t.ordno
ORDER BY a.id_item
       , k.RN

This produces the following result.

IDorddateQTYUNITPRICEEXPRICEINVONHANDINVCOSTCOGSGMPURIDPURQTYPURPRICE
202306021212023-06-02-85107.36-9125.6-850-10032.55-906.9520230603109 85118.03
202306021212023-06-02-8107.36-858.88-930-952.32-93.4420230605110 8119.04
202306031092023-06-0385118.0310032.55-80
202306051102023-06-0541119.044880.64333928.32
202306051162023-06-0515117.661764.9485693.22
202306051132023-06-05-20106.43-2128.6283312.42-2380.8-252.220230605110 20119.04
202306071002023-06-0747118.465567.62758880.04
202306071082023-06-07-13106.05-1378.65627332.52-1547.52-168.8720230605110 13119.04
202306071082023-06-07-15106.05-1590.75475567.62-1764.9-174.1520230605116 15117.66
202306071082023-06-07-9106.05-954.45384501.48-1066.14-111.6920230607100 9118.46
202306081032023-06-088118.3946.4465447.88
202306111182023-06-11-33106.14-3502.62131538.7-3909.18-406.5620230607100 33118.46
202306121072023-06-1292117.6310821.9610512360.66
202306121232023-06-1218119.582152.4412314513.1
202306121142023-06-12-5107.2-53611813920.8-592.3-56.320230607100 5118.46
202306121142023-06-12-8107.2-857.611012974.4-946.4-88.820230608103 8118.3
202306121142023-06-12-26107.2-2787.2849916.02-3058.38-271.1820230612107 26117.63
202306131012023-06-13-13105.71-1374.23718386.83-1529.19-154.9620230612107 13117.63
202306141202023-06-14-17105.41-1791.97546387.12-1999.71-207.7420230612107 17117.63
202306161062023-06-16-8106.11-848.88465446.08-941.04-92.1620230612107 8117.63
202306171122023-06-17-28105.54-2955.12182152.44-3293.64-338.5220230612107 28117.63
202306171122023-06-17-18105.54-1899.7200-2152.44-252.7220230612123 18119.58
202306171122023-06-17-45105.54-4749.3-450-5355.45-606.1520230618098 45119.01
202306180982023-06-1852119.016188.527833.07
202306180992023-06-18-7105.3-737.100-833.07-95.9720230618098 7119.01
202306180992023-06-18-23105.3-2421.9-230-2744.59-322.6920230619096 23119.33
202306180992023-06-18-22105.3-2316.6-450-2617.56-300.9620230619104 22118.98
202306180992023-06-18-18105.3-1895.4-630-2125.08-229.6820230629105 18118.06
202306190962023-06-1923119.332744.59-400
202306191042023-06-1922118.982617.56-180
202306211222023-06-21-70105.91-7413.7-880-8264.2-850.520230629105 70118.06
202306211222023-06-21-20105.91-2118.2-1080-2351.6-233.420230629115 20117.58
202306220952023-06-22-17107.12-1821.04-1250-1998.86-177.8220230629115 17117.58
202306220952023-06-22-2107.12-214.24-1270-239.8-25.5620230630097 2119.9
202306221022023-06-22-60105.99-6359.4-1870-7194-834.620230630097 60119.9
202306221022023-06-22-39105.99-4133.61-2260-4588.35-454.7420230630117 39117.65
202306240942023-06-24-4106.62-426.48-2300-470.6-44.1220230630117 4117.65
202306240942023-06-24-37106.62-3944.94-2670
202306241192023-06-24-47105.1-4939.7-3140
202306281112023-06-28-22107.41-2363.02-3360
202306291052023-06-2988118.0610389.28-2480
202306291152023-06-2937117.584350.46-2110
202306300972023-06-3062119.97433.8-1490
202306301172023-06-3043117.655058.95-1060
202306010172023-06-0113910.621476.181391476.18
202306010222023-06-01-12411.99-1486.7615159.3-1316.88169.8820230601017 12410.62
202306010232023-06-01-1511.86-177.900-159.318.620230601017 1510.62
202306010232023-06-01-74511.86-8835.7-7450-7867.2968.520230604019 74510.56
202306010012023-06-01-18011.81-2125.8-9250-1900.822520230604019 18010.56
202306010012023-06-01-43511.81-5137.35-13600-4589.25548.120230604008 43510.55
202306010012023-06-01-7211.81-850.32-14320-764.6485.6820230605010 7210.62
202306010012023-06-01-10411.81-1228.24-15360-1098.2413020230608013 10410.56
202306020252023-06-02-29011.78-3416.2-18260-3062.4353.820230608013 29010.56
202306020252023-06-02-12411.78-1460.72-19500-1313.16147.5620230608029 12410.59
202306020252023-06-02-11311.78-1331.14-20630-1187.63143.5120230610012 11310.51
202306030032023-06-03-37311.8-4401.4-24360-3920.23481.1720230610012 37310.51
202306030032023-06-03-29511.8-3481-27310-3171.25309.7520230614006 29510.75
202306030032023-06-03-6011.8-708-27910-643.264.820230614011 6010.72
202306040192023-06-0492510.569768-18660
202306040082023-06-0443510.554589.25-14310
202306050102023-06-057210.62764.64-13590
202306080132023-06-0839410.564160.64-9650
202306080292023-06-0812410.591313.16-8410
202306100122023-06-1048610.515107.86-3550
202306110052023-06-11-21111.85-2500.35-5660-2261.92238.4320230614011 21110.72
202306110052023-06-11-31411.85-3720.9-8800-3366.08354.8220230616009 31410.72
202306110052023-06-11-40111.85-4751.85-12810-4298.72453.1320230617027 40110.72
202306120042023-06-12-32311.93-3853.39-16040-3462.56390.8320230617027 32310.72
202306120042023-06-12-46911.93-5595.17-20730-4952.64642.5320230619024 46910.56
202306130302023-06-13-19511.86-2312.7-22680-2059.2253.520230619024 19510.56
202306130302023-06-13-8711.86-1031.82-23550-934.3897.4420230621020 8710.74
202306130302023-06-13-64611.86-7661.56-30010-6944.5717.0620230622016 64610.75
202306130302023-06-13-4211.86-498.12-30430-449.8248.320230630014 4210.71
202306140062023-06-1429510.753171.25-27480
202306140112023-06-1427110.722905.12-24770
202306160092023-06-1631410.723366.08-21630
202306160152023-06-16-20111.92-2395.92-23640-2152.71243.2120230630014 20110.71
202306170272023-06-1772410.727761.28-16400
202306190242023-06-1966410.567011.84-9760
202306190072023-06-19-1311.86-154.18-9890-139.2314.9520230630014 1310.71
202306190022023-06-19-37811.8-4460.4-13670-4048.38412.0220230630014 37810.71
202306190022023-06-19-53611.8-6324.8-19030
202306210202023-06-218710.74934.38-18160
202306220162023-06-2264610.756944.5-11700
202306230262023-06-23-74911.79-8830.71-19190
202306250212023-06-25-56012-6720-24790
202306260182023-06-26-89211.77-10498.84-33710
202306290282023-06-29-69111.86-8195.26-40620
202306300142023-06-3063410.716790.14-34280
202306020782023-06-02611.7870.68670.68
202306030682023-06-037011.93835.176905.78
202306040642023-06-0415311.861814.582292720.36
202306050892023-06-05-610.71-64.262232649.68-70.68-6.4220230602078 611.78
202306050892023-06-05-1710.71-182.072062446.87-202.81-20.7420230603068 1711.93
202306050742023-06-05-5310.52-557.561531814.58-632.29-74.7320230603068 5311.93
202306050742023-06-05-6210.52-652.24911079.26-735.32-83.0820230604064 6211.86
202306060762023-06-0618211.932171.262733250.52
202306070672023-06-07-3610.51-378.362372823.56-426.96-48.620230604064 3611.86
202306070832023-06-07-4810.61-509.281892254.28-569.28-6020230604064 4811.86
202306080702023-06-0817111.862028.063604282.34
202306080712023-06-0816611.751950.55266232.84
202306120852023-06-123511.87415.455616648.29
202306160692023-06-16-710.73-75.115546565.27-83.02-7.9120230604064 711.86
202306160692023-06-16-12910.73-1384.174255026.3-1538.97-154.820230606076 12911.93
202306170902023-06-171911.9226.14445252.4
202306170872023-06-17-5310.58-560.743914620.11-632.29-71.5520230606076 5311.93
202306170872023-06-17-1010.58-105.83814501.51-118.6-12.820230608070 1011.86
202306170752023-06-17-11210.58-1184.962693173.19-1328.32-143.3620230608070 11211.86
202306180812023-06-18-4910.66-522.342202592.05-581.14-58.820230608070 4911.86
202306180812023-06-18-6110.66-650.261591875.3-716.75-66.4920230608071 6111.75
202306190802023-06-19-10510.57-1109.8554641.55-1233.75-123.920230608071 10511.75
202306190802023-06-19-210.57-21.1452617.81-23.74-2.620230612085 211.87
202306200792023-06-20-3310.51-346.8319226.1-391.71-44.8820230612085 3311.87
202306200792023-06-20-1810.51-189.18111.9-214.2-25.0220230617090 1811.9
202306200662023-06-20-110.65-10.6500-11.9-1.2520230617090 111.9
202306200662023-06-20-3310.65-351.45-330-396-44.5520230621088 3312
202306200662023-06-20-11010.65-1171.5-1430-1302.4-130.920230622091 11011.84
202306210882023-06-213312396-1100
202306220912023-06-2212111.841432.6411130.24
202306220732023-06-227911.86936.94901067.18
202306220862023-06-227611.93906.681661973.86
202306270842023-06-27-1110.56-116.161551843.62-130.24-14.0820230622091 1111.84
202306270842023-06-27-7910.56-834.2476906.68-936.94-102.720230622073 7911.86
202306270842023-06-27-2810.56-295.6848572.64-334.04-38.3620230622086 2811.93
202306290822023-06-2919811.842344.322462916.96
202306290652023-06-291831221964295112.96
202306290922023-06-2917211.962057.126017170.08
202306290722023-06-2906017170.08020230622086 0
202306300772023-06-302911.99347.716307517.79
202306300632023-06-30-4810.53-505.445826945.15-572.64-67.220230622086 4811.93
202306300632023-06-30-7410.53-779.225086068.99-876.16-96.9420230629082 7411.84
202306010482023-06-01351106.4537363.9535137363.95
202306010612023-06-01-92119.49-10993.0825927570.55-9793.41199.6820230601048 92106.45
202306020542023-06-02316105.7733423.3257560993.87
202306030382023-06-03345106.0136573.4592097567.32
202306030412023-06-03265105.3527917.751185125485.07
202306060462023-06-06100107.35107351285136220.07
202306060552023-06-0648107.025136.961333141357.03
202306100402023-06-10317107.2333991.911650175348.94
202306110522023-06-11238106.625370.81888200719.74
202306130352023-06-13-259118.19-30611.211629173149.19-27570.553040.6620230601048 259106.45
202306130352023-06-13-184118.19-21746.961445153687.51-19461.682285.2820230602054 184105.77
202306140452023-06-14-132119.83-15817.561313139725.87-13961.641855.9220230602054 132105.77
202306140452023-06-14-287119.83-34391.211026109301-30424.873966.3420230603038 287106.01
202306150472023-06-15358105.5137772.581384147073.58
202306150502023-06-1525106.492662.251409149735.83
202306150322023-06-15-58119.08-6906.641351143587.25-6148.58758.0620230603038 58106.01
202306150322023-06-15-167119.08-19886.361184125993.8-17593.452292.9120230603041 167105.35
202306150422023-06-15-98119.92-11752.161086115669.5-10324.31427.8620230603041 98105.35
202306150422023-06-15-100119.92-11992986104934.5-10735125720230606046 100107.35
202306150422023-06-15-48119.92-5756.1693899797.54-5136.96619.220230606055 48107.02
202306150422023-06-15-32119.92-3837.4490696366.18-3431.36406.0820230610040 32107.23
202306160582023-06-16-285117.55-33501.7562165805.63-30560.552941.220230610040 285107.23
202306160582023-06-16-142117.55-16692.147950668.43-15137.21554.920230611052 142106.6
202306180372023-06-18-96118.84-11408.6438340434.83-10233.61175.0420230611052 96106.6
202306180372023-06-18-256118.84-30423.0412713424.27-27010.563412.4820230615047 256105.51
202306190442023-06-19429105.1445105.0655658529.33
202306200362023-06-20-102118.63-12100.2645447767.31-10762.021338.2420230615047 102105.51
202306200362023-06-20-25118.63-2965.7542945105.06-2662.25303.520230615050 25106.49
202306200362023-06-20-353118.63-41876.39767990.64-37114.424761.9720230619044 353105.14
202306230432023-06-2344105.14624.412012615.04
202306230392023-06-23-76118.62-9015.12444624.4-7990.641024.4820230619044 76105.14
202306230392023-06-23-44118.62-5219.2800-4624.4594.8820230623043 44105.1
202306230392023-06-23-6118.62-711.72-60-636.8474.8820230624056 6106.14
202306240562023-06-24398106.1442243.7239241606.88
202306240592023-06-24375106.7540031.2576781638.13
202306240492023-06-24-26119.94-3118.4474178878.49-2759.64358.820230624056 26106.14
202306250342023-06-25-366118.07-43213.6237540031.25-38847.244366.3820230624056 366106.14
202306250342023-06-25-109118.07-12869.6326628395.5-11635.751233.8820230624059 109106.75
202306270332023-06-27453107.1448534.4271976929.92
202306270572023-06-27279106.6329749.77998106679.69
202306290512023-06-29352106.4837480.961350144160.65
202306300532023-06-30144107.3615459.841494159620.49
202306300602023-06-3017105.131787.211511161407.7

Example #4

In this example we show how to get the ending inventories balances. We put some transaction date in #t and then use FIFOdet to put the inventory calculations in #fifo. We then execute SQL to get the ending inventory on hand and the cost of that inventory for each product in #fifo.

SET NOCOUNT ON;
DROP TABLE IF EXISTS #t; --SQL Server 2016 and above
DROP TABLE IF EXISTS #fifo;
--SQL Server 2016 and above
--Put some data in #t
SELECT *
INTO #t
FROM
(
    VALUES
        ('2020-11-18-002', 'PROD-000174', '2020-11-18', 223.18, 921.06, 205562.1708)
      , ('2022-04-15-009', 'PROD-000536', '2022-04-15', 187.13, 73.54, 13761.5402)
      , ('2022-04-17-002', 'PROD-000790', '2022-04-17', 50.43, 307.51, 15507.7293)
      , ('2020-09-03-007', 'PROD-000174', '2020-09-03', 235.1, 570.99, 134239.749)
      , ('2020-09-03-008', 'PROD-000536', '2020-09-03', 4.47, 101.87, 455.3589)
      , ('2021-05-19-001', 'PROD-000536', '2021-05-19', -22.05, 603.67, -13310.9235)
      , ('2021-12-05-008', 'PROD-001503', '2021-12-05', -12.8, 1449.14, -18548.992)
      , ('2022-03-30-003', 'PROD-000790', '2022-03-30', -457.17, 327.93, -149919.7581)
      , ('2022-07-18-008', 'PROD-000790', '2022-07-18', -440.99, 239.53, -105630.3347)
      , ('2022-07-18-010', 'PROD-000536', '2022-07-18', -21.18, 466.12, -9872.4216)
      , ('2020-12-29-008', 'PROD-000174', '2020-12-29', -258.02, 896.95, -231431.039)
      , ('2021-12-18-006', 'PROD-001503', '2021-12-18', -62.66, 119.22, -7470.3252)
      , ('2022-02-11-001', 'PROD-000174', '2022-02-11', 183.58, 861.8, 158209.244)
      , ('2022-02-12-004', 'PROD-000174', '2022-02-12', 251.96, 751.35, 189310.146)
      , ('2022-05-05-004', 'PROD-001503', '2022-05-05', -44.83, 464, -20801.12)
      , ('2023-05-23-001', 'PROD-000790', '2023-05-23', -305.53, 386.39, -118053.7367)
      , ('2020-07-17-003', 'PROD-000174', '2020-07-17', -94.4, 529.46, -49981.024)
      , ('2021-11-16-009', 'PROD-001503', '2021-11-16', -34.56, 1664.7, -57532.032)
      , ('2021-11-17-005', 'PROD-001503', '2021-11-17', -49.98, 1406.02, -70272.8796)
      , ('2021-11-19-007', 'PROD-001503', '2021-11-19', -66.05, 64.29, -4246.3545)
      , ('2022-03-08-007', 'PROD-000536', '2022-03-08', 28.94, 69.75, 2018.565)
      , ('2022-03-15-002', 'PROD-000536', '2022-03-15', -1.98, 570.72, -1130.0256)
      , ('2022-06-08-002', 'PROD-001503', '2022-06-08', -20.28, 305.35, -6192.498)
      , ('2022-06-13-003', 'PROD-000536', '2022-06-13', 128.65, 46.35, 5962.9275)
      , ('2021-08-08-004', 'PROD-000536', '2021-08-08', 152.55, 85.52, 13046.076)
      , ('2022-05-27-001', 'PROD-001503', '2022-05-27', -11.62, 1877.09, -21811.7858)
      , ('2022-08-25-006', 'PROD-000790', '2022-08-25', -179.22, 233.08, -41772.5976)
      , ('2021-05-20-005', 'PROD-000174', '2021-05-20', 507.47, 808.33, 410203.2251)
      , ('2021-11-03-003', 'PROD-001503', '2021-11-03', -17.99, 154.53, -2779.9947)
      , ('2022-10-24-004', 'PROD-000790', '2022-10-24', -133.09, 507.95, -67603.0655)
      , ('2023-04-25-004', 'PROD-000858', '2023-04-25', -102.2, 64.95, -6637.89)
      , ('2023-06-02-005', 'PROD-000858', '2023-06-02', 78.06, 572.11, 44658.9066)
      , ('2023-06-03-002', 'PROD-000858', '2023-06-03', 488.89, 589.58, 288239.7662)
      , ('2023-06-04-010', 'PROD-000858', '2023-06-04', 361.51, 450.95, 163022.9345)
      , ('2023-06-07-001', 'PROD-000858', '2023-06-07', 796.21, 554.68, 441641.7628)
      , ('2023-06-10-006', 'PROD-000858', '2023-06-10', 578.43, 947.96, 548328.5028)
      , ('2023-06-11-007', 'PROD-000858', '2023-06-11', -92.17, 60.94, -5616.8398)
      , ('2023-06-15-008', 'PROD-000858', '2023-06-15', 97.65, 709.47, 69279.7455)
      , ('2020-10-26-005', 'PROD-000536', '2020-10-26', 132.16, 140.27, 18538.0832)
      , ('2021-01-13-006', 'PROD-000174', '2021-01-13', 434.12, 967.74, 420115.2888)
      , ('2021-07-25-009', 'PROD-000174', '2021-07-25', -308.81, 1262.86, -389983.7966)
      , ('2021-10-06-010', 'PROD-001503', '2021-10-06', -69.61, 584.84, -40710.7124)
      , ('2022-05-20-011', 'PROD-001503', '2022-05-20', -33.21, 648.4, -21533.364)
      , ('2023-02-20-007', 'PROD-000790', '2023-02-20', -629.76, 340.04, -214143.5904)
      , ('2023-02-26-010', 'PROD-000790', '2023-02-26', -640.88, 648.62, -415687.5856)
      , ('2023-06-17-003', 'PROD-000858', '2023-06-17', 304.03, 728.47, 221476.7341)
      , ('2023-06-17-009', 'PROD-000858', '2023-06-17', 712.42, 588.63, 419351.7846)
      , ('2022-03-25-006', 'PROD-000536', '2022-03-25', 148.11, 84.54, 12521.2194)
      , ('2022-12-05-009', 'PROD-000790', '2022-12-05', -280.92, 305, -85680.6)
      , ('2023-04-01-005', 'PROD-000790', '2023-04-01', -32.38, 409.86, -13271.2668)
) n (trn, prod, tdate, qty, unitprice, extprice);

--Run FIFOdet and store the results in #fifo
SELECT CAST(n.prod as varchar(11))  as prod
     , k.rn
     , CAST(k.ID as varchar(14))    as ID
     , ROUND(k.qty, 4)              as qty
     , ROUND(k.unitprice, 4)        as unitprice
     , ROUND(k.EXPRICE, 4)          as exprice
     , ROUND(k.INVONHAND, 4)        as invonhand
     , ROUND(k.INVCOST, 4)          as invcost
     , ROUND(k.COGS, 4)             as cogs
     , ROUND(k.GM, 4)               as gm
     , CAST(k.PURID as varchar(14)) as purid
     , ROUND(k.PURQTY, 4)           as purqty
     , ROUND(k.PURPRICE, 4)         purprice
INTO #fifo
FROM
(SELECT DISTINCT PROD FROM #t) n
    CROSS APPLY wct.FIFOdet(REPLACE(
                                       'SELECT trn,qty,extprice FROM #t WHERE prod = ''@prod'' ORDER BY     tdate ASC, qty DESC'
                                     , '@prod'
                                     , n.prod
                                   )
                           )   k
--Get the closing balances
SELECT prod
     , invonhand
     , invcost
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY PROD ORDER BY PROD, RN DESC) as rnrev
         , f.*
    FROM #fifo f
) n
WHERE rnrev = 1

This produces the following result.

prodinvonhandinvcost
PROD-0001741174.18981435.0709
PROD-000536736.860133.8115
PROD-000790-3049.510
PROD-0008583222.832082767.1807
PROD-001503-423.590

Note that 2 of the products have negative inventory balances, meaning that the sales exceeded the purchases. Since the inventory balance is less than zero the inventory cost is zero. In the next two examples we will show how to get the details for inventory balances greater than zero and less than zero.

Example #5

SET NOCOUNT ON;
DROP TABLE IF EXISTS #t; --SQL Server 2016 and above
DROP TABLE IF EXISTS #fifo; --SQL Server 2016 and above
--Put some data in #t
SELECT *
INTO #t
FROM
(
    VALUES
        ('2020-11-18-002', 'PROD-000174', '2020-11-18', 223.18, 921.06, 205562.1708)
      , ('2022-04-15-009', 'PROD-000536', '2022-04-15', 187.13, 73.54, 13761.5402)
      , ('2022-04-17-002', 'PROD-000790', '2022-04-17', 50.43, 307.51, 15507.7293)
      , ('2020-09-03-007', 'PROD-000174', '2020-09-03', 235.1, 570.99, 134239.749)
      , ('2020-09-03-008', 'PROD-000536', '2020-09-03', 4.47, 101.87, 455.3589)
      , ('2021-05-19-001', 'PROD-000536', '2021-05-19', -22.05, 603.67, -13310.9235)
      , ('2021-12-05-008', 'PROD-001503', '2021-12-05', -12.8, 1449.14, -18548.992)
      , ('2022-03-30-003', 'PROD-000790', '2022-03-30', -457.17, 327.93, -149919.7581)
      , ('2022-07-18-008', 'PROD-000790', '2022-07-18', -440.99, 239.53, -105630.3347)
      , ('2022-07-18-010', 'PROD-000536', '2022-07-18', -21.18, 466.12, -9872.4216)
      , ('2020-12-29-008', 'PROD-000174', '2020-12-29', -258.02, 896.95, -231431.039)
      , ('2021-12-18-006', 'PROD-001503', '2021-12-18', -62.66, 119.22, -7470.3252)
      , ('2022-02-11-001', 'PROD-000174', '2022-02-11', 183.58, 861.8, 158209.244)
      , ('2022-02-12-004', 'PROD-000174', '2022-02-12', 251.96, 751.35, 189310.146)
      , ('2022-05-05-004', 'PROD-001503', '2022-05-05', -44.83, 464, -20801.12)
      , ('2023-05-23-001', 'PROD-000790', '2023-05-23', -305.53, 386.39, -118053.7367)
      , ('2020-07-17-003', 'PROD-000174', '2020-07-17', -94.4, 529.46, -49981.024)
      , ('2021-11-16-009', 'PROD-001503', '2021-11-16', -34.56, 1664.7, -57532.032)
      , ('2021-11-17-005', 'PROD-001503', '2021-11-17', -49.98, 1406.02, -70272.8796)
      , ('2021-11-19-007', 'PROD-001503', '2021-11-19', -66.05, 64.29, -4246.3545)
      , ('2022-03-08-007', 'PROD-000536', '2022-03-08', 28.94, 69.75, 2018.565)
      , ('2022-03-15-002', 'PROD-000536', '2022-03-15', -1.98, 570.72, -1130.0256)
      , ('2022-06-08-002', 'PROD-001503', '2022-06-08', -20.28, 305.35, -6192.498)
      , ('2022-06-13-003', 'PROD-000536', '2022-06-13', 128.65, 46.35, 5962.9275)
      , ('2021-08-08-004', 'PROD-000536', '2021-08-08', 152.55, 85.52, 13046.076)
      , ('2022-05-27-001', 'PROD-001503', '2022-05-27', -11.62, 1877.09, -21811.7858)
      , ('2022-08-25-006', 'PROD-000790', '2022-08-25', -179.22, 233.08, -41772.5976)
      , ('2021-05-20-005', 'PROD-000174', '2021-05-20', 507.47, 808.33, 410203.2251)
      , ('2021-11-03-003', 'PROD-001503', '2021-11-03', -17.99, 154.53, -2779.9947)
      , ('2022-10-24-004', 'PROD-000790', '2022-10-24', -133.09, 507.95, -67603.0655)
      , ('2023-04-25-004', 'PROD-000858', '2023-04-25', -102.2, 64.95, -6637.89)
      , ('2023-06-02-005', 'PROD-000858', '2023-06-02', 78.06, 572.11, 44658.9066)
      , ('2023-06-03-002', 'PROD-000858', '2023-06-03', 488.89, 589.58, 288239.7662)
      , ('2023-06-04-010', 'PROD-000858', '2023-06-04', 361.51, 450.95, 163022.9345)
      , ('2023-06-07-001', 'PROD-000858', '2023-06-07', 796.21, 554.68, 441641.7628)
      , ('2023-06-10-006', 'PROD-000858', '2023-06-10', 578.43, 947.96, 548328.5028)
      , ('2023-06-11-007', 'PROD-000858', '2023-06-11', -92.17, 60.94, -5616.8398)
      , ('2023-06-15-008', 'PROD-000858', '2023-06-15', 97.65, 709.47, 69279.7455)
      , ('2020-10-26-005', 'PROD-000536', '2020-10-26', 132.16, 140.27, 18538.0832)
      , ('2021-01-13-006', 'PROD-000174', '2021-01-13', 434.12, 967.74, 420115.2888)
      , ('2021-07-25-009', 'PROD-000174', '2021-07-25', -308.81, 1262.86, -389983.7966)
      , ('2021-10-06-010', 'PROD-001503', '2021-10-06', -69.61, 584.84, -40710.7124)
      , ('2022-05-20-011', 'PROD-001503', '2022-05-20', -33.21, 648.4, -21533.364)
      , ('2023-02-20-007', 'PROD-000790', '2023-02-20', -629.76, 340.04, -214143.5904)
      , ('2023-02-26-010', 'PROD-000790', '2023-02-26', -640.88, 648.62, -415687.5856)
      , ('2023-06-17-003', 'PROD-000858', '2023-06-17', 304.03, 728.47, 221476.7341)
      , ('2023-06-17-009', 'PROD-000858', '2023-06-17', 712.42, 588.63, 419351.7846)
      , ('2022-03-25-006', 'PROD-000536', '2022-03-25', 148.11, 84.54, 12521.2194)
      , ('2022-12-05-009', 'PROD-000790', '2022-12-05', -280.92, 305, -85680.6)
      , ('2023-04-01-005', 'PROD-000790', '2023-04-01', -32.38, 409.86, -13271.2668)
) n (trn, prod, tdate, qty, unitprice, extprice);
--Run FIFOdet and store the results in #fifo
SELECT CAST(n.prod as varchar(11))  as prod
     , k.rn
     , CAST(k.ID as varchar(14))    as ID
     , ROUND(k.qty, 4)              as qty
     , ROUND(k.unitprice, 4)        as unitprice
     , ROUND(k.EXPRICE, 4)          as exprice
     , ROUND(k.INVONHAND, 4)        as invonhand
     , ROUND(k.INVCOST, 4)          as invcost
     , ROUND(k.COGS, 4)             as cogs
     , ROUND(k.GM, 4)               as gm
     , CAST(k.PURID as varchar(14)) as purid
     , ROUND(k.PURQTY, 4)           as purqty
     , ROUND(k.PURPRICE, 4)         purprice
INTO #fifo
FROM
(SELECT DISTINCT PROD FROM #t) n
    CROSS APPLY wct.FIFOdet(REPLACE(
                                       'SELECT trn,qty,extprice FROM #t WHERE prod = ''@prod'' ORDER BY tdate ASC, qty DESC'
                                     , '@prod'
                                     , n.prod
                                   )
                           )   k
--Get the closing balances
/*SELECT prod
     , invonhand
     , invcost
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY PROD ORDER BY PROD, RN DESC) as rnrev
         , f.*
    FROM #fifo f
) n
WHERE rnrev = 1*/
--Get the details for the closing inventory
SELECT n.PROD
     , n.ID
     , t.tdate
     , t.qty                                                                              as [Original Transaction Quantity]
     , INVONHAND                                                                          as [Remaining Transaction Quantity]
     , t.extprice                                                                         as [Original Transaction Cost]
     , INVCOST                                                                            as [Remaining Transaction Cost]
     , SUM(INVONHAND) OVER (PARTITION BY n.PROD ORDER BY n.PROD, t.tdate ASC, t.qty DESC) as [Running Inventory Quantity]
     , SUM(INVCOST) OVER (PARTITION BY n.PROD ORDER BY n.PROD, t.tdate ASC, t.qty DESC)   as [Running Inventory Cost]
FROM
(
    SELECT p.PROD
         , p.ID
         , ROUND(p.qty + SUM(ISNULL(-s.PURQTY, 0)), 4)  AS INVONHAND
         , ROUND(p.exprice + SUM(ISNULL(s.COGS, 0)), 4) AS INVCOST
    FROM #fifo                p
        LEFT OUTER JOIN #fifo s
            ON p.PROD = s.prod
               AND p.id = s.purid
    WHERE p.qty > 0
    GROUP BY p.PROD
           , p.ID
           , p.qty
           , p.exprice
)                 n
    INNER JOIN #t t
        ON n.ID = t.trn
WHERE INVONHAND > 0
ORDER BY n.prod
       , t.tdate ASC
       , t.qty DESC

This produces the following result.

PRODIDtdateOriginal Transaction QuantityRemaining Transaction QuantityOriginal Transaction CostRemaining Transaction CostRunning Inventory QuantityRunning Inventory Cost
PROD-0001742021-01-13-0062021-01-13434.12231.17420115.2888223712.4558231.17223712.4558
PROD-0001742021-05-20-0052021-05-20507.47507.47410203.2251410203.2251738.64633915.6809
PROD-0001742022-02-11-0012022-02-11183.58183.58158209.244158209.244922.22792124.9249
PROD-0001742022-02-12-0042022-02-12251.96251.96189310.146189310.1461174.18981435.0708999999
PROD-0005362020-10-26-0052020-10-26132.1691.4218538.083212823.483491.4212823.4834
PROD-0005362021-08-08-0042021-08-08152.55152.5513046.07613046.076243.9725869.5594
PROD-0005362022-03-08-0072022-03-0828.9428.942018.5652018.565272.9127888.1244
PROD-0005362022-03-25-0062022-03-25148.11148.1112521.219412521.2194421.0240409.3438
PROD-0005362022-04-15-0092022-04-15187.13187.1313761.540213761.5402608.150000000000154170.88399999999
PROD-0005362022-06-13-0032022-06-13128.65128.655962.92755962.9275736.800000000000160133.81149999999
PROD-0008582023-06-03-0022023-06-03488.89372.58288239.7662219665.7164372.58219665.7164
PROD-0008582023-06-04-0102023-06-04361.51361.51163022.9345163022.9345734.0899999999999382688.6509
PROD-0008582023-06-07-0012023-06-07796.21796.21441641.7628441641.76281530.3824330.4137
PROD-0008582023-06-10-0062023-06-10578.43578.43548328.5028548328.50282108.731372658.9165
PROD-0008582023-06-15-0082023-06-1597.6597.6569279.745569279.74552206.381441938.662
PROD-0008582023-06-17-0092023-06-17712.42712.42419351.7846419351.78462918.81861290.4466
PROD-0008582023-06-17-0032023-06-17304.03304.03221476.7341221476.73413222.832082767.1807

Example #6

In this example, using the same tables that were created in Example #4, we show how to get the details of the outstanding unfilled order balances i.e., negative balances. For this example, those details are the product identifier, the transaction identifier, the transaction date, the original transaction quantity, the remaining transaction quantity, the original transaction amount, and the remaining transaction amount. This information might be used in the next inventory calculation after all the settled transactions have been archived. Additionally, we have included running totals of the sale quantity and sale amount which show that the amounts agree with the amounts in Example #4.

SET NOCOUNT ON;
DROP TABLE IF EXISTS #t; --SQL Server 2016 and above
DROP TABLE IF EXISTS #fifo; --SQL Server 2016 and above
--Put some data in #t
SELECT *
INTO #t
FROM
(
    VALUES
        ('2020-11-18-002', 'PROD-000174', '2020-11-18', 223.18, 921.06, 205562.1708)
      , ('2022-04-15-009', 'PROD-000536', '2022-04-15', 187.13, 73.54, 13761.5402)
      , ('2022-04-17-002', 'PROD-000790', '2022-04-17', 50.43, 307.51, 15507.7293)
      , ('2020-09-03-007', 'PROD-000174', '2020-09-03', 235.1, 570.99, 134239.749)
      , ('2020-09-03-008', 'PROD-000536', '2020-09-03', 4.47, 101.87, 455.3589)
      , ('2021-05-19-001', 'PROD-000536', '2021-05-19', -22.05, 603.67, -13310.9235)
      , ('2021-12-05-008', 'PROD-001503', '2021-12-05', -12.8, 1449.14, -18548.992)
      , ('2022-03-30-003', 'PROD-000790', '2022-03-30', -457.17, 327.93, -149919.7581)
      , ('2022-07-18-008', 'PROD-000790', '2022-07-18', -440.99, 239.53, -105630.3347)
      , ('2022-07-18-010', 'PROD-000536', '2022-07-18', -21.18, 466.12, -9872.4216)
      , ('2020-12-29-008', 'PROD-000174', '2020-12-29', -258.02, 896.95, -231431.039)
      , ('2021-12-18-006', 'PROD-001503', '2021-12-18', -62.66, 119.22, -7470.3252)
      , ('2022-02-11-001', 'PROD-000174', '2022-02-11', 183.58, 861.8, 158209.244)
      , ('2022-02-12-004', 'PROD-000174', '2022-02-12', 251.96, 751.35, 189310.146)
      , ('2022-05-05-004', 'PROD-001503', '2022-05-05', -44.83, 464, -20801.12)
      , ('2023-05-23-001', 'PROD-000790', '2023-05-23', -305.53, 386.39, -118053.7367)
      , ('2020-07-17-003', 'PROD-000174', '2020-07-17', -94.4, 529.46, -49981.024)
      , ('2021-11-16-009', 'PROD-001503', '2021-11-16', -34.56, 1664.7, -57532.032)
      , ('2021-11-17-005', 'PROD-001503', '2021-11-17', -49.98, 1406.02, -70272.8796)
      , ('2021-11-19-007', 'PROD-001503', '2021-11-19', -66.05, 64.29, -4246.3545)
      , ('2022-03-08-007', 'PROD-000536', '2022-03-08', 28.94, 69.75, 2018.565)
      , ('2022-03-15-002', 'PROD-000536', '2022-03-15', -1.98, 570.72, -1130.0256)
      , ('2022-06-08-002', 'PROD-001503', '2022-06-08', -20.28, 305.35, -6192.498)
      , ('2022-06-13-003', 'PROD-000536', '2022-06-13', 128.65, 46.35, 5962.9275)
      , ('2021-08-08-004', 'PROD-000536', '2021-08-08', 152.55, 85.52, 13046.076)
      , ('2022-05-27-001', 'PROD-001503', '2022-05-27', -11.62, 1877.09, -21811.7858)
      , ('2022-08-25-006', 'PROD-000790', '2022-08-25', -179.22, 233.08, -41772.5976)
      , ('2021-05-20-005', 'PROD-000174', '2021-05-20', 507.47, 808.33, 410203.2251)
      , ('2021-11-03-003', 'PROD-001503', '2021-11-03', -17.99, 154.53, -2779.9947)
      , ('2022-10-24-004', 'PROD-000790', '2022-10-24', -133.09, 507.95, -67603.0655)
      , ('2023-04-25-004', 'PROD-000858', '2023-04-25', -102.2, 64.95, -6637.89)
      , ('2023-06-02-005', 'PROD-000858', '2023-06-02', 78.06, 572.11, 44658.9066)
      , ('2023-06-03-002', 'PROD-000858', '2023-06-03', 488.89, 589.58, 288239.7662)
      , ('2023-06-04-010', 'PROD-000858', '2023-06-04', 361.51, 450.95, 163022.9345)
      , ('2023-06-07-001', 'PROD-000858', '2023-06-07', 796.21, 554.68, 441641.7628)
      , ('2023-06-10-006', 'PROD-000858', '2023-06-10', 578.43, 947.96, 548328.5028)
      , ('2023-06-11-007', 'PROD-000858', '2023-06-11', -92.17, 60.94, -5616.8398)
      , ('2023-06-15-008', 'PROD-000858', '2023-06-15', 97.65, 709.47, 69279.7455)
      , ('2020-10-26-005', 'PROD-000536', '2020-10-26', 132.16, 140.27, 18538.0832)
      , ('2021-01-13-006', 'PROD-000174', '2021-01-13', 434.12, 967.74, 420115.2888)
      , ('2021-07-25-009', 'PROD-000174', '2021-07-25', -308.81, 1262.86, -389983.7966)
      , ('2021-10-06-010', 'PROD-001503', '2021-10-06', -69.61, 584.84, -40710.7124)
      , ('2022-05-20-011', 'PROD-001503', '2022-05-20', -33.21, 648.4, -21533.364)
      , ('2023-02-20-007', 'PROD-000790', '2023-02-20', -629.76, 340.04, -214143.5904)
      , ('2023-02-26-010', 'PROD-000790', '2023-02-26', -640.88, 648.62, -415687.5856)
      , ('2023-06-17-003', 'PROD-000858', '2023-06-17', 304.03, 728.47, 221476.7341)
      , ('2023-06-17-009', 'PROD-000858', '2023-06-17', 712.42, 588.63, 419351.7846)
      , ('2022-03-25-006', 'PROD-000536', '2022-03-25', 148.11, 84.54, 12521.2194)
      , ('2022-12-05-009', 'PROD-000790', '2022-12-05', -280.92, 305, -85680.6)
      , ('2023-04-01-005', 'PROD-000790', '2023-04-01', -32.38, 409.86, -13271.2668)
) n (trn, prod, tdate, qty, unitprice, extprice);
--Run FIFOdet and store the results in #fifo
SELECT CAST(n.prod as varchar(11))  as prod
     , k.rn
     , CAST(k.ID as varchar(14))    as ID
     , ROUND(k.qty, 4)              as qty
     , ROUND(k.unitprice, 4)        as unitprice
     , ROUND(k.EXPRICE, 4)          as exprice
     , ROUND(k.INVONHAND, 4)        as invonhand
     , ROUND(k.INVCOST, 4)          as invcost
     , ROUND(k.COGS, 4)             as cogs
     , ROUND(k.GM, 4)               as gm
     , CAST(k.PURID as varchar(14)) as purid
     , ROUND(k.PURQTY, 4)           as purqty
     , ROUND(k.PURPRICE, 4)         purprice
INTO #fifo
FROM
(SELECT DISTINCT PROD FROM #t) n
    CROSS APPLY wct.FIFOdet(REPLACE(
                                       'SELECT trn,qty,extprice FROM #t WHERE prod = ''@prod'' ORDER BY tdate ASC, qty DESC'
                                     , '@prod'
                                     , n.prod
                                   )
                           )   k
--Get the unfilled sales
SELECT f.prod
     , f.ID
     , t.tdate
     , t.qty                                                           as [Original Transaction Quantity]
     , f.qty                                                           as [Remaining Transaction Quantity]
     , t.extprice                                                      as [Original Transaction Amount]
     , f.exprice                                                       [Remaining Transaction Amount]
     , SUM(f.qty) OVER (PARTITION BY f.prod ORDER BY f.prod, f.rn)     as [Running Sale Quantity]
     , SUM(f.exprice) OVER (PARTITION BY f.prod ORDER BY f.prod, f.rn) as [Running Sale Amount]
FROM #fifo        f
    INNER JOIN #t t
        ON f.ID = t.trn
WHERE f.qty < 0
      AND purid IS NULL

This produces the following result.

PRODIDtdateOriginal Transaction QuantityRemaining Transaction QuantityOriginal Transaction CostRemaining Transaction CostRunning Inventory QuantityRunning Inventory Cost
2022-03-30-0032022-03-30-457.17-406.74
2022-07-18-0082022-07-18-440.99-440.99
2022-08-25-0062022-08-25-179.22-179.22
2022-10-24-0042022-10-24-133.09-133.09
2022-12-05-0092022-12-05-280.92-280.92
2023-02-20-0072023-02-20-629.76-629.76
2023-02-26-0102023-02-26-640.88-640.88
2023-04-01-0052023-04-01-32.38-32.38
2023-05-23-0012023-05-23-305.53-305.53
2021-10-06-0102021-10-06-69.61-69.61
2021-11-03-0032021-11-03-17.99-17.99
2021-11-16-0092021-11-16-34.56-34.56
2021-11-17-0052021-11-17-49.98-49.98
2021-11-19-0072021-11-19-66.05-66.05
2021-12-05-0082021-12-05-12.8-12.8
2021-12-18-0062021-12-18-62.66-62.66
2022-05-05-0042022-05-05-44.83-44.83
2022-05-20-0112022-05-20-33.21-33.21
2022-05-27-0012022-05-27-11.62-11.62
2022-06-08-0022022-06-08-20.28-20.28

See Also

FIFOtvf - Calculate the endrunning FIFO balances in an ordered resultant table.

FIFOEnd - Calculate the ending FIFO balances in an ordered resultant table.