0

I have a payment system, and want to display the counter of each customer payment (first, second, third ...) and if it's the fist payment, display (New). How can i do this in one query ?

SELECT c.customer_name,
       p.payment_datetime,
       p.amount
FROM tbl_payments p
         LEFT JOIN tbl_customers c ON p.customer_id = c.customer_id
ORDER BY p.payment_datetime DESC;

+---------------+-------------------+--------+
| customer_name | payment_datetime  | amount |
+---------------+-------------------+--------+
| Cus1          | 2015              | 200$   |
| Cus2          | 2016              | 300$   |
| Cus1          | 2017              | 140$   |
| Cus1          | 2018              | 500$   |
+---------------+-------------------+--------+

The output must be

+---------------+-------------------+--------+--------+--------------------------------+
| customer_name | payment_datetime  | amount | status |                                |
+---------------+-------------------+--------+--------+--------------------------------+
| Cus1          | 2015              | 200$   | Old(1) | 1 -> First payment for "cus1"  |
| Cus2          | 2016              | 300$   | New    | New -> "cus2" have one payment |
| Cus1          | 2017              | 140$   | Old(2) | 2 -> Second payment for "cus1" |
| Cus1          | 2018              | 500$   | Old(3) | 3 -> Third payment for "cus1"  |
+---------------+-------------------+--------+--------+--------------------------------+

And on executing this query

SELECT c.customer_name,
       p.payment_datetime,
       p.amount
FROM tbl_payments p
         LEFT JOIN tbl_customers c ON p.customer_id = c.customer_id
WHERE p.payment_datetime > '2015' AND p.payment_datetime < '2019'
ORDER BY p.payment_datetime DESC;

Get the result :

+---------------+-------------------+--------+--------+--------------------------------+
| customer_name | payment_datetime  | amount | status |                                |
+---------------+-------------------+--------+--------+--------------------------------+
| Cus2          | 2016              | 300$   | New    | New -> "cus2" have one payment |
| Cus1          | 2017              | 140$   | Old(2) | 2 -> Second payment for "cus1" |
| Cus1          | 2018              | 500$   | Old(3) | 3 -> Third payment for "cus1"  |
+---------------+-------------------+--------+--------+--------------------------------+
  • What version of MySQL are you using? And what's the primary key of tbl_payments? – sticky bit Aug 4 at 23:14
  • MySQL version 5.7 and the primary key of tbl_payments is payment_id – Hussein Mansour Aug 4 at 23:20
0

You can use a CASE expression that checks for the existence of another payment of the same customer for each payment. If such a payment exists get the count of previous payments plus 1 and use that in the string indicating and "old" payment. Otherwise return the string indicating a "new" payment.

SELECT c1.customer_name,
       p1.payment_datetime,
       p1.amount,
       CASE
         WHEN EXISTS (SELECT *
                             FROM tbl_payments p2
                             WHERE p2.customer_id = p1.customer_id
                                   p2.payment_id <> p1.payment_id) THEN
           concat('Old(',
                  (SELECT count(*) + 1
                          FROM tbl_payments p2
                               WHERE p2.customer_id = p1.customer_id
                                     AND (p2.payment_datetime < p1.payment_datetime
                                           OR p2.payment_datetime = p1.payment_datetime
                                              AND p2.payment_id < p1.payment_id)),
                  ')')
         ELSE
           'New'
       END status
       FROM tbl_payments p1
            LEFT JOIN tbl_customers c1
                      ON p1.customer_id = c1.customer_id
       WHERE p.payment_datetime > '2015'
             AND p.payment_datetime < '2019';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.