Show the previous date from an incomplete date list in MySQL

Question

I have a database where records are not kept every day. However I need to calculate the balance change between dates where there are records (ignoring dates where there are no entries).

For example I would like a result that looks like this:

business_date   balance prev_date       prev_balance    diff
2014-01-16      -2648   2014-01-13      436            -3084
2014-02-04       37     2014-01-16     -2648            2685
2014-02-11       37     2014-02-04      37              0
2014-02-12       37     2014-02-11      37              0
2014-02-21      -9633   2014-02-12      37             -9670
2014-03-03       396    2014-02-21     -9633           10029
2014-04-02      -10252  2014-03-03      396           -10648
2014-04-03      -7033   2014-04-02     -10252          3219
2014-05-21      -6849   2014-04-03     -7033           184

Unfortunately I do not know the interval between the current and previous dates, as it is always different. So I can't use something like DATE_SUB.

This is what I've tried:

select * from

(select business_date, balance
from table1
order by business_date) d

LEFT JOIN

(select business_date as prev_date,balance as prev_balance
from table2
order by business_date) a

on a.prev_date > d.business_date

But this returns only a bunch of nulls for the prev columns.

Everything I've read on this subject seems to involve creating a table of all dates, but this won't work for me as my list of dates is supposed to be incomplete, I only want to include dates for which we have records.

Any suggestions on how to approach this would be very helpful, thank you.

Jim Mc · Answer 1 · 2015-03-17T17:37:58.870

0

In your first derived table 'd', generate a row number and include it in the table. Then in your second derived table 'a', generate the same row numbers. Then join 'a' to 'd' using d.rowno-1= a.rowno for calculating the diff.

Here's what I wrote to do this.

select * from
(
    SELECT * FROM
    (
        SELECT @curRowd := @curRowd + 1 AS row_number, business_date, balance
        FROM bals
    ) dr
    cross JOIN 
    (
        SELECT @curRowd:=0 AS Rowdinit
    ) initd
) d
LEFT JOIN 
(
    SELECT * FROM
    (
        SELECT @curRowa := @curRowa + 1 AS row_number, business_date, balance
        FROM bals
    ) dr
    cross JOIN 
    (
        SELECT @curRowa:=0 AS Rowainit
    ) initd
) a ON a.row_number = d.row_number-1

edited Mar 17 '15 at 17:37

answered Mar 16 '15 at 21:07

Jim Mc

336
1
10

This seems like it would work. However I do not know how to generate row numbers in MySQL (I always thought MySQL doesn't do row numbers?). Everything I've looked up about generating row numbers in MySQL seems to use variables, which I'm not familiar with (maybe today is the day to learn?) Also, my first table is table d, my second table is table a, so your reply is a bit confusing. – Hopper06 Mar 16 '15 at 21:31
Yes, using variables is the way to generate row numbers in mysql. – Jim Mc Mar 16 '15 at 23:32
Thank you, I read the page about how to create row numbers, and it works on an isolated table. But when I tried to join it with my second table, no data returns. This is what I did: select * from (select [at]curRow := [at]curRow + 1 AS row_number, business_date, balance from table 1 JOIN (SELECT [at]curRow := 0) r ) d LEFT JOIN (select [at]curRow := [at]curRow + 1 AS row_number, business_date, balance from table 2 JOIN (SELECT [at]curRow := 0) s ) a on d.row_number-1 = a.row_number – Hopper06 Mar 17 '15 at 12:48
I thank you for all the effort you've put into trying to help me. I tried your solution, with the same result. When I process the indivudual tables, I get a very nice result with row numbers. But once I do that Left join, it returns no results. It's a bit baffling to me, frankly. – Hopper06 Mar 19 '15 at 18:45

Show the previous date from an incomplete date list in MySQL

1 Answers1