By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH Quotes AS
( SELECT *
FROM (
VALUES ('POCKET', 1.00), -- Pocket and money always have $1 price
('MONEY' , 1.00), -- Pocket and money always have $1 price
('XPTO' , 200.00), -- Current rate for this asset
('WXYZ' , 8.50))
Entries(Asset, Price)
), RawData AS
( SELECT *
FROM (
VALUES (1, '2020-08-01', 'MBIT', 'POCKET', 'MONEY', 100.00, 100.00), -- added $100 from my pocket into MBIT exchange
(2, '2020-08-02', 'MBIT', 'MONEY' , 'XPTO' , 0.50, 100.00), -- bought 0.50 XPTO for $100 (thus rate=200)
(3, '2020-08-03', 'MBIT', 'XPTO' , 'MONEY', 0.50, 125.00), -- sold 0.50 XPTO for $125 (thus rate=250, $25 profit)
(4, '2020-08-04', 'MBIT', 'MONEY' , 'XPTO' , 0.35, 85.00), -- bought 0.35 XPTO for $85
(5, '2020-08-05', 'MBIT', 'MONEY' , 'WXYZ' , 1.75, 20.00), -- bought 1.75 WXYZ for $20 (rate~=11.43)
(6, '2020-08-06', 'MBIT', 'MONEY' , 'WXYZ' , 1.85, 15.00)) -- bought 1.85 WXYZ for $15 (new total = 3.6 for $35, rate~=9.72)
Entries([Order#], Date, Exchange, Debit, Credit, Quantity, Value)
), Accounting AS
( SELECT [Order#], Date, Exchange,
Credit AS Account,
+Value [$],
+CASE WHEN Credit IN ('POCKET', 'MONEY') THEN Value ELSE Quantity END Quantity,
+CASE WHEN Credit IN ('POCKET', 'MONEY') THEN 1.000 ELSE Value / Quantity END Rate
FROM RawData
UNION ALL
SELECT [Order#], Date, Exchange,
Debit AS Account,
+Value [$],
-CASE WHEN Debit IN ('POCKET', 'MONEY') THEN Value ELSE Quantity END Quantity,
+CASE WHEN Debit IN ('POCKET', 'MONEY') THEN 1.000 ELSE Value / Quantity END Rate
FROM RawData
)
SELECT Balance.Exchange,
Balance.Account,
Balance.Quantity,
| Exchange | Account | Quantity | Original | Current | % | Value |
|---|---|---|---|---|---|---|
| MBIT | MONEY | 5.00 | 1.0000000 | 1.00 | 0.0000000000000000000000000000 | 5.0000 |
| MBIT | -100.00 | 1.0000000 | 1.00 | 0.0000000000000000000000000000 | -100.0000 | |
| MBIT | WXYZ | 3.60 | 9.7222222 | 8.50 | -0.1257142837159183627792419721 | 30.6000 |
| MBIT | XPTO | 0.35 | 171.4285714 | 200.00 | 0.1666666668611111111435185185 | 70.0000 |