I have one table cryptotransactionledger in below structure where transactions are stored.

TRANSACTION_TYPEID is foreign_key from TRANSACTIONTYPE table (master table).

How can I write a query to implement below rules

1. bitcoin-transferred should only be deducted from bitcoin-mined
2. bitcoin-lost can be deducted from either bitcoin-received or bitcoin-mined in FIFO manner

Below is the expected result

Logic for remaining balance:

In the cryptotransactionledger table, the first debit is row no 3 which is bitcoin-transferred of amount -5. In FIFO model, we need to deduct it from the first eligible credit. Since bitcoin-transferred cannot be reduced from bitcoin-received (row no 1), so, it looks for the next credit which is row no 2, bitcoin-mined. Hence, -5 will be taken from row no 2 and respective amount becomes 20 - 5 = 15.

Now it looks for the next debit which is row no 4 i.e. bitcoin-lost of 10 points. Based on FIFO model it looks for the first credit with remaining balance which is row no 1 (Bitcoin received 5). Bitcoin Lost can be deduced from received so it tries to reduce -10 from row no 1 and available amount will be taken out (which is 5). Remaining 5 will be deducted from the next credit (FIFO) and it gets deducted from row no 2. In the previous step row 2 was updated to 15 from 20, so again now 5 will be deducted from row no 2 and it becomes 10. At this point row no 1 is 0 and row no 2 is 10.

After that it looks for the next debit which is Lost -16. This gets deducted from row no 2 since that’s the next available credit with balance. Amount available is 10 and it’s deducted and updated to 0. Remaining 6 will be deducted from next available credit which is row no 5. 55 - 6 becomes 49. At this point, row 1 amount is 0, row 2 amount is 0 and row 5 amount is 49.

Next debit is -5 but it is Bitcoin transferred. Even though we’ve a balance of 49 for row no 5 now, we cannot deduct it from there because Bitcoin transferred cannot be deducted from Bitcoin received. Hence it looks for the next credit which is row no 6 (Bitcoin-mined amount 8) and so 5 gets deducted from here. At this point row 1 amount is 0, row 2 amount is 0, row 5 amount is 49, row 6 amount is 3.

UPDATED Suggested query in the answers gives me result as below (Db Fiddle) which is close to my expected result

Thanks in advance

CodePudding user response：

Assumptions:

1. We can't mine or receive less than 0.
2. We can't transfer or lose amounts we don't have.

It wasn't clear what the FIFO behavior involves. A better test case might have helped there.

Here's an updated test case with the above data and then a slightly larger data set, plus a solution which attempts to introduce FIFO logic:

The updated test case with more data and FIFO logic

The following solution uses a few calculations to do the work.

In cte1 we derive:

1. run_mined - the running sum (in id order) of type = 102 (mined amounts)
2. tot_xfer - the overall total of type = 103 (transferred amounts)
3. tot_lost - the overall total of type = 104 (lost amounts)

Then since transferred amounts can only be deducted from mined amounts, we do this next in cte2, adjusting amounts of mined rows.

If the total transfer sum is greater than the current running sum of a mined row, that amount is reduced to 0. We've transferred all this amount.

If the total transfer sum is not greater than the current running sum of mined data, we deduct the transferred amount, no greater than the current mined amount for this row.

Any subsequent mined row is not touched, since there are no further transfers.

In cte1b, we calculate run2_in, which is the updated running sum of both mined and received amounts. Note that mined amounts were adjusted in cte2.

cte3 now performs a calculation similar to cte2, but this time adjusts both types received and mined (101 and 102) in FIFO order, based on the total remaining lost amount.

Finally, we select only the fully adjusted received and mined rows to display, along with the corresponding id to indicate the order in which the FIFO operations were performed.

The SQL:

WITH cte1 AS (
         SELECT a.*
              , SUM(CASE WHEN (transaction_typeid = 102) THEN 1 ELSE 0 END * amount) OVER (ORDER BY id) AS run_mined   
              , SUM(CASE WHEN (transaction_typeid = 103) THEN 1 ELSE 0 END * amount) OVER ()            AS tot_xfer    
              , SUM(CASE WHEN (transaction_typeid = 104) THEN 1 ELSE 0 END * amount) OVER ()            AS tot_lost    
           FROM cryptotransactionledger a
          ORDER BY id
     )
   , cte2 AS (
         SELECT a.id, a.transaction_typeid,  a.transaction_name
              , CASE WHEN transaction_typeid <> 102        THEN amount
                     WHEN run_mined  <= ABS(tot_xfer )     THEN 0
                     WHEN run_mined    tot_xfer  >= amount THEN amount
                                                           ELSE run_mined    tot_xfer 
                 END AS amount
              , run_mined 
              , tot_xfer 
              , tot_lost 
              , amount AS amount1
           FROM cte1 a
     )
   , cte1b AS (
         SELECT a.*
              , SUM(CASE WHEN (transaction_typeid IN (101, 102)) THEN 1 ELSE 0 END * amount) OVER (ORDER BY id) AS run2_in     
           FROM cte2 a
     )
   , cte3 AS (
         SELECT a.id, a.transaction_typeid,  a.transaction_name
              , CASE WHEN transaction_typeid NOT IN (101, 102) THEN amount
                     WHEN run2_in    <= ABS(tot_lost )         THEN 0
                     WHEN run2_in      tot_lost  >= amount     THEN amount
                                                               ELSE run2_in      tot_lost 
                 END AS amount
              , run_mined 
              , tot_xfer 
              , tot_lost 
              , run2_in
              , amount1
              , amount AS amount2
           FROM cte1b a
     )
SELECT id, transaction_name, amount
  FROM cte3
 WHERE transaction_typeid IN (101, 102)
 ORDER BY id
;

Result using data from the original question (the trivial case):

 ---- ------------------ -------- 
| id | transaction_name | amount |
 ---- ------------------ -------- 
|  1 | bitcoin-received |      0 |
|  2 | bitcoin-mined    |     10 |
 ---- ------------------ -------- 

In the updated fiddle, an example with more data is provided:

The new data:

create table cryptotransactionledger as
    select  1 as id, 101 as transaction_typeid, 'bitcoin-received'    as transaction_name,   5 as amount from dual union all
    select  2 as id, 102 as transaction_typeid, 'bitcoin-mined'       as transaction_name,  20 as amount from dual union all
    select  3 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -5 as amount from dual union all
    select  4 as id, 104 as transaction_typeid, 'bitcoin-lost'        as transaction_name, -10 as amount from dual union all
    select  5 as id, 101 as transaction_typeid, 'bitcoin-received'    as transaction_name,  55 as amount from dual union all
    select 15 as id, 102 as transaction_typeid, 'bitcoin-mined'       as transaction_name,   8 as amount from dual union all
    select 16 as id, 102 as transaction_typeid, 'bitcoin-mined'       as transaction_name,  20 as amount from dual union all
    select 17 as id, 102 as transaction_typeid, 'bitcoin-mined'       as transaction_name,  30 as amount from dual union all
    select 18 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -5 as amount from dual union all
    select 19 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -5 as amount from dual union all
    select 20 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -5 as amount from dual union all
    select 30 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -5 as amount from dual union all
    select 31 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -4 as amount from dual union all
    select 40 as id, 104 as transaction_typeid, 'bitcoin-lost'        as transaction_name, -16 as amount from dual union all
    select 99 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -5 as amount from dual WHERE 1 = 0
;

The result:

 ---- ------------------ -------- 
| id | transaction_name | amount |
 ---- ------------------ -------- 
|  1 | bitcoin-received |      0 |
|  2 | bitcoin-mined    |      0 |
|  5 | bitcoin-received |     34 |
| 15 | bitcoin-mined    |      0 |
| 16 | bitcoin-mined    |     19 |
| 17 | bitcoin-mined    |     30 |
 ---- ------------------ -------- 

CodePudding user response：

The following answer abuses recursion to implement a loop.

• It's probably better to write an actual loop...

This is because the FIFO rules mean that it's impossible to know in advance which mined/received records will have been decremented by a lost record. They each lost/transferred record must therefore be fully processed, before the allocation for the next lost/transferred record can begin.

Then, I used the following logic...

• An income record is when the transaction_typeid is 101 or 102.
• An outgoing record is when the transaction_typeid is 103 or 104.
• If an outgoing is type 104/lost, it can be applied to any income type. Else, the outgoing must be type 103/transferred and can only be applied to income type 101/mined.

Then...

• Create a record set of all income records
• Join the outgoing records to that set one at a time (lowest id first)
• The most that can be allocated to the first income record is LEAST(in.amount, out.amount)
• For the 2nd record, that becomes LEAST(in.amount, out.amount - <amount allocated to row1>)

Using window functions, that becomes (pseudo-code)...

LEAST(
  in.amount,
  GREATEST(
    0,
    out.amount - SUM(in.amount) OVER (<all-preceding-rows>)
  )
)
WHERE out.transcation_type_id = 104
   OR  in.transaction_type_id = 101

So, the final (quite long) query is...

WITH
  income
AS
(
  SELECT
    c.id,
    c.transaction_typeid,
    c.amount
  FROM
    cryptotransactionledger  c
  WHERE
    c.transaction_typeid IN (101, 102)
),
  outgoing
AS
(
  SELECT
    o.*,
    ROW_NUMBER() OVER (ORDER BY o.id)  AS seq_num
  FROM
    cryptotransactionledger  o
  WHERE
    o.transaction_typeid IN (103, 104)
),
  fifo(
    depth, id, transaction_typeid, amount
  )
AS
(
  SELECT 0, i.* FROM income i
  ---------
  UNION ALL
  ---------
  SELECT
    f.depth   1,
    f.id,
    f.transaction_typeid,
    f.amount
    -
    LEAST(
      -- everything remaining
      f.amount,
      -- the remaining available deductions
      GREATEST(
        0,
        CASE WHEN o.transaction_typeid = 104 THEN -o.amount
             WHEN f.transaction_typeid = 101 THEN -o.amount
                                             ELSE 0         END
        -
        -- the total amount from all preceding income rows
        COALESCE(
          SUM(CASE WHEN o.transaction_typeid = 104 THEN f.amount
                   WHEN f.transaction_typeid = 101 THEN f.amount
                                                   ELSE 0         END
          )
          OVER (ORDER BY f.id
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND         1 PRECEDING
          ),
          0
        )
      )
    )
  FROM
    fifo     f
  INNER JOIN
    outgoing o
      ON o.seq_num = f.depth   1
)
SELECT
  f.*
FROM
  fifo  f
WHERE
  f.depth = (SELECT MAX(depth) FROM fifo)
ORDER BY
  f.id
;

Here's a demo, based on the one in you question.

• https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=5bc85943fd8ff4e87cac9b5e68e092ee