Please help where you can. I have tried to be clear in my explanation but will clarify if needed.
I am trying to track the SALE history for people and calculate the value of each SALE.
In the example below someone buys a quantity of 100, then buys another 40, then sells 75, then sells 30. The BUYs and SELLs are in one table but I break them into two tables as I think this is clearer but it doesn't have to be.
I have TABLE A for a single CLIENT ID. The table has two records, 5 fields in each record. 1. CLIENT ID = 1, AMOUNT = 100, MOVEMENT = 'BUY', DATE = 01/01/2000, PRICE = $15 2. CLIENT ID = 1, AMOUNT = 40, MOVEMENT = 'BUY', DATE = 01/02/2000, PRICE = $30
I have TABLE B for a single CLIENT ID. The table has two records, 5 fields in each record. 1. CLIENT ID = 1, AMOUNT = 75, MOVEMENT = 'SELL', DATE = 01/03/2000, PRICE = $20 2. CLIENT ID = 1, AMOUNT = 30, MOVEMENT = 'SELL', DATE = 01/04/2000, PRICE = $55
What I want to do is track the 'SELL' history and find out which 'BUY' each SELL relates to and the value of each SELL. The idea is that my output is DRIVEN by the existance of a SELL record.
Below are the conditions to the creation of an output record
1. To find out which BUY I join to I need to sort the BUYS by PRICE descending. So in my example the first SELL relates to the SECOND BUY as the second BUY has the highest PRICE of all BUYS for this CLIENT ID.
2. If there is a remainder when substracting a SELL amount from a BUY amount then I need the value of the remainder calculated too.
3. Also the SALE date has to occur after a BUY date. (i.e. SALE date >= BUY date) There are some cases where the transaction history in date order could be BUY, SELL, SELL, BUY, SELL, SELL. When querying the first SELL it cannot look at the second BUY when producing a result as the BUY date was after the SELL date. Similarly the third SELL cannot look at the second BUY when producing a result as the BUY date was after the SELL date.
EXAMPLE 1. I look at the first SELL (chosen in ascending DATE order) which is an amount = 75
2. I want to find the BUY that the SELL relates to. I do this by selecting the BUY with the highest PRICE which is BUY record number 2 as it has a PRICE = $30 and an amount = 40
3. So I join first SELL (75) to second BUY (40) but the amount of the BUY is only (40) so I still need to work out what to do with the remaining SELL (35) that the second BUY does not cover. I believe that the easiest way to resolve this is to make a new record with the remaining SELL (35) as a new record (with the same CLIENT ID, MOVEMENT, DATE and PRICE information as the SELL record it came from). Unless you can suggest something else!
4. Since there is a remainder I need to resolve this SELL BEFORE I move onto SELL number 2.
5. Thefore for remainding SELL (35) I want to find the BUY that the SELL relates to. I do this by selecting the BUY with the highest PRICE that HAS NOT already been "USED UP" by PREVIOUS SELLS. Given that BUY record number 2 has already been used up, I need to join to the next BUY record (sorted by PRICE descending). This should give me BUY record 1 as it has a PRICE = $15 and an amount = 100.
6. That resolves the remainder for the SELL. Now I have a remainder for the BUY of 65. If there are no other SELLS then I can stop as this work is all driven by the existance of SELLS. However in my example there is still SELL number 2 from my original table above which has not yet been queried.
7. I look at this next SELL (chosen in ascending DATE order) which is an amount = 30.
8. For this SELL (30) I want to find the BUY that the SELL relates to. I do this by selecting the BUY with the highest PRICE that HAS NOT already been "USED UP" by PREVIOUS SELLS. BUY record 1 and 2 have both been used but there is a BUY record with a REMAINDER to use up with an amount = 65. I believe that the easiest way to resolve this is to make a new record with the remaining BUY (65) as a new record (with the same CLIENT ID, MOVEMENT, DATE and PRICE information as the BUY record it came from). Unless you can suggest something else!
9. The SELL(30) therefore joins to BUY REMAINDER (65). This still leaves a BUY REMAINDER (65) but there are no more SELL records for this CLIENT therefore the query can end.
Using my idea of creating a new record whenever there is a remainder, a would expect the following output. This may of course change if you can think of a better method.
Expected output is three rows returning everything from both the BUY and SELL tables.
So that's it. I think I have included all steps now. I really wish to solve this but am at my skill limit. At a high level I think I need to query the SELL table a join to the BUY table, then update the BUY table with any BUY REMAINDER so that when the next SELL record attempts to join to the BUY table it JOINS to the BUY REMAINDER record rather than the next BUY record. If that makes sense haha!
I am looking at stored procedures using a loop of some sort (but am not experienced at the application of them). I tried using recursive statements but since discovered that you cannot use derived tables etc in these. I couldn't get it working using a recursive statement without them so that idea didn't work. I keep coming back to this idea of looping until there is no REMAINDER then move onto the next BUY or SELL record for the CLIENTID.
For those that are going to attempt it I thought I would give some tables to help test their theories! :)
CREATE VOLATILE TABLE BUY ,NO FALLBACK ,CHECKSUM = DEFAULT ,NO LOG ( BUY_CLIENTID INTEGER ,BUY_AMOUNT INTEGER ,BUY_MOVEMENT CHAR(4) ,BUY_DATE DATE FORMAT 'YY/MM/DD' ,BUY_PRICE DECIMAL(10,2) ) PRIMARY INDEX (CLIENTID) ON COMMIT PRESERVE ROWS ;