Group Query Results

Question

Group Query Results

Navigation

#1 by (1 votes)

0

The procedure below makes a query per day between the start and end dates, so it brings up multiple queries, is there a way to group all queries generated in just one? Also, how to eliminate the empty 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 //

mysql while stored-procedures

asked by anonymous 23.06.2015 / 01:42

1 answer

Component select2 with AngularJS Retrieve data in form in jQuery

score 1 · Accepted Answer

Use the BETWEEN operator to do date range queries.

Example:

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

This way you can eliminate REPEAT .

To get the last value 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 of ROW_NUMBER() next to PARTITION BY .

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

Within CASE we will increment the row while the date is on the same day ( DATE ) regardless of time. It is the @curData that allows me to do 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

Finally I order data_mov_saldo DESC to get the last record of the day and in the SELECT outside I result.rn = 2 to get only the 1st record of each grouping.