John Davidson

Message:

I have a table with 2 columns, date and score. It has at most 30 entries, for each of the last 30 days one.

date      score

-----------------

1.8.2010  19

2.8.2010  21

4.8.2010  14

7.8.2010  10

10.8.2010 14

My problem is that some dates are missing - I want to see:

date      score

-----------------

1.8.2010  19

2.8.2010  21

3.8.2010  0

4.8.2010  14

5.8.2010  0

6.8.2010  0

7.8.2010  10

...

What I need from the single query is to get: 19,21,9,14,0,0,10,0,0,14... That means that the missing dates are filled with 0.

I know how to get all the values and in server side language iterating through dates and missing the blanks. But is this possible to do in mysql, so that I sort the result by date and get the missing pieces.

EDIT: In this table there is another column named UserID, so I have 30.000 users and some of them have the score in this table. I delete the dates every day if date < 30 days ago because I need last 30 days score for each user. The reason is I am making a graph of the user activity over the last 30 days and to plot a chart I need the 30 values separated by comma. So I can say in query get me the USERID=10203 activity and the query would get me the 30 scores, one for each of the last 30 days. I hope I am more clear now.

---

My Answer:
You can achieve this by creating a temporary table that contains all the dates in the range you are interested in, and then left joining your original table with this temporary table. Here's an example query:

sql
CREATE TEMPORARY TABLE temp_dates (date DATE);

SET @start_date = CURDATE() - INTERVAL 29 DAY;

WHILE @start_date <= CURDATE() DO
INSERT INTO temp_dates (date) VALUES (@start_date);
SET @start_date = @start_date + INTERVAL 1 DAY;
END WHILE;

SELECT temp_dates.date, COALESCE(your_table.score, 0) AS score
FROM temp_dates
LEFT JOIN your_table ON temp_dates.date = your_table.date
ORDER BY temp_dates.date;


This query will create a temporary table `temp_dates` that contains all the dates in the last 30 days. It then left joins this temporary table with your original table, filling in missing dates with a score of 0. The `COALESCE` function is used to replace `NULL` values with 0. Finally, the result is ordered by date.