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

Question

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

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I have the balance table, which always when executing a procedure, is generated a new record in the same, being able to have several record the same day ... However, when I make an appointment, 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, I now need to do this query by bringing the data between a date range, ie bringing the data between a start date and an end date, but bringing only the last record for each day, as in the above procedure. / p>

The fields of the tables are shown in the image.

mysql datetime procedure stored-procedures

asked by anonymous 19.06.2015 / 19:57

3 answers

confirmDialog does not work Receiving Site Information

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

0

in the where clause 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';

19.06.2015 / 20:19

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 range.

The following example shows what should be the result of the subquery 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

So, just do the JOIN of the result of the subquery 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've created an example that runs online at link