Mysql query for latest value when date is missing

Question

I have a table with a date and value column. The table is populated daily with new values. On occasion the population does not take place. I need to generate a report of the latest value on each past Monday. This query shows that no data was collected on 2013-02-18.

mysql> select date from table where dayofweek(date) = 2;

+------------+
| date       |
+------------+ 
| 2013-02-04 | 
| 2013-02-11 | 
| 2013-02-25 | 
| 2013-03-04 | 
| 2013-03-11 | 
| 2013-03-18 | 
| 2013-03-25 | 
+------------+ 
7 rows in set (0.00 sec)

So I'd like to get the latest previous value for that Monday. I can do that with <= max(date) separately if I know the missing date like this:

mysql> select max(date) from table where date <= "2013-02-18"; 
+------------+ 
| max(date) | 
+------------+ 
| 2013-02-14 | 
+------------+
1 row in set (0.00)

This shows that the latest value for 2013-02-18 was collected on 2013-02-14. So I need a single query that will return what the latest value was on each Monday. And if no value exists for that day use the latest prior existing value. My output should look like this:

+------------+
| date       |
+------------|
| 2013-02-04 |
| 2013-02-11 |
| 2013-02-14 |
| 2013-02-25 |
| 2013-03-04 |
| 2013-03-11 |
| 2013-03-18 |
| 2013-03-25 |
+------------+

Thanks in advance.

score 0 · Answer 1 · answered Mar 31 '13 at 06:30

Try this

SELECT 
    date 
FROM
    temp t
WHERE
    dayofweek(date) = 2 OR EXISTS   (
                                        SELECT 
                                            max( DATE )
                                        FROM    
                                            temp i
                                        WHERE
                                            WEEK( t.date ) = WEEK( i.date )
                                            AND NOT EXISTS ( SELECT 1 FROM temp WHERE WEEK( date ) = CASE WHEN WEEK( i.date ) = 53 THEN 0 ELSE WEEK( i.date ) + 1 END = WEEK( date ) AND YEAR( date ) = YEAR( i.date ) + CASE WHEN WEEK( i.date ) = 53 THEN 1 ELSE 0 END AND dayofweek(date) = 2 )
                                    )

The query works as,

displays the current row if its dayofweek = 2 OR if there are no records of dayofweek = 2 in the next week and the current date is maximum for the current week

I have also handled year changes for the week() condition

Here's the SQLFiddle

Hope this helps

Thanks for the help. But I eviden'ty didn't provide enough information. As it is this query returns 4858 rows. The data is normally entered into the table daily for a number of named objects. Here's your query for my environment: — user2225048, Apr 01 '13 at 13:38
mysql> SELECT date FROM temp t WHERE array like "arrayname" and dayofweek(date) = 2 OR EXISTS (SELECT max( DATE ) FROM temp i WHERE array like "arranyname" and WEEK( t.date ) = WEEK( i.date ) AND NOT EXISTS ( SELECT 1 FROM temp WHERE array like "arrayname" and WEEK( date ) = CASE WHEN WEEK( i.date ) = 53 THEN 0 ELSE WEEK( i.date ) + 1 END = WEEK( date ) AND YEAR( date ) = YEAR( i.date ) + CASE WHEN WEEK( i.date ) = 53 THEN 1 ELSE 0 END AND dayofweek(date) = 2 )); — user2225048, Apr 01 '13 at 13:43

Mysql query for latest value when date is missing

1 Answers1