SQL Server LIFOend Function
Updated 2023-10-12 12:53:07.617000
Description
Use the table-valued function LIFOend to return the items which make up the ending inventory on a periodic LIFO (Last In, First Out) basis. LIFOend calculates the balances for each value from the first value to the last value in the ordered group or partition.
LIFOend supports both long and short inventory positions. In other words, if the quantity on hand falls below the zero, LIFOend calculates from the last sale or withdrawal transaction, rather than from the last purchases or additions to inventory.
LIFOend assumes that the quantity (i.e. the number of units) of the transaction and the monetary value of the transaction have the same sign. LIFOend adds NULL values to the inventory at the last price.
LIFOend supports the processing of multiple products in a single SQL statement. LIFOend resets all calculated values when the row number value is 1.
Syntax
SELECT * FROM [westclintech].[wct].[LIFOend] (
<@DataQuery, nvarchar(max),>)
Arguments
@DataQuery
An SQL statement which returns a resultant table containing the unique transaction identifier, ROW_NUMBER(), quantity and amount.
Return Type
table
| colName | colDatatype | colDesc |
|---|---|---|
| UID | sql_variant | unique transaction identifier |
| RowNumber | int | the row number passed into the function |
| qty | float | inventory number of items |
| amt | float | inventory value |
Remarks
If the 3rd column (quantity) of the resultant table from @DataQuery contains NULL an error will be generated.
For multi-product sets, this function relies on the SQL Server ROW_NUMBER() function and expects results to be returned in ascending ROW_NUMBER() order within a PARTITION.
When ROW_NUMBER() = 1 all inventory values are re-initialized.
Examples
Example #1
In the following examples, we calculate LIFO inventory values for stock trades in the ABC, XYZ, and GHI companies. We will create a temporary table, #c and populate it with some data. We can be either short or long the shares at any point in time.
CREATE TABLE #c
(
trn int,
sym char(3),
tDate date,
qty money,
price_unit money,
price_extended money,
PRIMARY KEY (trn)
);
--Populate the table with some data
INSERT INTO #c
VALUES
(01131019, 'XYZ', '2013-10-19', 424, 25.13, 10655.12);
INSERT INTO #c
VALUES
(02130617, 'ABC', '2013-06-17', 313, 12.93, 4047.09);
INSERT INTO #c
VALUES
(03130308, 'ABC', '2013-03-08', -157, 13.17, -2067.69);
INSERT INTO #c
VALUES
(04130516, 'GHI', '2013-05-16', 160, 34.48, 5516.8);
INSERT INTO #c
VALUES
(05130706, 'XYZ', '2013-07-06', -170, 23.46, -3988.2);
INSERT INTO #c
VALUES
(06130924, 'GHI', '2013-09-24', 328, 34.95, 11463.6);
INSERT INTO #c
VALUES
(07130722, 'ABC', '2013-07-22', 599, 13.65, 8176.35);
INSERT INTO #c
VALUES
(08131231, 'ABC', '2013-12-31', -145, 13.19, -1912.55);
INSERT INTO #c
VALUES
(09131025, 'XYZ', '2013-10-25', -153, 24.31, -3719.43);
INSERT INTO #c
VALUES
(10130908, 'ABC', '2013-09-08', -386, 13.65, -5268.9);
INSERT INTO #c
VALUES
(11130906, 'XYZ', '2013-09-06', -13, 23.97, -311.61);
INSERT INTO #c
VALUES
(12130621, 'ABC', '2013-06-21', -326, 12.73, -4149.98);
INSERT INTO #c
VALUES
(13131221, 'GHI', '2013-12-21', 72, 34.38, 2475.36);
INSERT INTO #c
VALUES
(14130705, 'XYZ', '2013-07-05', -277, 25.01, -6927.77);
INSERT INTO #c
VALUES
(15130307, 'GHI', '2013-03-07', 559, 35.21, 19682.39);
INSERT INTO #c
VALUES
(16131107, 'ABC', '2013-11-07', 27, 12.68, 342.36);
INSERT INTO #c
VALUES
(17130924, 'GHI', '2013-09-24', -291, 35.69, -10385.79);
INSERT INTO #c
VALUES
(18140125, 'GHI', '2014-01-25', -78, 35.46, -2765.88);
INSERT INTO #c
VALUES
(19130516, 'XYZ', '2013-05-16', 315, 23.57, 7424.55);
INSERT INTO #c
VALUES
(20130518, 'ABC', '2013-05-18', 298, 13.23, 3942.54);
INSERT INTO #c
VALUES
(21131103, 'XYZ', '2013-11-03', -326, 23.24, -7576.24);
INSERT INTO #c
VALUES
(22131012, 'XYZ', '2013-10-12', 596, 23.16, 13803.36);
INSERT INTO #c
VALUES
(23130619, 'XYZ', '2013-06-19', 296, 23.46, 6944.16);
INSERT INTO #c
VALUES
(24130418, 'XYZ', '2013-04-18', 275, 24.83, 6828.25);
INSERT INTO #c
VALUES
(25130408, 'ABC', '2013-04-08', 298, 12.98, 3868.04);
INSERT INTO #c
VALUES
(26130320, 'ABC', '2013-03-20', -92, 13.64, -1254.88);
INSERT INTO #c
VALUES
(27130906, 'XYZ', '2013-09-06', -147, 23.81, -3500.07);
INSERT INTO #c
VALUES
(28131209, 'XYZ', '2013-12-09', 315, 24.46, 7704.9);
INSERT INTO #c
VALUES
(29130602, 'XYZ', '2013-06-02', 114, 24.29, 2769.06);
INSERT INTO #c
VALUES
(30130519, 'XYZ', '2013-05-19', 467, 23.15, 10811.05);
INSERT INTO #c
VALUES
(31140205, 'XYZ', '2014-02-05', 42, 24.39, 1024.38);
INSERT INTO #c
VALUES
(32130310, 'ABC', '2013-03-10', -63, 12.61, -794.43);
INSERT INTO #c
VALUES
(33140102, 'XYZ', '2014-01-02', -196, 22.98, -4504.08);
INSERT INTO #c
VALUES
(34130507, 'XYZ', '2013-05-07', 55, 23.43, 1288.65);
INSERT INTO #c
VALUES
(35130321, 'XYZ', '2013-03-21', 275, 24.83, 6828.25);
INSERT INTO #c
VALUES
(36130917, 'XYZ', '2013-09-17', 92, 24.6, 2263.2);
INSERT INTO #c
VALUES
(37130220, 'XYZ', '2013-02-20', 528, 23.54, 12429.12);
INSERT INTO #c
VALUES
(38130311, 'XYZ', '2013-03-11', -193, 24.9, -4805.7);
INSERT INTO #c
VALUES
(39130908, 'ABC', '2013-09-08', 490, 12.69, 6218.1);
INSERT INTO #c
VALUES
(40131013, 'XYZ', '2013-10-13', 359, 23.91, 8583.69);
INSERT INTO #c
VALUES
(41130310, 'ABC', '2013-03-10', 463, 13.38, 6194.94);
INSERT INTO #c
VALUES
(42131011, 'XYZ', '2013-10-11', -250, 23.12, -5780);
INSERT INTO #c
VALUES
(43130521, 'GHI', '2013-05-21', -174, 34.2, -5950.8);
INSERT INTO #c
VALUES
(44130227, 'XYZ', '2013-02-27', 357, 22.86, 8161.02);
INSERT INTO #c
VALUES
(45131030, 'XYZ', '2013-10-30', -350, 23.36, -8176);
INSERT INTO #c
VALUES
(46130301, 'ABC', '2013-03-01', 157, 13.01, 2042.57);
INSERT INTO #c
VALUES
(47130619, 'XYZ', '2013-06-19', 413, 25.18, 10399.34);
INSERT INTO #c
VALUES
(48130430, 'ABC', '2013-04-30', 229, 13.32, 3050.28);
INSERT INTO #c
VALUES
(49130508, 'ABC', '2013-05-08', 238, 12.79, 3044.02);
INSERT INTO #c
VALUES
(50131103, 'GHI', '2013-11-03', -246, 35.61, -8760.06);
INSERT INTO #c
VALUES
(51131206, 'GHI', '2013-12-06', 85, 33.64, 2859.4);
INSERT INTO #c
VALUES
(52131014, 'GHI', '2013-10-14', -91, 33.12, -3013.92);
INSERT INTO #c
VALUES
(53140102, 'GHI', '2014-01-02', 396, 35.52, 14065.92);
INSERT INTO #c
VALUES
(54130831, 'XYZ', '2013-08-31', -61, 23.23, -1417.03);
INSERT INTO #c
VALUES
(55130630, 'XYZ', '2013-06-30', -272, 23.45, -6378.4);
INSERT INTO #c
VALUES
(56130419, 'GHI', '2013-04-19', 416, 34.46, 14335.36);
INSERT INTO #c
VALUES
(57130813, 'XYZ', '2013-08-13', -163, 23.65, -3854.95);
INSERT INTO #c
VALUES
(58130722, 'XYZ', '2013-07-22', -88, 24.64, -2168.32);
INSERT INTO #c
VALUES
(59130320, 'XYZ', '2013-03-20', -20, 23.59, -471.8);
INSERT INTO #c
VALUES
(60130419, 'XYZ', '2013-04-19', 277, 22.83, 6323.91);
INSERT INTO #c
VALUES
(61130916, 'ABC', '2013-09-16', -202, 12.94, -2613.88);
INSERT INTO #c
VALUES
(62131027, 'XYZ', '2013-10-27', -248, 24.71, -6128.08);
INSERT INTO #c
VALUES
(63130806, 'GHI', '2013-08-06', 445, 33.5, 14907.5);
INSERT INTO #c
VALUES
(64140109, 'XYZ', '2014-01-09', -253, 24.46, -6188.38);
INSERT INTO #c
VALUES
(65131227, 'GHI', '2013-12-27', 376, 33.49, 12592.24);
INSERT INTO #c
VALUES
(66140203, 'XYZ', '2014-02-03', 459, 23.7, 10878.3);
INSERT INTO #c
VALUES
(67130302, 'XYZ', '2013-03-02', 9, 24.04, 216.36);
INSERT INTO #c
VALUES
(68130223, 'ABC', '2013-02-23', 238, 12.78, 3041.64);
INSERT INTO #c
VALUES
(69130403, 'GHI', '2013-04-03', -151, 33.16, -5007.16);
INSERT INTO #c
VALUES
(70130702, 'GHI', '2013-07-02', -162, 35.48, -5747.76);
INSERT INTO #c
VALUES
(71130731, 'ABC', '2013-07-31', 79, 13.55, 1070.45);
INSERT INTO #c
VALUES
(72140204, 'XYZ', '2014-02-04', -208, 24.36, -5066.88);
INSERT INTO #c
VALUES
(73131028, 'GHI', '2013-10-28', -46, 34.65, -1593.9);
INSERT INTO #c
VALUES
(74130619, 'XYZ', '2013-06-19', 202, 22.95, 4635.9);
INSERT INTO #c
VALUES
(75131216, 'GHI', '2013-12-16', 500, 33.55, 16775);
INSERT INTO #c
VALUES
(76131009, 'ABC', '2013-10-09', 249, 13.47, 3354.03);
INSERT INTO #c
VALUES
(77130825, 'GHI', '2013-08-25', 272, 33.86, 9209.92);
INSERT INTO #c
VALUES
(78140112, 'XYZ', '2014-01-12', 332, 24.28, 8060.96);
INSERT INTO #c
VALUES
(79131223, 'XYZ', '2013-12-23', -376, 25.12, -9445.12);
INSERT INTO #c
VALUES
(80140126, 'XYZ', '2014-01-26', 404, 24.23, 9788.92);
INSERT INTO #c
VALUES
(81131123, 'GHI', '2013-11-23', 187, 33.86, 6331.82);
INSERT INTO #c
VALUES
(82140131, 'XYZ', '2014-01-31', 548, 23.65, 12960.2);
INSERT INTO #c
VALUES
(83130428, 'XYZ', '2013-04-28', 142, 23.13, 3284.46);
INSERT INTO #c
VALUES
(84140104, 'XYZ', '2014-01-04', 261, 24.46, 6384.06);
INSERT INTO #c
VALUES
(85131002, 'GHI', '2013-10-02', -295, 32.51, -9590.45);
INSERT INTO #c
VALUES
(86131114, 'ABC', '2013-11-14', -386, 12.4, -4786.4);
INSERT INTO #c
VALUES
(87131116, 'GHI', '2013-11-16', -320, 34.16, -10931.2);
INSERT INTO #c
VALUES
(88131009, 'ABC', '2013-10-09', 187, 13.36, 2498.32);
INSERT INTO #c
VALUES
(89130611, 'ABC', '2013-06-11', 130, 13.02, 1692.6);
INSERT INTO #c
VALUES
(90130430, 'ABC', '2013-04-30', -191, 13.49, -2576.59);
INSERT INTO #c
VALUES
(91130615, 'XYZ', '2013-06-15', 545, 23.88, 13014.6);
INSERT INTO #c
VALUES
(92130924, 'XYZ', '2013-09-24', -248, 24.58, -6095.84);
INSERT INTO #c
VALUES
(93130622, 'ABC', '2013-06-22', -227, 13.47, -3057.69);
INSERT INTO #c
VALUES
(94131117, 'GHI', '2013-11-17', -92, 32.65, -3003.8);
INSERT INTO #c
VALUES
(95130908, 'GHI', '2013-09-08', 103, 35.42, 3648.26);
INSERT INTO #c
VALUES
(96130716, 'GHI', '2013-07-16', -347, 35.29, -12245.63);
INSERT INTO #c
VALUES
(97131125, 'XYZ', '2013-11-25', -278, 23.18, -6444.04);
INSERT INTO #c
VALUES
(98130413, 'XYZ', '2013-04-13', 243, 24.25, 5892.75);
INSERT INTO #c
VALUES
(99130515, 'XYZ', '2013-05-15', 97, 25.13, 2437.61);
--Calculate the LIFO Values and store in temp table
SELECT cast(UID as int) as ID,
RowNumber,
qty,
amt
INTO #lifo
FROM wct.LIFOend('SELECT trn, ROW_NUMBER() OVER (PARTITION by sym ORDER BY sym, tDate, trn),qty,price_extended FROM #c ORDER BY sym, tDate, trn');
--JOIN to the source data to produce the inventory output
SELECT c.trn,
c.sym,
c.tDate,
c.qty as [Invoice Quantity],
c.price_unit,
c.price_extended as [Invoice Amount],
l.[qty] as [Inventory Quantity],
l.[amt] as [Inventory Cost]
FROM #c c
INNER JOIN #LIFO l
ON c.trn = l.[ID]
ORDER BY c.sym,
c.tDate,
c.trn;
This produces the following result.
| trn | sym | tDate | Invoice Quantity | price_unit | Invoice Amount | Inventory Quantity | Inventory Cost |
|---|---|---|---|---|---|---|---|
| 68130223 | ABC | 2013-02-23 | 238.00 | 12.78 | 3041.64 | 175 | 2236.5 |
| 41130310 | ABC | 2013-03-10 | 463.00 | 13.38 | 6194.94 | 371 | 4963.98 |
| 25130408 | ABC | 2013-04-08 | 298.00 | 12.98 | 3868.04 | 298 | 3868.04 |
| 48130430 | ABC | 2013-04-30 | 229.00 | 13.32 | 3050.28 | 38 | 506.16 |
| 49130508 | ABC | 2013-05-08 | 238.00 | 12.79 | 3044.02 | 238 | 3044.02 |
| 20130518 | ABC | 2013-05-18 | 298.00 | 13.23 | 3942.54 | 188 | 2487.24 |
| 7130722 | ABC | 2013-07-22 | 599.00 | 13.65 | 8176.35 | 292 | 3985.8 |
| 39130908 | ABC | 2013-09-08 | 490.00 | 12.69 | 6218.10 | 220 | 2791.8 |
| 15130307 | GHI | 2013-03-07 | 559.00 | 35.21 | 19682.39 | 68 | 2394.28 |
| 81131123 | GHI | 2013-11-23 | 187.00 | 33.86 | 6331.82 | 187 | 6331.82 |
| 51131206 | GHI | 2013-12-06 | 85.00 | 33.64 | 2859.40 | 85 | 2859.4 |
| 75131216 | GHI | 2013-12-16 | 500.00 | 33.55 | 16775.00 | 500 | 16775 |
| 13131221 | GHI | 2013-12-21 | 72.00 | 34.38 | 2475.36 | 72 | 2475.36 |
| 65131227 | GHI | 2013-12-27 | 376.00 | 33.49 | 12592.24 | 376 | 12592.24 |
| 53140102 | GHI | 2014-01-02 | 396.00 | 35.52 | 14065.92 | 318 | 11295.36 |
| 37130220 | XYZ | 2013-02-20 | 528.00 | 23.54 | 12429.12 | 528 | 12429.12 |
| 44130227 | XYZ | 2013-02-27 | 357.00 | 22.86 | 8161.02 | 153 | 3497.58 |
| 35130321 | XYZ | 2013-03-21 | 275.00 | 24.83 | 6828.25 | 275 | 6828.25 |
| 98130413 | XYZ | 2013-04-13 | 243.00 | 24.25 | 5892.75 | 243 | 5892.75 |
| 24130418 | XYZ | 2013-04-18 | 275.00 | 24.83 | 6828.25 | 275 | 6828.25 |
| 60130419 | XYZ | 2013-04-19 | 277.00 | 22.83 | 6323.91 | 277 | 6323.91 |
| 83130428 | XYZ | 2013-04-28 | 142.00 | 23.13 | 3284.46 | 142 | 3284.46 |
| 34130507 | XYZ | 2013-05-07 | 55.00 | 23.43 | 1288.65 | 55 | 1288.65 |
| 99130515 | XYZ | 2013-05-15 | 97.00 | 25.13 | 2437.61 | 97 | 2437.61 |
| 19130516 | XYZ | 2013-05-16 | 315.00 | 23.57 | 7424.55 | 315 | 7424.55 |
| 30130519 | XYZ | 2013-05-19 | 467.00 | 23.15 | 10811.05 | 207 | 4792.05 |
| 84140104 | XYZ | 2014-01-04 | 261.00 | 24.46 | 6384.06 | 8 | 195.68 |
| 78140112 | XYZ | 2014-01-12 | 332.00 | 24.28 | 8060.96 | 332 | 8060.96 |
| 80140126 | XYZ | 2014-01-26 | 404.00 | 24.23 | 9788.92 | 404 | 9788.92 |
| 82140131 | XYZ | 2014-01-31 | 548.00 | 23.65 | 12960.20 | 548 | 12960.2 |
| 66140203 | XYZ | 2014-02-03 | 459.00 | 23.70 | 10878.30 | 251 | 5948.7 |
| 31140205 | XYZ | 2014-02-05 | 42.00 | 24.39 | 1024.38 | 42 | 1024.38; |
Example #2
Using the same data as Example #1, we generate the ending inventory values without using the ROW_NUMBER() feature.
--Calculate the LIFO Values and store in temp table
SELECT n.sym,
cast(k.UID as int) as ID,
k.RowNumber,
k.qty,
k.amt
INTO #lifo
FROM
(SELECT DISTINCT sym FROM #c) n
CROSS APPLY wct.LIFOend('SELECT trn,NULL,qty,price_extended FROM #c where sym = ''' + n.sym
+ ''' ORDER BY tDate, trn'
) k;
--JOIN to the source data to produce the inventory output
SELECT c.trn,
c.sym,
c.tDate,
c.qty as [Invoice Quantity],
c.price_unit,
c.price_extended as [Invoice Amount],
l.[qty] as [Inventory Quantity],
l.[amt] as [Inventory Cost]
FROM #c c
INNER JOIN #LIFO l
ON c.trn = l.[ID]
AND c.sym = l.sym
ORDER BY c.sym,
c.tDate,
c.trn;
This produces the following result.
| trn | sym | tDate | Invoice Quantity | price_unit | Invoice Amount | Inventory Quantity | Inventory Cost |
|---|---|---|---|---|---|---|---|
| 68130223 | ABC | 2013-02-23 | 238.00 | 12.78 | 3041.64 | 175 | 2236.5 |
| 41130310 | ABC | 2013-03-10 | 463.00 | 13.38 | 6194.94 | 371 | 4963.98 |
| 25130408 | ABC | 2013-04-08 | 298.00 | 12.98 | 3868.04 | 298 | 3868.04 |
| 48130430 | ABC | 2013-04-30 | 229.00 | 13.32 | 3050.28 | 38 | 506.16 |
| 49130508 | ABC | 2013-05-08 | 238.00 | 12.79 | 3044.02 | 238 | 3044.02 |
| 20130518 | ABC | 2013-05-18 | 298.00 | 13.23 | 3942.54 | 188 | 2487.24 |
| 7130722 | ABC | 2013-07-22 | 599.00 | 13.65 | 8176.35 | 292 | 3985.8 |
| 39130908 | ABC | 2013-09-08 | 490.00 | 12.69 | 6218.10 | 220 | 2791.8 |
| 15130307 | GHI | 2013-03-07 | 559.00 | 35.21 | 19682.39 | 68 | 2394.28 |
| 81131123 | GHI | 2013-11-23 | 187.00 | 33.86 | 6331.82 | 187 | 6331.82 |
| 51131206 | GHI | 2013-12-06 | 85.00 | 33.64 | 2859.40 | 85 | 2859.4 |
| 75131216 | GHI | 2013-12-16 | 500.00 | 33.55 | 16775.00 | 500 | 16775 |
| 13131221 | GHI | 2013-12-21 | 72.00 | 34.38 | 2475.36 | 72 | 2475.36 |
| 65131227 | GHI | 2013-12-27 | 376.00 | 33.49 | 12592.24 | 376 | 12592.24 |
| 53140102 | GHI | 2014-01-02 | 396.00 | 35.52 | 14065.92 | 318 | 11295.36 |
| 37130220 | XYZ | 2013-02-20 | 528.00 | 23.54 | 12429.12 | 528 | 12429.12 |
| 44130227 | XYZ | 2013-02-27 | 357.00 | 22.86 | 8161.02 | 153 | 3497.58 |
| 35130321 | XYZ | 2013-03-21 | 275.00 | 24.83 | 6828.25 | 275 | 6828.25 |
| 98130413 | XYZ | 2013-04-13 | 243.00 | 24.25 | 5892.75 | 243 | 5892.75 |
| 24130418 | XYZ | 2013-04-18 | 275.00 | 24.83 | 6828.25 | 275 | 6828.25 |
| 60130419 | XYZ | 2013-04-19 | 277.00 | 22.83 | 6323.91 | 277 | 6323.91 |
| 83130428 | XYZ | 2013-04-28 | 142.00 | 23.13 | 3284.46 | 142 | 3284.46 |
| 34130507 | XYZ | 2013-05-07 | 55.00 | 23.43 | 1288.65 | 55 | 1288.65 |
| 99130515 | XYZ | 2013-05-15 | 97.00 | 25.13 | 2437.61 | 97 | 2437.61 |
| 19130516 | XYZ | 2013-05-16 | 315.00 | 23.57 | 7424.55 | 315 | 7424.55 |
| 30130519 | XYZ | 2013-05-19 | 467.00 | 23.15 | 10811.05 | 207 | 4792.05 |
| 84140104 | XYZ | 2014-01-04 | 261.00 | 24.46 | 6384.06 | 8 | 195.68 |
| 78140112 | XYZ | 2014-01-12 | 332.00 | 24.28 | 8060.96 | 332 | 8060.96 |
| 80140126 | XYZ | 2014-01-26 | 404.00 | 24.23 | 9788.92 | 404 | 9788.92 |
| 82140131 | XYZ | 2014-01-31 | 548.00 | 23.65 | 12960.20 | 548 | 12960.2 |
| 66140203 | XYZ | 2014-02-03 | 459.00 | 23.70 | 10878.30 | 251 | 5948.7 |
| 31140205 | XYZ | 2014-02-05 | 42.00 | 24.39 | 1024.38 | 42 | 1024.38; |
Example #3
In this example, we will look at a simple FX blotter. In FX trading, it is not at all unusual to switch from a long currency position to a short currency position during the course of a day. Additionally, if you are trading something like the US dollars against the Euro, if you are long US dollars, you are short the Euro. Thus, it's critical to pay attention to the sign. We will create the #fx table and populate it with some data.
--Create the temporary table
CREATE TABLE #fx
(
trn int,
ccy char(3),
amt_ccy money,
rate float,
ctr char(3),
amt_ctr money,
PRIMARY KEY (trn)
);
--Populate the table with some data
INSERT INTO #fx
VALUES
(101, 'GBP', 8000000, 1.619, 'USD', -12952000);
INSERT INTO #fx
VALUES
(102, 'GBP', -10000000, 1.62, 'USD', 16200000);
INSERT INTO #fx
VALUES
(103, 'GBP', -4000000, 1.613, 'USD', 6452000);
INSERT INTO #fx
VALUES
(104, 'GBP', 7000000, 1.618, 'USD', -11326000);
INSERT INTO #fx
VALUES
(105, 'GBP', 6000000, 1.623, 'USD', -9738000);
INSERT INTO #fx
VALUES
(106, 'GBP', -5000000, 1.618, 'USD', 8090000);
INSERT INTO #fx
VALUES
(107, 'GBP', -10000000, 1.602, 'USD', 16020000);
INSERT INTO #fx
VALUES
(108, 'GBP', 2000000, 1.608, 'USD', -3216000);
INSERT INTO #fx
VALUES
(109, 'GBP', -2000000, 1.602, 'USD', 3204000);
INSERT INTO #fx
VALUES
(110, 'GBP', 10000000, 1.626, 'USD', -16260000);
--Calculate the LIFO Values
SELECT cast(k.UID as int) as trn,
fx.amt_ccy as [GBP trade amount],
fx.amt_ctr as [USD trade amount],
k.qty as [GBP Position],
-k.amt as [USD Position]
FROM wct.LIFOend('SELECT trn,NULL,amt_ccy,-amt_ctr FROM #fx ORDER BY trn') k
INNER JOIN #fx fx
ON cast(k.UID as int) = fx.trn;
This produces the following result.
| trn | GBP trade amount | USD trade amount | GBP Position | USD Position |
|---|---|---|---|---|
| 110 | 10000000.00 | -16260000.00 | 2000000 | -3252000 |
Example #4
In this example we require multiple columns to uniquely identify the inventory items and there are no unique transaction identifiers.
--Put some data into #p
SELECT *
INTO #p
FROM ( VALUES ('ZYX', 'Coffee Mug', 'Ceramic', '8 oz', 'Black', '2017-01-15', 50, 1.12, 56),
('ZYX', 'Coffee Mug', 'Ceramic', '12 oz', 'Black', '2017-01-15', 50, 1.12, 56),
('ZYX', 'Coffee Mug', 'Ceramic', '16 oz', 'Black', '2017-01-15', 50, 1.12, 56),
('ZYX', 'Coffee Mug', 'Ceramic', '20 oz', 'Black', '2017-01-15', 50, 1.12, 56),
('ZYX', 'Coffee Mug', 'Ceramic', '8 oz', 'White', '2017-01-15', 50, 1.12, 56),
('ZYX', 'Coffee Mug', 'Ceramic', '12 oz', 'White', '2017-01-15', 50, 1.12, 56),
('ZYX', 'Coffee Mug', 'Ceramic', '16 oz', 'White', '2017-01-15', 50, 1.12, 56),
('ZYX', 'Coffee Mug', 'Ceramic', '20 oz', 'White', '2017-01-15', 50, 1.12, 56),
('ZYX', 'Coffee Mug', 'Plastic', '8 oz', 'Black', '2017-01-15', 50, 0.89, 44.5),
('ZYX', 'Coffee Mug', 'Plastic', '12 oz', 'Black', '2017-01-15', 50, 0.89, 44.5),
('ZYX', 'Coffee Mug', 'Plastic', '16 oz', 'Black', '2017-01-15', 50, 0.89, 44.5),
('ZYX', 'Coffee Mug', 'Plastic', '20 oz', 'Black', '2017-01-15', 50, 0.89, 44.5),
('ZYX', 'Coffee Mug', 'Plastic', '8 oz', 'White', '2017-01-15', 50, 0.89, 44.5),
('ZYX', 'Coffee Mug', 'Plastic', '12 oz', 'White', '2017-01-15', 50, 0.89, 44.5),
('ZYX', 'Coffee Mug', 'Plastic', '16 oz', 'White', '2017-01-15', 50, 0.89, 44.5),
('ZYX', 'Coffee Mug', 'Plastic', '20 oz', 'White', '2017-01-15', 50, 0.89, 44.5),
('ZYX', 'Coffee Mug', 'Ceramic', '8 oz', 'Black', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Ceramic', '12 oz', 'Black', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Ceramic', '16 oz', 'Black', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Ceramic', '20 oz', 'Black', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Ceramic', '8 oz', 'White', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Ceramic', '12 oz', 'White', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Ceramic', '16 oz', 'White', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Ceramic', '20 oz', 'White', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Plastic', '8 oz', 'Black', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Plastic', '12 oz', 'Black', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Plastic', '16 oz', 'Black', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Plastic', '20 oz', 'Black', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Plastic', '8 oz', 'White', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Plastic', '12 oz', 'White', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Plastic', '16 oz', 'White', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Plastic', '20 oz', 'White', '2017-02-15', 50, 1.15, 57.5),
('ZYX', 'Coffee Mug', 'Plastic', '8 oz', 'White', '2017-01-16', -5, 2.95, -14.75),
('ZYX', 'Coffee Mug', 'Plastic', '8 oz', 'White', '2017-01-23', -2, 2.95, -5.9),
('ZYX', 'Coffee Mug', 'Plastic', '8 oz', 'White', '2017-01-24', -1, 2.95, -2.95),
('ZYX', 'Coffee Mug', 'Plastic', '8 oz', 'White', '2017-02-01', -3, 2.95, -8.85),
('ZYX', 'Coffee Mug', 'Plastic', '8 oz', 'White', '2017-02-08', -7, 2.95, -20.65),
('ZYX', 'Coffee Mug', 'Plastic', '8 oz', 'White', '2017-02-15', -6, 2.95, -17.7),
('ZYX', 'Coffee Mug', 'Plastic', '8 oz', 'White', '2017-02-22', -2, 2.95, -5.9)) n ([Manufacturer],
[Description],
[Material],
[Size], [Color],
[Date], [qty],
[unit price],
[extended price]);
--Copy the #p data into #p2 and put it in order for LIFO processing
SELECT IDENTITY(int, 1, 1) as ID,
ROW_NUMBER() OVER (PARTITION BY [Manufacturer],
[Description],
[Material],
[Size],
[Color]
ORDER BY [Manufacturer],
[Description],
[Material],
[Size],
[Color],
[Date],
qty DESC) as rn,
#p.*
INTO #p2
FROM #p
ORDER BY [Manufacturer],
[Description],
[Material],
[Size],
[Color],
[Date],
qty DESC;
--Put the tvf results into the #lifo table
SELECT CAST([UID] as Int) as ID,
RowNumber,
qty,
amt
INTO #lifo
FROM wct.LIFOend('SELECT ID, rn,qty,[extended price] FROM #p2 ORDER BY ID');
--JOIN to the source data to produce the inventory output
SELECT #p2.[Manufacturer],
#p2.[Description],
#p2.[Material],
#p2.[Size],
#p2.[Color],
#p2.[Date],
#p2.[qty] as [Invoice Quantity],
#p2.[unit price],
#p2.[extended price] as [Invoice Amount],
l.[qty] as [Inventory-On-Hand],
l.[amt] as [Inventory Cost]
FROM #p2
INNER JOIN #LIFO l
ON #p2.ID = l.[ID]
ORDER BY #p2.id;
This produces the following result.
| Manufacturer | Description | Material | Size | Color | Date | Invoice Quantity | unit price | Invoice Amount | Inventory-On-Hand | Inventory Cost |
|---|---|---|---|---|---|---|---|---|---|---|
| ZYX | Coffee Mug | Ceramic | 12 oz | Black | 2017-01-15 | 50 | 1.12 | 56.00 | 50 | 56 |
| ZYX | Coffee Mug | Ceramic | 12 oz | Black | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Ceramic | 12 oz | White | 2017-01-15 | 50 | 1.12 | 56.00 | 50 | 56 |
| ZYX | Coffee Mug | Ceramic | 12 oz | White | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Ceramic | 16 oz | Black | 2017-01-15 | 50 | 1.12 | 56.00 | 50 | 56 |
| ZYX | Coffee Mug | Ceramic | 16 oz | Black | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Ceramic | 16 oz | White | 2017-01-15 | 50 | 1.12 | 56.00 | 50 | 56 |
| ZYX | Coffee Mug | Ceramic | 16 oz | White | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Ceramic | 20 oz | Black | 2017-01-15 | 50 | 1.12 | 56.00 | 50 | 56 |
| ZYX | Coffee Mug | Ceramic | 20 oz | Black | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Ceramic | 20 oz | White | 2017-01-15 | 50 | 1.12 | 56.00 | 50 | 56 |
| ZYX | Coffee Mug | Ceramic | 20 oz | White | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Ceramic | 8 oz | Black | 2017-01-15 | 50 | 1.12 | 56.00 | 50 | 56 |
| ZYX | Coffee Mug | Ceramic | 8 oz | Black | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Ceramic | 8 oz | White | 2017-01-15 | 50 | 1.12 | 56.00 | 50 | 56 |
| ZYX | Coffee Mug | Ceramic | 8 oz | White | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Plastic | 12 oz | Black | 2017-01-15 | 50 | 0.89 | 44.50 | 50 | 44.5 |
| ZYX | Coffee Mug | Plastic | 12 oz | Black | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Plastic | 12 oz | White | 2017-01-15 | 50 | 0.89 | 44.50 | 50 | 44.5 |
| ZYX | Coffee Mug | Plastic | 12 oz | White | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Plastic | 16 oz | Black | 2017-01-15 | 50 | 0.89 | 44.50 | 50 | 44.5 |
| ZYX | Coffee Mug | Plastic | 16 oz | Black | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Plastic | 16 oz | White | 2017-01-15 | 50 | 0.89 | 44.50 | 50 | 44.5 |
| ZYX | Coffee Mug | Plastic | 16 oz | White | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Plastic | 20 oz | Black | 2017-01-15 | 50 | 0.89 | 44.50 | 50 | 44.5 |
| ZYX | Coffee Mug | Plastic | 20 oz | Black | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Plastic | 20 oz | White | 2017-01-15 | 50 | 0.89 | 44.50 | 50 | 44.5 |
| ZYX | Coffee Mug | Plastic | 20 oz | White | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Plastic | 8 oz | Black | 2017-01-15 | 50 | 0.89 | 44.50 | 50 | 44.5 |
| ZYX | Coffee Mug | Plastic | 8 oz | Black | 2017-02-15 | 50 | 1.15 | 57.50 | 50 | 57.5 |
| ZYX | Coffee Mug | Plastic | 8 oz | White | 2017-01-15 | 50 | 0.89 | 44.50 | 32 | 28.48 |
| ZYX | Coffee Mug | Plastic | 8 oz | White | 2017-02-15 | 50 | 1.15 | 57.50 | 42 | 48.3 |
See Also
FIFO - Calculate FIFO (first in, first out) values in an ordered resultant table.
FIFOend - Calculate the ending FIFO balances in an ordered resultant table.
FIFOtvf - Calculate running FIFO (First In, First Out) values in an ordered resultant table.
LIFO - Calculate LIFO (last in, first out) values in an ordered resultant table.
LIFOtvf - Calculate the running LIFO balances in an ordered resultant table.
WAC - Calculate running weighted average cost in an ordered resultant table.
WACtvf - Calculate running weighted-average cost values in an ordered resultant table.