Filters records via datetime and trace them between a start and end date

Question

Filters records via datetime and trace them between a start and end date

Balance table

I have the balance table, which always when performing a procedure, a new record is generated in it, and can have several records on the same day... However, when making a query, I want to bring only the last record of each day, so I did this:

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 by bringing the data between a date range, i.e. bringing the data between a start date and an end date, but bringing only the last record of every day, as in the above procedure.

The table fields are shown in the image.

1

mysql datetime procedure stored-procedures

Author: rodrigom, 2015-06-19

Source

3 answers

score 1 · Answer 1

Try 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

score 0 · Answer 2

In 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';

0

Author: Hebert Lima, 2015-06-19 18:58:47

score 0 · Answer 3

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

The example below shows what the subquery result should be based on the values contained in data_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 per 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;

To test

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