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

Create a timeline from non-continuous data in MySql

I have data from my bank in a table. The table contains all postings and a balance. The problem is if there is no posting on a date then there is no entry and hence no balance.

I would like to get a table out with all dates (e.g. today and a year back) for each account. Then I will join exchange rates and sum each day, so I have a "bank total balance" per day.

Here’s my table with sample data (simplified, only relevant columns, except Amount which is included to make understanding the table easier):

Id Account Currency Date Amount Balance
100 1 EUR 2024-02-10 100 550
101 1 EUR 2024-02-10 15 565
102 2 USD 2024-02-10 50 420
103 1 EUR 2024-02-10 13 578
104 1 EUR 2024-02-11 -8 570
105 1 EUR 2024-02-12 310 880
106 1 EUR 2024-02-12 -50 830

The balance for the day is always the one with the highest Id.

Desired output:

Date Currency Balance
2024-02-10 EUR 565
2024-02-10 USD 420
2024-02-11 EUR 578
2024-02-11 USD 420
2024-02-12 EUR 830
2024-02-12 USD 420

I do have a table of dates (don’t know if that’s necessary).

I’ve tried a lot but simply can’t figure out how to do it.

I’ve come this far:

SELECT Date, Currency, Balance
FROM bank
WHERE Id IN (
  SELECT MAX(Id) AS Id
  FROM bank
  WHERE Account IN (1, 2)
  GROUP BY Date, Currency
 )
Tags:

3

Answers


  1. 0

    You want a Window function. These operate over a range of rows and can perform different functionality like aggregating the data from them, or taking the max value.

    You want something like:

    SELECT * MAX(balance) OVER(PARTITION BY currency, date)
FROM bank
ORDER BY date

    If you order the query correctly you could use LAST_VALUE instead of MAX.

    Login or Signup to reply.
  2. 0

    Here we can see how a date range can be selected between two dates of your choice which can be then left-joined with your table. In the example below I used an ad-hoc table to join with, instead, you may left join sample on sample.Date = v.selected_date instead:

    select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
 left join (select 1 as foo) t
 on 1=1
where selected_date between '2023-02-16' and '2024-02-16'
    Login or Signup to reply.
  3. 0

    The idea of my comment is roughly this way:

    with currencies as (
  select distinct(currency) as currency from bank
),
curr_dates as (
  select *
  from dates as d
  join currencies
  where `date` between '2024-02-10' and '2024-02-12'
)
select *,
  (
    select balance
    from bank as b
    where b.`date` <= cd.`date`
      and b.currency = cd.currency
      and b.account in (1, 2)
    order by b.id desc
    limit 1
  ) as balance
from curr_dates as cd
order by date;

    Fiddle: https://www.db-fiddle.com/f/gt9sEP9YX7Y4zteae6ddk1/2

    It outputs:

    date currency balance
    2024-02-10 EUR 578
    2024-02-10 USD 420
    2024-02-11 EUR 570
    2024-02-11 USD 420
    2024-02-12 EUR 830
    2024-02-12 USD 420

    It might not be performant, unfortunately there is no given answer that produce the desired result in the answer.

    Also, i do wonder why on earth EUR for 2024-02-10 is 565 (row 101) instead of 578 (row 103) is it a mistake?

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

Back To Top
Search