Question 261997 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL Query not including current month data

@Query(value = "SELECT o.restaurant_id as restaurantId, YEAR(o.date_created) as year, MONTH(o.date_created) as month, "
             + " count(1) as tpn, sum(o.total) as tpv "
             + " FROM ordersDB.orders o "
             + " WHERE o.provider_id = :providerId "
             + " AND o.status != 2"
             + " AND o.restaurant_id = :restaurantId "
             + " AND o.date_created BETWEEN :dateFrom AND :dateUntil "
             + " GROUP BY 1,2,3 ", nativeQuery = true)
    List<IReportOrderEvolutionDto> getRestaurantProviderEvolution(Long providerId, Long restaurantId, LocalDate dateFrom, LocalDate dateUntil);

The problem I’ve got is that It only returns data from past months, not the current one.

If I select from 05/2023 to 10/2023, it will only return data from orders up to 09/30/2023 23:59, despite having orders created in October.

I also tried something like this:

SELECT o.restaurant_id as restaurantId, YEAR(o.date_created) as year, MONTH(o.date_created) as month, 
            count(1) as tpn, sum(o.total) as tpv 
             FROM ordersDB.orders o 
             WHERE o.provider_id = 1
            AND o.status != 2
             AND o.restaurant_id = 4
             AND MONTH(o.date_created) BETWEEN MONTH(DATE('2023-05-01')) AND MONTH(DATE('2023-10-31')) 
             AND YEAR(o.date_created) BETWEEN YEAR(DATE('2023-05-01')) AND YEAR(DATE('2023-10-31'))
            GROUP BY 1,2,3

But it didn’t work either.

Thanks a lot.

Tags:

2

Answers


  1. 0

    The problem I’ve got is that It only returns data from past months, not the current one.

    I see that problem with your query is that you use MONTH() and YEAR() functions to compare the dates to the beginning and end of the month, respectively. It means that any orders placed in the current month will be excluded, since they will have a month value > the current month and a year value > the current year.

    I tried to fix the issue this way:

    SELECT o.restaurant_id AS restaurantId,
       YEAR(o.date_created) AS year,
       MONTH(o.date_created) AS month,
       COUNT(1) AS tpn,
       SUM(o.total) AS tpv
FROM ordersDB.orders o
WHERE o.provider_id = :providerId
AND o.status != 2
AND o.restaurant_id = :restaurantId
AND YEAR(o.date_created) >= YEAR(DATE('2023-05-01'))
AND YEAR(o.date_created) <= YEAR(DATE('2023-10-31'))
AND MONTH(o.date_created) >= MONTH(DATE('2023-05-01'))
AND MONTH(o.date_created) <= MONTH(LAST_DAY(CURRENT_DATE))
GROUP BY 1, 2, 3;

    Let me know if it works for you, please.

    Login or Signup to reply.
  2. 0

    If your dates are always the first day of the month you want to include, then you’ll want to compare your end value to the month following the end date, because that date exists at the boundary of your date range.

    SELECT o.restaurant_id as restaurantId, YEAR(o.date_created) as year, 
MONTH(o.date_created) as month, count(1) as tpn, sum(o.total) as tpv
FROM ordersDB.orders o WHERE o.provider_id = :providerId
AND o.status != 2 AND o.restaurant_id = :restaurantId
AND o.date_created >= :dateFrom 
AND o.date_created < :dateUntil + INTERVAL 1 MONTH
GROUP BY 1,2,3
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search