Filter records via datetime and bring them between an initial and final date

Question

I have the balance table, that always when running a precedent, It is generated a new record in the same, You can have several record in the same day... However, when making a query, I want to bring only the last record of each day, so I did so:

DELIMITER //
CREATE PROCEDURE pro_get_balanco_diario(IN var_data DATETIME)
BEGIN
SELECT *
FROM saldo
WHERE DATE(data_mov_saldo) = var_data
ORDER BY data_mov_saldo DESC
LIMIT 1;
END //

However, now I need to do this query bringing the data between a date range, that is, bring the data between an initial date and a final date, but bringing only the last record of each day, as in the above process.

The table fields are shown in the image.

Answer 1

try like this:

SELECT 
    entradas            AS ENTRADAS,
    saidas              AS SAIDAS,
    saldo               AS SALDO,
    MAX(data_mov_saldo) AS DATA 
    FROM saldo 
        WHERE data_mov_saldo  BETWEEN '10/10/2016' AND '20/10/2016'
            GROUP BY data_mov_saldo,entradas,saidas,saldo

Answer 2

First you need to create a subquery that returns the date/time of the last movement of each day of the requested interval.

The example below shows what should be the result of the subquery based on the values contained indata_mov_saldo:

saldo                         resultado da subquery
data_mov_saldo                dia          ultima_mov
03.01.2017 10:00:00           03.01.2017   03.01.2017 11:00:00
03.01.2017 11:00:00           04.01.2017   04.01.2017 13:00:00
04.01.2017 12:00:00           05.01.2017   05.01.2017 15:00:00
04.01.2017 13:00:00
05.01.2017 14:00:00
05.01.2017 15:00:00

Then, just JOIN the subquery result with the table saldo as the query below:

select * 
  from (select date(data_mov_saldo) as dia
              ,max(data_mov_saldo) as ultima_mov
         from saldo
        where date(data_mov_saldo) between '2017-01-01' and '2017-01-10'
        group by date(data_mov_saldo)) as mov 
  join saldo on saldo.data_mov_saldo = mov.ultima_mov;

For testing

I created an example that runs online on http://sqlfiddle.com/#! 9/a41cff/1

Answer 3

in the clause where you put WHERE column_data_name BETWEEN data_value1 AND data_value2;
example

 SELECT * FROM table_name WHERE column_data BETWEEN '01/01/2010' AND '30/01/2010';