How to query customer's credit/debit total balance with this table structure in MYSQL?

Question

0.00/5 (No votes)

See more:

I have a table in mysql. The table name is customer_transaction. This table contains current movements.
If the orders_type column is 0, the invoice has been received, if it is 1, the invoice has been sold. Now, how can I find the debit/credit/running balance in this table according to the customer id? What I want to do is something like this;

| id  | customer_id|orders_tip| comment    |amount     |
| --- | -----------|----------| -----------|-----------|
| 1   | 25         |  0       |acc. opening| 400.25    |
| 2   | 25         |  0       |purc invoice| 1000.00   |
| 3   | 25         |  1       |salesinvoice| 600.00    |
| 4   | 25         |  1       |salesinvoice| 1400.25   |
| 5   | 26         |  0       |purc invoice| 700.25    |
| 6   | 26         |  1       |salesinvoice| 900.95    |
| 7   | 27         |  1       |salesinvoice| 400.25    |
| 8   | 26         |  0       |purc invoice| 125.79    |
-----------------------------------------------------------------

for customer_id=25 ;

| id  | customer_id|orders_tip| comment    |debit    |credit   |balance  |
| --- | -----------|----------| -----------|---------|---------|---------|
| 1   | 25         |  0       |acc. opening| 0       |400.25   | -400.25 |
| 2   | 25         |  0       |purc invoice| 0       |1000.00  | -1400.25|
| 3   | 25         |  1       |salesinvoice| 600.00  | 0       | -800.25 |
| 4   | 25         |  1       |salesinvoice| 1400.25 | 0       |  600.00 |
I need MYSQL query that will return the result in the table. Thank you very much in advance. I hope I explained correctly.

What I have tried:

mysql query

SELECT * ,(SELECT SUM(tutar) from customer_hesap WHERE id<=cHes.id) as YURUYEN_TOPLAM FROM `customer_hesap` as cHes

Posted 14-Sep-22 12:55pm

Mert Baran

Updated 14-Sep-22 23:46pm

Add a Solution

Comments

[no name] 14-Sep-22 21:11pm

Nobody uses a running balance. There's a total for the period (month, quarter, and / or year) and a "current balance", and that's about it. Debit / credit columns can be created with a CASE off the original column.

CHill60 15-Sep-22 6:47am

"Nobody uses a running balance" - I do. Amongst other things, I note the amount that was in my Accounts when specific payments were made into them and transfer that amount into savings/investments. With a running balance I can do it at a glance, but with current balance only I'd have to either calculate backwards or remember to check the account on specific days. PITA for me.

[no name] 15-Sep-22 12:21pm

I'm talking about "business accounting" ... not managing your check book. Ask yourself, when dealing with hundreds of customers and thousands of transactions, what would a running balance (on every transaction) tell you? Particularly when virtually all billing and payment cycles are "monthly".

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2022-09-14T23:46:00

You were quite close with your attempt, but you first need to split out the amount (tutar?) as either a debit or a credit. I prefer to use Common Table Expressions (see An Introduction to MySQL CTE[^]) , but you could use a sub-query or a temporary table if you prefer.

SQL

;with cte as 
(
	select id, customer_id, orders_tip, comment
	, case when orders_tip = 1 then amount else 0 end as debit
	, case when orders_tip = 0 then amount else 0 end as credit
	from @customer_hesap
)
select id, customer_id, orders_tip, comment, debit, credit
,(SELECT SUM(debit - credit) from cte WHERE id <= cHes.id) as YURUYEN_TOPLAM 
FROM cte as cHes
WHERE customer_id=25;

Note that I've used SUM(debit - credit) instead of amount. By the way, it's usually the other way around but this fits your expected results.

I'm not a fan of correlated sub-queries so I prefer to use Window function (see MySQL :: MySQL 8.0 Reference Manual :: 12.21.2 Window Function Concepts and Syntax[^] ) - I find them easier to read, but also correlated sub-queries can kill performance.

SQL

;with cte as 
(
	select id, customer_id, orders_tip, comment
	, case when orders_tip = 1 then amount else 0 end as debit
	, case when orders_tip = 0 then amount else 0 end as credit
	from @customer_hesap
)
select id, customer_id, orders_tip, comment, debit, credit
,sum(debit - credit) over (partition by customer_id order by id)
from cte a;

How to query customer's credit/debit total balance with this table structure in MYSQL?

1 solution

Solution 1

Add your solution here

Preview 0

How to query customer's credit/debit total balance with this table structure in MYSQL?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us