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

I need to gather 15 consecutive days of records even if some days have none, not just the last 15 – Mysql

I have a table with dates and several columns of data. Most weeks every day will have entries but some will not. I have a chart that I want to load the last 15 days of accumulated data into but it only displays 14 days if a day is missing. How do I get 15 actual dates rather than the last 15 calander spanning dates?

EDIT: My solution was adding this line below to the WHERE clause that eliminates Sundays from my result:

AND dayofweek( date ) > 1

SELECT COUNT(name) AS days,
       SUM(act_del_stops+act_pu_stops) AS stops, 
       SEC_TO_TIME(SUM(TIME_TO_SEC(on_road_hrs))) AS on_rh,  
       date AS date3
       FROM service_data 
       WHERE date >= (select MAX(date) FROM service_data)  - INTERVAL 15 DAY
       GROUP BY date  
       ORDER BY date ASC
Tags:

2

Answers


  1. 0

    As @Stu mentions, you need to join to a table that includes all of that data. One way to do that is to create a temporary table, like this:

    CREATE TEMPORARY TABLE numbers (n INT);
INSERT INTO numbers VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14);

    You could then perform the outer join against that table. Perhaps like this:

    SELECT COUNT(name) AS days,
   SUM(act_del_stops+act_pu_stops) AS stops, 
   SEC_TO_TIME(SUM(TIME_TO_SEC(on_road_hrs))) AS on_rh,  
   date AS date3
 FROM service_data
   OUTER JOIN numbers on numbers.n = service_data.date
 WHERE date >= (select MAX(date) FROM service_data)  - INTERVAL 15 DAY
 GROUP BY date  
 ORDER BY date ASC

    And from experience, I believe this may need to be a RIGHT JOIN so all of the results from the numbers table are always included.

    Login or Signup to reply.
  2. 0

    Another option to dynamically build your calendar of every possible day within the 15-day range is with MySQL Recursive CTE. In the example below, the union allows the recursion to be based on the query above the union which is "sets" the initial values. Ex: the Select current_Date(), 1 will get whatever the current date is and also 1 for the first record. The UNION portion re-calls the same alias "MyCal" by subtracting 1 day from its current date start, but adding 1 to the day number UNTIL the day number gets to your 15 day cutoff.

    WITH RECURSIVE 
MyCal (calDate, dayNum) AS
(
   SELECT current_date(), 1 
   union all
   SELECT date_add( calDate, interval -1 day ), dayNum +1 
      from MyCal 
      where dayNum < 20 
)
SELECT * FROM MyCal

    Now that you have that as the basis of calendar days to work with, just LEFT-JOIN to your production data to get the other parts. So, leaving the With Recursive MyCal, just change the part select * from MyCal above to

    select
      m.CalDate,
      sd.days,
      sd.stops,
      sd.on_rh
   from
      ( select * from MyCal ) m
         left join
         ( SELECT 
                 date AS date3,
                 COUNT(name) AS days,
                 SUM(act_del_stops+act_pu_stops) stops, 
                 SEC_TO_TIME(SUM(TIME_TO_SEC(on_road_hrs))) on_rh
              FROM 
                 service_data 
              WHERE 
                 date >= date_add( current_date(), interval -20 day )
              GROUP BY 
                 date ) sd
            on m.CalDate = sd.date3
   where
      -- this will exclude Sundays
      dayofweek( m.CalDate ) > 1
   order by
      m.CalDate DESC
   limit 15

    Sorry about the prior issues, been a bit unclear from current medication drowziness. Anyhow, above should now be good. I changed to that each component of the query is a pre-aggregate first, THEN joined the results. The MyCal table is the primary to qualify ONLY the 15 days in question. The LEFT JOIN to your query only pre-aggregates the last 15 days and no longer plows through every date.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search