-2

I have data like this:

Id Price Quantity Value
1   1000    4      4000
2   1000    4.5    4500
3   1000    5      5000

and I would ask for database row when the price is the sum of for example 2500.

I expected to answer this:

Id Price Quantity Value
1  1000     4      4000
2  1000     4.5    4500
3   500     5      2500

Of could I could use while loop but I think select will be more smart. My database is Firebird.

I have 3 lines in database for example buy dollars.

  • first, I bought $ 1000 for 4 PLN,
  • second I bought $ 1000 for 4.5 PLN,
  • and the third I bought $ 1000 for 5 PLN.

(PLN is Polish currency).

And now I would like to sell $ 2500 and of course I would like know how much is dollars cost in PLN.

In my opinion this transaction cost: (1000 * 4) + (1000 * 4.5) + (500 * 5) = 12000 PLN.

This is very important to know how much dollars is cost in PLN to Polish accountant.

MartynA
  • 30,454
  • 4
  • 32
  • 73
  • 1
    I have no idea what you're asking. If your data for row 3 is price 1000, quantity 5, then value should be 5000. Why would you want it to be only 2500? Why would you expect its value to be half of what quantity * price should be? When I have four coins in my pocket and they're valued at 25 cents (.25 dollars), I expect the four of them together to equal a whole dollar, not half of one. – Ken White Jan 23 '17 at 23:06
  • `select Id, Price, Quantity, (Price * Quantity) as 'Value' from MyTable` ? – Jerry Dodge Jan 23 '17 at 23:31
  • @Ken In the "expected" example, the `Price` field is `500`, not `1000`. The math looks correct to me. – Jerry Dodge Jan 23 '17 at 23:33
  • @Jerry: Look at the first block of data, where it says *Price 1000 Quantity 5 Value 5000*, which is absolutely correct as well. Where does the price change from 1000 (top example) to 500 (bottom value)? And if you can't tell how or why that value should be cut in half, then you can't explain how to write any SQL. – Ken White Jan 23 '17 at 23:36
  • @Ken I understand it as OP wishes for the `Value` field to be equal to `Price` * `Quantity`. So, if the `Price` were to change, the query will return the appropriate product for `Value`. – Jerry Dodge Jan 23 '17 at 23:37
  • @Jerry: That's one **guess** what the poster might be asking. If that's the case, there is absolutely no need to have a `VALUE` column at all, as it can be calculated at the time of the query execution. But based on what is actually written in the post, I'm not sure enough your guess is right to write an answer. Better to have the poster [edit] the question to make it clear than to start posting SWAGs that may (or may not) have anything to do with the question. – Ken White Jan 23 '17 at 23:39
  • @Ken Hence the comment rather than an answer, and saying "I understand..." rather than "It is...". My guess is just as plausible as your guess. – Jerry Dodge Jan 23 '17 at 23:42
  • @KenWhite my interpretation of his question is that he's asking for how to construct a Where clause based on the value of (price * qty) because he said "ask for database row".. – John Easley Jan 23 '17 at 23:43
  • I have 3 line in database for example, dollars. First, I bought $ 1000 for 4 PLN, second I bought $ 1000 for 4.5 PLN, and the third I bought $ 1000 for 5 PLN. And now I sell $ 2500 and of course I would like know how much is dollars cost in PLN. – Robert Zawadzki Jan 23 '17 at 23:52
  • @RobertZawadzki what is PLN? – John Easley Jan 24 '17 at 00:04
  • PLN = Polish currency – Robert Zawadzki Jan 24 '17 at 00:11
  • 1
    Don't add details in comments. Instead, [edit] your question and add it there where people can see them. When you do, remember we can't see your screen or read your mind, so you need to explain clearly what you're asking. – Ken White Jan 24 '17 at 01:05
  • I've been trying to work out why in your example, it's the row with ID = 3 that you change - the round numbers you have used make it very hard to understand what exactly you are trying to illustrate. Your title contains "fifo" (= first in, first out). If it said "lifo" (= **last** in, first out), then it would make sense that you want to answer the q"how much did X dollars cost me?" by considering your most recent purchase (ID = 3). But if you want to sell more dollars than in row ID = 3, what happens then? – MartynA Jan 24 '17 at 09:30
  • "very important to know how much dollars is cost in PLN to Polish accountent" Indeed. But the first thing you need to know is exactly how the accountant needs the calculation to be done: It could be based on the average cost or on the oldest purchase(s) or the newest, and he ought to know which calculation is the correct one to use. – MartynA Jan 24 '17 at 13:10
  • Of course, I know how my accountent calculations. They use fifo method. In this example I buy money in the order like ID column and then as I will want to sell it I use fifo method again. I'll take a thousand from ID 1, next thousand from ID 2 and 500 from ID 3. And I would like to select with this score in one query to database. On the end I calculated average cost of money. – Robert Zawadzki Jan 24 '17 at 14:15
  • (1000 * 4) + (1000 * 4.5) + (500 * 5) = 11000 PLN and not 12000 as you wrote. It is not logical to ask for sum of Price because Price is for 1 item (Quantity) and there is not point of suming Prices. Logical would be to ask for first 11 dollars or for first 11000 PLN. – Daniel Vidić Jan 24 '17 at 15:27
  • my mistake :) if you have 3000 dollars and someone ask you about price for 2500 dollars what do you say? You say 5 PLN for dollars? Maybe 4.5 PLN for dollars or 4 PLN? I know 5 PLN will be the best but you don't have any buyer for this price. Unfortunatelly you must calculate price using fifo (tax law) and 11000 PLN / 2500 USD = 4.4 PLN + 1% (margin) per USD will be the best offer. More then 90% exchange points in Poland have 1% margin. – Robert Zawadzki Jan 24 '17 at 16:27
  • You really ought to rewrite your question to use a different price for each purchase and specify the **exact** algorithm that you want to use to calculate the purchase cost of the USD you want to sell. – MartynA Jan 24 '17 at 17:14
  • There are a lot of comments here. I think since the OP mentioned FIFO (first-in first-out), he is imagining something like inventory bought at different times with different prices. Now someone comes in and buys 2500 units. How much did those units cost using FIFO accounting. He says $2500 but I think he means 2500 units. So can you write a query that will tell him what the oldest 2500 units cost? I think it might take a loop. – jrodenhi Jan 24 '17 at 22:11
  • These are for SqlServer, but you might take a look at http://stackoverflow.com/questions/22936112/fifo-implementation-in-inventory-using-sql and https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1d6795ff-4df9-477d-aff9-486538343617/sql-fifo-query?forum=transactsql – MartynA Jan 25 '17 at 09:47

2 Answers2

3

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:

  1. Find the lowest ID of a row whose cumulativecost exceeds the dollar amount to sell.

  2. 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.

  3. 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;
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
MartynA
  • 30,454
  • 4
  • 32
  • 73
  • @RobertZawadzki: Hope it helps. At the moment, I'm struggling with converting it to Interbase/Firebird – MartynA Jan 25 '17 at 14:52
  • @RobertZawadzki: Actually, I'm going to give up on translating my SQL code to Firebird. There are just too many apparent restrictions on what you can and can't do in Firebird Sql. Maybe you could post how to translate it as a new q. – MartynA Jan 25 '17 at 16:10
  • it doesn't matter, just you do more than I expected. Thank you very much – Robert Zawadzki Jan 25 '17 at 19:40
  • @RobertZawadzki: I decided to carry on with my port to Firebird because it bugged me how hard I found it. I got there in the end, so I've added it to my answer. – MartynA Jan 27 '17 at 14:21
0

Here is example of Delphi code, Q is TQuery, other variables are Real. You should add correct rounding depending on law regulations in Poland.

USD := 2500; // how much dollars
PLN := 0; // used to store PNL
USDTemp := 0; // temporary variable
Q.Sql.Add('Select * from Table1 WHERE ... ORDER BY ID'); // all awailable dollars
Q.Open;
while not Q.EOF and (USDTemp < USD) do // while there is dollars and not to much
begin
  if USDTemp+Q.FieldByName('USD').AsFloat <= USD then // if will not excede needed dollars 
  begin
    PLN := PLN + Q.FieldByName('USD').AsFloat;
    USDTemp := USDTemp + Q.FieldByName('USD').AsFloat;
  end
  else // only part of available dollars is needed
  begin 
    PLN := PLN + ((USD-USDTemp)/Q.FieldByName('USD').AsFloat); 
    USDTemp := USD;
  end;
  Q.next;
end;
Q.Close;
ExchangeRate := PLN/USDTemp; // dividing with USDTemp because maybe there was not enough dollars, result is exchange rate, and then you add margin
Daniel Vidić
  • 164
  • 2
  • 11
  • This answer is incomplete, I think. It would use the same dollar purchases over and over, as you don't remove the sales amounts from the table. – MartynA Jan 24 '17 at 18:30
  • OP already said he could "loop", he's asking for an SQL solution. – John Easley Jan 24 '17 at 20:16
  • Q.next was missing. SQL solution is no a way to go because logic and possible specal cases are to complicated. It is probably possible with stored procedure. – Daniel Vidić Jan 24 '17 at 20:30
  • Hi Daniel, I know how is do in delphi but thanks for your work. I expected sql query but maybe in delphi will be easier. Thanx again – Robert Zawadzki Jan 25 '17 at 09:19