CODEWITHSUNDEEP
mysqlsql
Burning Hippo
Burning Hippo

Reputation: 805

query for balance due and due date for entity

I am trying to get a list of guests who have not yet fully paid their total charges (those who still have a balance).

I want to display like so: 

1231231(reservation id) - John Doe(guest name) 2013-11-17(date due) 153.14(balance due)

without the ( ) of course.

I have tinkered but still can't find a query that is satisfactory.

Here is a link to the SQLFiddle.

If I haven't provided enough info, kindly let me know.

Upvotes: 2

Views: 412

Answers (1)

peterm
peterm

Reputation: 92845

Are you looking for something like this?

SELECT r.id, 
       CONCAT(g.fname, ' ', g.lname) guest_name, 
       r.arrival date_due,
       r.total_price - COALESCE(p.payment_amt, 0) balance_due
  FROM reservations r JOIN guests g
    ON r.guest = g.id LEFT JOIN
(
  SELECT reservation, SUM(payment_amt) payment_amt
    FROM payments
   GROUP BY reservation
) p
    ON r.id = p.reservation
HAVING balance_due > 0

Sample output (based on provided sample data):

|      ID | GUEST_NAME |                        DATE_DUE | BALANCE_DUE |
|---------|------------|---------------------------------|-------------|
| 1000023 |   John Doe | November, 09 2013 00:00:00+0000 |        40.5 |
| 1000022 |   John Doe | November, 09 2013 00:00:00+0000 |        40.5 |
| 1000018 |   John Doe | November, 01 2013 00:00:00+0000 |          54 |
| 1000019 |   John Doe | November, 08 2013 00:00:00+0000 |          54 |
| 1000020 |  Mary Jane | November, 08 2013 00:00:00+0000 |          54 |
| 1000021 |  Mary Jane | November, 08 2013 00:00:00+0000 |          54 |

Here is SQLFiddle demo

Upvotes: 3

Related Questions