# calculation for cash and card payment report

## Recommended Posts

i have foreach loop for payment list of a client if they fully paid or have a balance, my problem is client has two receipt like payment in cash and payment in card. he/she paid by different date but don't mind the date.. here is my table

dbtable scenario

uid | name | payment_type | cost     | payment  |
1    |  kim    |   card                |    100   |   100    |
2    |  lee     |   cash               |    95      |   95     |
3    |  kent   |   cash              |   100      |   50     |
3    |  kent   |   card               |   100      |   50     |
4    |  iya      |   cash              |    80       |   40     |
4    |  iya      |  card                |   80        |   20     |

its should be the output

uid | name   | cash   |  card  | cost   | balance |
1    | kim       |  0        |   100  |  100   |  0            |
2    | lee        |  95      |   0      |  95      |  0            |
3    | kent      |  50      |   0     |  100    |  50          |<-this part 100-50 = 50 balance
3    | kent      |  0       |   50    |  100   |  0             |<-balance 50-50 = 0 balnce
4    | iya        |  40      |   0      |  80     |  40         |<-this part 80-40 = 40 balance
4    | iya        |  0        |   20    |  80     |  20         |<-balance 40-20 = 20 balnce

need your assistance newbe here for this scenario but i try to resolve but i got failed. i did not get the correct output..

Edited by Yohanne
##### Share on other sites

Well, the table structure isn't really good to work with. That's your real problem. Because as far as I can read that table, Kent has a total cost 200 and total payment 100, and Iya has total cost 160 and total payment 60.

You need a concept of separate orders and payment. One table stores the 100 cost for Kent, then another table stores the 50 cash payment and 50 card payment.

##### Share on other sites

I totally agree with @requinix regarding the two  tables.

However, if you are willing to compromise over the output, you could do something like this

```SELECT uid
, name
, SUM(CASE payment_type
WHEN 'cash' THEN payment
ELSE 0
END) as cash
, SUM(CASE payment_type
WHEN 'card' THEN payment
ELSE 0
END) as card
, cost
, cost-SUM(payment) as balance
FROM payment
GROUP BY uid

+------+------+------+------+------+---------+
| uid  | name | cash | card | cost | balance |
+------+------+------+------+------+---------+
|    1 | kim  |    0 |  100 |  100 |       0 |
|    2 | lee  |   95 |    0 |   95 |       0 |
|    3 | kent |   50 |   50 |  100 |       0 |
|    4 | iya  |   40 |   20 |   80 |      20 |
+------+------+------+------+------+---------+```

If you really need every transaction listed, the SQL becomes quite complex involving user variables and subqueries. It would be much easier to do in the PHP as you output each row.

[EDIT] ...

For the sake of completeness

```SELECT uid
, name
, cash
, card
, cost
, cost-total as balance
FROM (
SELECT
name
, CASE payment_type
WHEN 'cash' THEN payment
ELSE 0
END as cash
, CASE payment_type
WHEN 'card' THEN payment
ELSE 0
END as card
, cost
, @tot := CASE @previd
WHEN uid THEN @tot + payment
ELSE payment
END as total
, @previd := uid as uid
FROM (
SELECT *
FROM payment
ORDER BY uid
) sorted
JOIN (SELECT @previd:=0, @tot:=0) initialize
) recs;

+------+------+------+------+------+---------+
| uid  | name | cash | card | cost | balance |
+------+------+------+------+------+---------+
|    1 | kim  |    0 |  100 |  100 |       0 |
|    2 | lee  |   95 |    0 |   95 |       0 |
|    3 | kent |   50 |    0 |  100 |      50 |
|    3 | kent |    0 |   50 |  100 |       0 |
|    4 | iya  |   40 |    0 |   80 |      40 |
|    4 | iya  |    0 |   20 |   80 |      20 |
+------+------+------+------+------+---------+```

Edited by Barand
• 1
• 1
##### Share on other sites

An alternative to the 2-table option is to treat costs as transactions, just like payments (cost amounts +ve, payment amounts -ve in this example)...

DATA

```TABLE: payment
+------+------+------------+--------------+---------+
| uid  | name | trans_date | payment_type | payment |
+------+------+------------+--------------+---------+
|    1 | kim  | 2020-03-01 | cost         |     100 |
|    1 | kim  | 2020-03-02 | card         |    -100 |
|    2 | lee  | 2020-03-01 | cost         |      95 |
|    2 | lee  | 2020-03-02 | cash         |     -95 |
|    3 | kent | 2020-03-01 | cost         |     100 |
|    3 | kent | 2020-03-03 | cash         |     -50 |
|    3 | kent | 2020-03-04 | card         |     -50 |
|    4 | iya  | 2020-03-01 | cost         |      80 |
|    4 | iya  | 2020-03-05 | cash         |     -40 |
|    4 | iya  | 2020-03-06 | card         |     -20 |
+------+------+------------+--------------+---------+```

then

```SELECT uid
, name
, date
, cost
, cash
, card
, total as balance
FROM (
SELECT
name
, DATE_FORMAT(trans_date, '%b %D') as date
, CASE payment_type
WHEN 'cash' THEN -payment
ELSE '-'
END as cash
, CASE payment_type
WHEN 'card' THEN -payment
ELSE '-'
END as card
, CASE payment_type
WHEN 'cost' THEN payment
ELSE '-'
END as cost
, @tot := CASE @previd
WHEN uid THEN @tot+payment
ELSE payment
END as total
, @previd := uid as uid
FROM (
SELECT *
FROM payment
ORDER BY uid, trans_date
) sorted
JOIN (SELECT @previd:=0, @tot:=0) initialize
) recs;

+------+------+---------+------+------+------+---------+
| uid  | name | date    | cost | cash | card | balance |
+------+------+---------+------+------+------+---------+
|    1 | kim  | Mar 1st | 100  | -    | -    |     100 |
|    1 | kim  | Mar 2nd | -    | -    | 100  |       0 |
|    2 | lee  | Mar 1st | 95   | -    | -    |      95 |
|    2 | lee  | Mar 2nd | -    | 95   | -    |       0 |
|    3 | kent | Mar 1st | 100  | -    | -    |     100 |
|    3 | kent | Mar 3rd | -    | 50   | -    |      50 |
|    3 | kent | Mar 4th | -    | -    | 50   |       0 |
|    4 | iya  | Mar 1st | 80   | -    | -    |      80 |
|    4 | iya  | Mar 5th | -    | 40   | -    |      40 |
|    4 | iya  | Mar 6th | -    | -    | 20   |      20 |
+------+------+---------+------+------+------+---------+```

• 1
• 1
##### Share on other sites

just wow.. @Barand why you are like that.. so easy to solve.. for me actually i got whole day like 15hrs.. but  actually i follow your 1st statement.. thank you so much..

##### Share on other sites

This thread is more than a year old.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.