The following shows the essentials of how to operate a FIFO model of dollar purchases and sales in SQL. I've written and tested it for MS SQL Server translated it into Firebird SQL and tested it
So, start with a DollarPurchases table as follows
CREATE TABLE [dbo].[DollarPurchases](
[ID] [int] NULL,
[Price] [float] NULL,
[Quantity] [float] NULL,
[Cost] AS ([Price]*[Quantity])
)
and add a few rows to it
insert dollarpurchases(id, price, quantity) values (1, 1000, 4)
insert dollarpurchases(id, price, quantity) values (2, 1100, 4.5)
insert dollarpurchases(id, price, quantity) values (3, 1500, 5)
Then, we can create a view which includes a running total
create view vwcosts as
select
* ,
cumulativecost = (select sum(Cost) from dollarpurchases p2 where p2.id <= p1.id)
from
dollarpurchases p1
The contents of the view would look like this
ID Price Quantity Cost cumulativecost
1 1000 4 4000 4000
2 1100 4.5 4950 8950
3 1500 5 7500 16450
Now, suppose we want to sell a certain amount of dollars. An algorithm to do this might be as follows, assuming the ID column value reflects the order in which the purchases were made:
Find the lowest ID of a row whose cumulativecost exceeds the dollar amount to sell.
Flag or delete all the rows with a lower ID, as they will need to be sold in their entirety to realise the dollar amount.
The dollars in the row with the found ID will need to be sold in whole or in part to realise the remainder of the dollar amount. If it needs to be the whole, delete or flag this row as per step 1, if in part, update the row to reflect the residual quantity of dollars and their cost.
and that's it.
In the following, instead of doing these operations on the live data, I'm going to make a copy of the DollarPurchases and perform the operations on that. I've left a few debugging statements in the code for checking purposes.
-- declare some script variables to use
declare
@dollarstosell float,
@highestrowtosell int,
@dollarsremaining float
select
@dollarstosell = 8000
select * into purchasescopy from dollarpurchases -- create copy of purchases table
select @highestrowtosell = (select min(id) from vwcosts where cumulativecost > @dollarstosell)
select @highestrowtosell -- for debugging
-- calculate how many dollars will remain in the row which will be partially sold
select @dollarsremaining = (select cumulativecost from vwcosts where id = @highestrowtosell) - @dollarstosell
select @dollarsremaining -- for debugging
-- remove the rows which will be sold in toto
delete from purchasescopy where id < @highestrowtosell
--update the row which will be partially sold
update purchasescopy set quantity = @dollarsremaining / price, cost = @dollarsremaining where id = @highestrowtosell
select * from purchasescopy
-- following are optional to tidy up
drop view vwcosts
drop table purchasescopy
This yields
ID Price Quantity Cost
2 1100 0.86 950
3 1500 5 7500
Of course, the above only addresses the case where the highest relevant row is only partially sold, but it would be trivial to deal with it as per step 1 if it is being sold in its entirety.
I imagine that a real SQL expert could do all the above in a single SQL statement, but I hope this step-by-step approach makes it easier to follow what's going on and to debug it.
It's worth mentioning that this could all be done processing the data row-by-row
using a SQL cursor in a while loop, but maybe that's a bit too much like the Delphi code example you've been given in the other answer.
A translation of the above into Firebird SQL is shown below. It has two main changes compared to the MS SQL Server version:
Ihe computed cost column is renamed to avalue (it would have been value but for a naming conflict).
As Firebird SQL does not support the free use of local variables in the way TransactSQL does, I replaced the variable by entries in a single-row variables table. This makes some of the statements a bit wordier but is preferable from my pov to doing it with Firebird's EXECUTE BLOCK.
The code:
create table dollarpurchases(id int, price float, quantity float, avalue computed by (price*quantity));
create table purchasescopy(id int, price float, quantity float);
create view vwDollarPurchases as select p1.*, (select sum(avalue) from dollarpurchases p2 where p2.id <= p1.id) as cumulativevalue from dollarpurchases p1;
create table variables(ID int, dollarstosell float, highestrowtosell int, dollarsremaining float);
insert into dollarpurchases(id, price, quantity) values (1, 1000, 4);
insert into dollarpurchases(id, price, quantity) values (2, 1100, 4.5);
insert into dollarpurchases(id, price, quantity) values (3, 1500, 5);
insert into variables(ID, dollarstosell, highestrowtosell, dollarsremaining)
values(1, 8000, 0, 0);
insert into purchasescopy(id, price, quantity) select id, price, quantity from dollarpurchases;
update variables set highestrowtosell = (select min(id) from VWDOLLARPURCHASES where cumulativevalue > dollarstosell) where id = 1;
update variables v1 set v1.dollarsremaining = (select distinct v2.cumulativevalue from VWDOLLARPURCHASES v2 where v2.id = v1.highestrowtosell) - v1.dollarstosell where v1.id = 1;
delete from purchasescopy where id < (select highestrowtosell from variables where id = 1);
update purchasescopy set quantity = (select dollarsremaining from variables where id = 1) / price where id = (select highestrowtosell from variables where id = 1);
select * from purchasescopy;
