CODEWITHSUNDEEP
mysql
sineverba
sineverba

Reputation: 5172

MYSQL: SQL debit / credit

Editing my previous question, I simplified (I hope!) my problem.

Let's go.

We have a table like in this fiddle: http://sqlfiddle.com/#!9/42250/1

We have 3 different id_customer, I need to SELECT only that made transactions in two or more id_shop.

In effect I have this data with this query:

SELECT DISTINCT(id_customer)
FROM transaction AS t1
WHERE EXISTS

    (SELECT id_customer
    FROM transaction as t2
    WHERE t2.id_shop_where_transaction_is_done != t1.id_shop_where_transaction_is_done 
    AND t2.id_customer = t1.id_customer)

And data are 64982 and 64984.

Now I need to calculate the credit / debit between shop(s), and having a result table like following:

+------------+-------+--------+
|            | DEBIT | CREDIT |
+------------+-------+--------+
| TRASTEVERE | 5.50  | 0.00   |
| MONTI      | 2.00  | 5.50   |
| PRATI      | 0.00  | 2.00   |
+------------+-------+--------+

Why "TRASTEVERE" is in debit of 5.50? Because id_customer 64984 has charged 11.00 in TRASTEVERE and spent 5.50€ in MONTI.

Why "MONTI" is in credit of 5.50? Because id_customer 64984 has charged 11.00 in TRASTEVERE and spent 5.50 in MONTI.

Basically, I need to

1) Exclude customers that spent in just one shop (made, see the query above) 2) If a customer charge X in shop A and spend Y in shop B, I need to calculate this and apply credit/debit.

Thank you very much.

Upvotes: 0

Views: 459

Answers (1)

Aleksandar Miladinovic
Aleksandar Miladinovic

Reputation: 1027

After discussion in comment and chat we figured out that the solution here (with minimal table structure change) is to be added new column (id_shop_where_money_come_from INT) in table transaction which will hold data which represent account_id where money come from so we can fallow between which two account transfer is made. Here is SQL Fiddle with added column and query which will output desired result...

GL!

Upvotes: 1

Related Questions