Group results of queries

Question

The process below makes one query per day between the initial and final dates, so it brings several queries, there is a way to group all queries generated in only one? And yet, how to eliminate the various results?

DELIMITER //
CREATE PROCEDURE pro_get_balanco_entre_datas(IN data_ini DATETIME, IN data_fin DATETIME)
BEGIN
DECLARE dataini DATE;
DECLARE datafin DATE;

SET dataini = DATE(data_ini);
SET datafin = DATE(data_fin);

REPEAT

SELECT *
FROM saldo
WHERE DATE(data_mov_saldo) = DATE(dataini)
ORDER BY data_mov_saldo DESC
LIMIT 1;

SET dataini = DATE_ADD(dataini, INTERVAL 1 DAY);
UNTIL dataini > datafin END REPEAT;
END //

Answer 1

Use the operator BETWEEN to make date range queries.

Example:

SELECT *
FROM saldo
WHERE DATE(data_mov_saldo) BETWEEN DATE(dataini) AND DATE(datafin) 
ORDER BY data_mov_saldo DESC

That way you can eliminate the REPEAT.

To take the last amount of each day, do something like this:

SELECT DATE(data_mov_saldo) as data_mov_saldo, valor
FROM 
(
  SELECT data_mov_saldo as data_mov_saldo, valor, 
         ( 
           CASE DATE(data_mov_saldo) 
           WHEN @curData 
           THEN @curRow := @curRow + 1 
           ELSE @curRow := 1 AND @curData := DATE(data_mov_saldo) END
         ) + 1 AS rn
  FROM saldo, (SELECT @curRow := 0, @curData := '') r
  WHERE DATE(data_mov_saldo) BETWEEN DATE('2015-06-01') AND DATE('2015-06-30') 
  ORDER BY data_mov_saldo DESC
) result
WHERE result.rn = 2

Sqlfiddle Demo

Explanation

First of all I need to create variables in the query that will enable me to make an equivalent to ROW_NUMBER() next to the PARTITION BY.

(SELECT @curRow := 0, @curData := '') r

Within the CASE we increase the line while the date is the same day (DATE) independent of the time. It is the @curData which allows me to make this grouping of counters.

( 
   CASE DATE(data_mov_saldo) 
   WHEN @curData 
   THEN @curRow := @curRow + 1 
   ELSE @curRow := 1 AND @curData := DATE(data_mov_saldo) END
) + 1 AS rn

At last I command data_mov_saldo DESC to pick up the last record of the day and on SELECT from outside do result.rn = 2 to catch only the 1° registration of each grouping.