Question 20356 in Mysql Question posted in
The official MySQL documentation can be found here.

How to get all dates where the customer's total balance reaches 0 – Mysql

I have a table as follows. It consists of many customers (only showing one customer, James, here for simplicity) who can have many accounts. Each account has a balance every day. My goal is to get all the dates for each customer where the total balance (across all accounts) goes to 0.

Let’s have a look at this scenario. The records start from 1 Jan 2017, and James has 3 active accounts with balances 44, 56, 928. The next day on 2 Jan 2017, his account 2 balance goes to 0. The following day on the 3rd, his accounts 1 and 3 also go to 0, therefore on the 3rd, his TOTAL balance (across all accounts) is 0. The next day on the 4th, his account 2 balance goes back up to 435. Then on the 5th, it goes back down to 0, and his TOTAL balance (across all accounts) is back to 0. Now on the 6th, he has a new account #4 with a balance of 64. The next day on the 7th, this account #4 goes to 0 and again his TOTAL balance (across all accounts) is back to 0.

Customer Account Date Balance
James 1 1/1/2017 44
James 2 1/1/2017 56
James 3 1/1/2017 928
James 1 2/1/2017 44
James 2 2/1/2017 0
James 3 2/1/2017 928
James 1 3/1/2017 0
James 2 3/1/2017 0
James 3 3/1/2017 0
James 1 4/1/2017 0
James 2 4/1/2017 435
James 3 4/1/2017 0
James 1 5/1/2017 0
James 2 5/1/2017 0
James 3 5/1/2017 0
James 1 6/1/2017 0
James 2 6/1/2017 0
James 3 6/1/2017 0
James 4 6/1/2017 64
James 1 7/1/2017 0
James 2 7/1/2017 0
James 3 7/1/2017 0
James 4 7/1/2017 0

Therefore, the output of the SQL query should be like this (for every customer, not just James)

Customer Date_Zero_Bal
James 3/1/2017
James 5/1/2017
James 7/1/2017

I have the following code but it doesn’t look at total balance going to 0. It shows the dates when the balance for each account goes to 0 which is not what I want:

WITH cte AS (
SELECT
    customer                   
    , account                       
    , date                   
    , balance      
    , LAG(balance) OVER (PARTITION BY customer, account ORDER BY date) AS prev_bal
FROM table
)
 
SELECT customer, account, date
FROM cte
WHERE balance = 0 AND prev_bal > 0;
Tags:

2

Answers


  1. 0

    Just try this:

    SELECT customer
      ,date
      ,SUM(Balance)
FROM customer_account
GROUP BY customer
        ,date
HAVING SUM(Balance) = 0

    enter image description here

    Here is the fiddle:

    SELECT customer
      ,date AS Date_Zero_Bal
FROM customer_account
GROUP BY customer
        ,date
HAVING SUM(Balance) = 0

    enter image description here

    WITH DataSource AS
(
    SELECT customer
           ,date
           ,SUM(Balance) AS Balance
           ,ROW_NUMBER() OVER (PARTITION BY customer ORDER BY date) as row_id
    FROM customer_account
    GROUP BY customer
            ,date

)
SELECT DS1.customer
      ,DS1.date
FROM DataSource DS1
INNER JOIN DataSource DS2
   ON DS1.customer = DS2.customer
   AND DS1.row_id - 1 = DS2.row_id
WHERE DS1.Balance = 0
   AND DS2.Balance > 0
    Login or Signup to reply.
  2. 0

    You need to build the sum for all the customers accounts for every day which is:

    SELECT
   customer, date, SUM(balance)
FROM
   table
GROUP BY
   customer, date

    With this table you can easily move on.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search