MySQL Stored Procedure Loop Increment Date In Select Query Returns Last Date

Question

I have a stored procedure which increments a specific date in a select query. I need to get the current value of the incrementing date variable along with other fields. But the query always returns the last incremented date only. Can you please show me where is the error?.

Stored Procedure:

CREATE PROCEDURE `IBE_getAvailabilityForRange`(IN firstStayDate DATE, IN lastStayDate DATE)
BEGIN
DECLARE nextDate DATE;
SET nextDate = firstStayDate;
WHILE nextDate <= lastStayDate DO
SELECT nextDate as stayDate, Room_type, No_of_room, ArrivalDate, DepartDate, state FROM reservation_temp WHERE Reservation_is_done = 1 AND state != 0 AND ArrivalDate <= nextDate AND DepartDate > nextDate;
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 DAY);
END WHILE;
END

Result it output when called call IBE_getAvailabilityForRange('2019-02-25', '2019-02-27');

[EDIT] I need it to output results for 2019-02-25, 2019-02-26, 2019-02-27. Currently, it gives me results for 2019-02-27 only.

[EDIT: Edited stored procedure with GROUP by and SUM]

BEGIN
DECLARE nextDate DATE;
SET nextDate = firstStayDate;
WHILE nextDate <= lastStayDate DO
SELECT nextDate as stayDate, Room_type, SUM(No_of_room), ArrivalDate, DepartDate, state FROM reservation_temp WHERE Reservation_is_done = 1 AND state != 0 AND ArrivalDate <= nextDate AND DepartDate > nextDate GROUP BY stayDate, Room_type;
SET nextDate = DATE_ADD(nextDate, INTERVAL 1 DAY);
END WHILE;

That actually looks like you're **only** seeing the results of the last iteration of the `WHILE` loop as all of those results are valid results for `nextDate = '2019-02-27'`. It sounds like a problem in the way you are retrieving the results from the stored procedure. — Nick, Feb 22 '19 at 02:03
@nick, it should be the case. Can you show me how to get the results for other dates as well. — Johna, Feb 22 '19 at 02:05
What is your desired results? The issue is caused by the `SELECT` statement iterating over the rowset for the day, prior to incrementing `nextDate`. As a result your procedure will execute multiple `SELECT` statements for each day, showing the last executed in your query tool. Without knowing how you want the results, we can't really offer an appropriate solution. i.e. Show duplicate records for each `stayDate` that match the criteria (`UNION`), a single row for each `stayDate` or filter out the duplicates somehow. — Will B., Feb 22 '19 at 02:07
@Johna are you executing the query directly in MySQL workbench or phpMyAdmin or from an application framework? If the latter, you need to add your code into the question. — Nick, Feb 22 '19 at 02:10
@fyrye I have edited the stored procedure, please check. I need to get **Room_type** and sum of **No_of_room** per each day. — Johna, Feb 22 '19 at 02:17
I would start with the bigger picture. See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Feb 22 '19 at 07:16

Will B. · Accepted Answer · 2019-02-22T03:32:48.183

As mentioned in my comment, the issue is caused by the SELECT statement iterating over the rowset for the day, prior to incrementing nextDate. As a result your procedure will execute multiple SELECT statements for each day, and return the result from the last executed query.

i.e:

SELECT day1;
SELECT day2;

To resolve the issue in the manner I believe you are looking to achieve, one method is to use a temporary table to store the retrieved records for each day until each day is processed, then retrieve the values from the temporary table.

However you need to better define your GROUP BY columns for ArrivalDate, DepartDate, and state. As MySQL 5.7+ has ONLY_FULL_GROUP_BY enabled by default and will error. I have used MIN, MAX and added state to the groupings to prevent errors.

Alternatively explicitly use ANY_VALUE() on the columns, to allow MySQL to choose a value within each group that has not been aggregated. [sic]

CREATE PROCEDURE `IBE_getAvailabilityForRange`(IN firstStayDate DATE, IN lastStayDate DATE)
BEGIN
DECLARE nextDate DATE;

DROP TEMPORARY TABLE IF EXISTS tmp_stays;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_stays(
    stayDate DATE,
    Room_type INT(10),
    rooms INT(10),
    ArrivalDate DATE,
    DepartDate DATE,
    state INT
);

SET nextDate = firstStayDate;
WHILE nextDate <= lastStayDate DO
    INSERT tmp_stays
    SELECT
       nextDate, 
       Room_type, 
       SUM(No_of_room), 
       MIN(ArrivalDate), 
       MAX(DepartDate), 
       state
    FROM reservation_temp 
    WHERE Reservation_is_done = 1 
    AND state != 0 
    AND ArrivalDate <= nextDate 
    AND DepartDate > nextDate 
    GROUP BY nextDate, Room_type, state;

    SET nextDate = DATE_ADD(nextDate, INTERVAL 1 DAY);
END WHILE;

SELECT * FROM tmp_stays;
DROP TEMPORARY TABLE IF EXISTS tmp_stays;
END

With the dataset of (note that id 1 and 2 have differing DepartDate values)

reservation_temp
---
| id | Room_type | state | No_of_room | ArrivalDate | DepartDate | Reservation_is_done | 
| ---: | ---: | ---: | ---: | --- | --- | ---: | 
| 1 | 1 | 1 | 1 | 2019-01-01 | 2019-01-02 | 1 | 
| 2 | 1 | 1 | 1 | 2019-01-01 | 2019-01-03 | 1 | 
| 3 | 1 | 1 | 1 | 2019-01-03 | 2019-01-04 | 1 | 
| 4 | 1 | 1 | 1 | 2019-01-04 | 2019-01-04 | 1 | 
| 5 | 1 | 1 | 1 | 2019-01-04 | 2019-01-05 | 1 |

Result

/* CALL IBE_getAvailabilityForRange('2019-01-01', '2019-01-05'); */

tmp_stays
---
| stayDate | Room_type | rooms | ArrivalDate | DepartDate | state | 
| --- | ---: | ---: | --- | --- | ---: | 
| 2019-01-01 | 1 | 2 | 2019-01-01 | 2019-01-03 | 1 | 
| 2019-01-02 | 1 | 1 | 2019-01-01 | 2019-01-03 | 1 | 
| 2019-01-03 | 1 | 1 | 2019-01-03 | 2019-01-04 | 1 | 
| 2019-01-04 | 1 | 1 | 2019-01-04 | 2019-01-05 | 1 |

This answer solves the problem I have described here. So I accepted it as the answer. However, I found another simple approach to get the result I need. — Johna, Feb 22 '19 at 08:22

score 0 · Answer 2 · answered Feb 22 '19 at 02:05

0

I recommend you don't bloat a simple query with a stored procedure:

If you are looping in a stored procedures over something a query could be doing its very inefficient.

So this procedure could be replace with a single query:

SELECT Room_type, No_of_room, ArrivalDate, DepartDate, state
FROM reservation_temp
WHERE Reservation_is_done = 1
      AND state != 0
      AND ArrivalDate <= {arrivedate}
      AND {enddate} < DepartDate

Is reservation_temp really a temporary table?

answered Feb 22 '19 at 02:05

danblack

12,130
2
22
41

Actually, **reservation_temp** is not a temporary table. That is how the developers have named the table. – Johna Feb 22 '19 at 02:22
Wow, that's just asking for a person to drop it. – danblack Feb 22 '19 at 02:24

score -1 · Answer 3 · answered Feb 22 '19 at 04:29

You may want to look at this discussion: generate days from date range

Using some queries from the discussion you can get a list of dates from your dates range. Then just inner/left join your data and you get what you want.

With my implementation (I have helper table + view, but this is not required), the resulting query would look something like this:

SELECT
      Dates.d
    , reservation_temp.*
FROM
    global.Dates
    INNER JOIN reservation_temp ON (
            Reservation_is_done = 1
        AND state != 0
        AND ArrivalDate <= Dates.d
        AND DepartDate > Dates.d
    )
WHERE
    Dates.d BETWEEN firstStayDate AND lastStayDate

MySQL Stored Procedure Loop Increment Date In Select Query Returns Last Date

3 Answers3

Result