I am trying to make a current balance system for pre-accounting in mysql. I create receipts in different currencies and collect payments in different currencies. My main currency is Turkish Lira (TRY). The amount field in the table below is the amount of the foreign currency itself. I am trying to make my calculations using dovizKarsiligiTl. yes I missed it
Original table
id | meblag | dovizKuru | dovizKarsiligi | dovizID |
---|---|---|---|---|
452 | -60 | 32,31 | 1938,63 | 2 |
455 | 100 | 35,14 | 3514 | 3 |
456 | -300 | 1 | 300 | 1 |
dovizID | name |
---|---|
1 | TRY |
2 | USD |
3 | EURO |
SET @TRY_balance := 0;
SET @USD_balance := 0;
SET @EUR_balance := 0;
id | amount | foreignExchangeEquivalentTl | exchange rate | dovizID | TRY_balance | USD_balance | EUR_balance |
---|---|---|---|---|---|---|---|
452 | -60 | 1938.63 | 32.31 | 2 | 0 | 1938.6 | 0 |
455 | 100 | 3514.00 | 35.14 | 3 | 0 | 1938.6 | 3514 |
456 | -300 | 300 | 1 | 1 | -300 | 1938.6 | 3514 |
In the table above, the TRY_BAKİYE, USD_BAKİYE, EUR_BAKİYE fields are virtual, that is, they can change depending on the transaction we perform in each row.
The table above is now my raw table and my balance fields are incorrect according to my query result.
In line 1, I owe 1938.6 TL, equivalent to -60 dollars. In the 2nd line, I paid 3514 TL for 100 euros, so according to the balance status of the 2nd line, my debt should have been completed and 1575.4 TL should have become a creditor. In the 3rd line, I owed 300 TL in return for -300 TL, and since I was credited with 1575.4 TL in the 2nd line, I should have become a creditor of 1275.4 TL. Accordingly, the following table should be formed. However, I cannot find the query that will create this table.
id | amount | exchange rate | dovizID | TRY_balance | USD_balance | EUR_balance |
---|---|---|---|---|---|---|
452 | -60 | 32.31 | 2 | 0 | 1938.6 | 0 |
455 | 100 | 35.14 | 3 | 0 | 0 | 1575.4 |
456 | -300 | 1 | 1 | 0 | 0 | 1275.4 |
The data here is not fixed but variable. Payment or debt status can be understood from the -+ value in the amount. A positive TL value is kept as the foreign currency equivalent in each line.
I tried making inquiries with Chatgpt for 8 hours, but I did not get any successful results.
My incorrect query is below
SELECT
id,
meblag,
dovizKuru,
dovizKarsiligi,
dovizID,
SUM( CASE WHEN dovizID = 1 THEN CASE WHEN meblag > 0 THEN @TRY_bakiye:= @TRY_bakiye + dovizKarsiligi ELSE @TRY_bakiye := @TRY_bakiye -1*dovizKarsiligi END ELSE 0 END ) OVER (ORDER BY islemTarihi asc ) AS trb,
SUM( CASE WHEN dovizID = 2 THEN CASE WHEN meblag > 0 THEN @USD_bakiye:= @USD_bakiye + @EUR_bakiye + @TRY_bakiye + dovizKarsiligi ELSE @USD_bakiye:= @USD_bakiye:= -1*dovizKarsiligi END ELSE 0 END ) OVER (ORDER BY islemTarihi asc ) AS usb,
SUM( CASE WHEN dovizID = 3 THEN CASE WHEN meblag > 0 THEN @EUR_bakiye:= @EUR_bakiye + dovizKarsiligi ELSE @EUR_bakiye:=@EUR_bakiye -1*dovizKarsiligi END ELSE 0 END ) OVER (ORDER BY islemTarihi asc ) AS eub
FROM
al_islemler
WHERE
al_islemler.cariID = 33
AND al_islemler.firmaID = 382
GROUP BY
id
ORDER BY
al_islemler.islemTarihi ASC,
al_islemler.id DESC
LIMIT 0,
10;
MySQL 8 introduced window functions, which come here very handy
SELECT *
,
SUM(CASE WHEN dovizID =1 THEN `amount` * `exchange rate` ELSE 0 END) OVER (ORDER BY `id`) as `TRY_balance`,
SUM(CASE WHEN dovizID =2 THEN `amount` * `exchange rate` ELSE 0 END ) OVER (ORDER BY `id`) AS `USD_balance`,
SUM(CASE WHEN dovizID =3 THEN `amount` * `exchange rate` ELSE 0 END) OVER (ORDER BY `id`) AS `EUR_balance`
FROM balance
id | amount | foreignExchangeEquivalentTl | exchange rate | dovizID | TRY_balance | USD_balance | EUR_balance |
---|---|---|---|---|---|---|---|
452 | -60 | 1938.63 | 32.31 | 2 | 0.00 | -1938.60 | 0.00 |
455 | 100 | 3514.00 | 35.14 | 3 | 0.00 | -1938.60 | 3514.00 |
456 | -300 | 300.00 | 1.00 | 1 | -300.00 | -1938.60 | 3514.00 |
CREATE TABLE balance (
`id` INTEGER,
`amount` INTEGER,
`foreignExchangeEquivalentTl` DECIMAL(10,2),
`exchange rate` DECIMAL(10,2),
`dovizID` INTEGER
);
INSERT INTO balance
(`id`, `amount`, `foreignExchangeEquivalentTl`, `exchange rate`, `dovizID` )
VALUES
('452', '-60', '1938.63', '32.31', '2'),
('455', '100', '3514.00', '35.14', '3'),
('456', '-300', '300', '1', '1');
Records: 3 Duplicates: 0 Warnings: 0
SELECT
`id`, `amount`
, `exchange rate`, `dovizID`,
SUM(CASE WHEN dovizID =1 THEN `amount` * `exchange rate` ELSE 0 END) OVER (ORDER BY `id`) as `TRY_balance`,
SUM(CASE WHEN dovizID =2 THEN `amount` * `exchange rate` ELSE 0 END ) OVER (ORDER BY `id`) AS `USD_balance`,
SUM(CASE WHEN dovizID =3 THEN `amount` * `exchange rate` ELSE 0 END) OVER (ORDER BY `id`) AS `EUR_balance`
FROM balance
id | amount | exchange rate | dovizID | TRY_balance | USD_balance | EUR_balance |
---|---|---|---|---|---|---|
452 | -60 | 32.31 | 2 | 0.00 | -1938.60 | 0.00 |
455 | 100 | 35.14 | 3 | 0.00 | -1938.60 | 3514.00 |
456 | -300 | 1.00 | 1 | -300.00 | -1938.60 | 3514.00 |
SELECT
`id`, `amount`
, `exchange rate`, `dovizID`,
SUM(CASE WHEN dovizID =1 THEN `amount` * `exchange rate` ELSE 0 END) OVER (ORDER BY `id`) as `TRY_balance`,
SUM(CASE WHEN dovizID =2 THEN `amount` * `exchange rate` ELSE 0 END ) OVER (ORDER BY `id`) AS `USD_balance`,
SUM(CASE WHEN dovizID =3 THEN `amount` * `exchange rate` ELSE 0 END) OVER (ORDER BY `id`) AS `EUR_balance`
,SUM(`amount` * `exchange rate`) OVER (ORDER BY `id`) as total_balance
FROM balance
id | amount | exchange rate | dovizID | TRY_balance | USD_balance | EUR_balance | total_balance |
---|---|---|---|---|---|---|---|
452 | -60 | 32.31 | 2 | 0.00 | -1938.60 | 0.00 | -1938.60 |
455 | 100 | 35.14 | 3 | 0.00 | -1938.60 | 3514.00 | 1575.40 |
456 | -300 | 1.00 | 1 | -300.00 | -1938.60 | 3514.00 | 1275.40 |
the best that i can do
SELECT
`id`, `amount`
, `exchange rate`, `dovizID`,
CASE WHEN dovizID =1 THEN
SUM(CASE WHEN dovizID =1 THEN `amount` * `exchange rate` ELSE 0 END) OVER (ORDER BY `id`) ELSE 0 ENd as `TRY_balance`,
CASE WHEN dovizID =2 THEN SUM(CASE WHEN dovizID =2 THEN `amount` * `exchange rate` ELSE 0 END ) OVER (ORDER BY `id`) ELSE 0 ENd AS `USD_balance`,
CASE WHEN dovizID =3 THEN SUM(CASE WHEN dovizID =3 THEN `amount` * `exchange rate` ELSE 0 END) OVER (ORDER BY `id`) ELSE 0 END AS `EUR_balance`
,SUM(`amount` * `exchange rate`) OVER (ORDER BY `id`) as total_balance
FROM balance
id | amount | exchange rate | dovizID | TRY_balance | USD_balance | EUR_balance | total_balance |
---|---|---|---|---|---|---|---|
452 | -60 | 32.31 | 2 | 0.00 | -1938.60 | 0.00 | -1938.60 |
455 | 100 | 35.14 | 3 | 0.00 | 0.00 | 3514.00 | 1575.40 |
456 | -300 | 1.00 | 1 | -300.00 | 0.00 | 0.00 | 1275.40 |