0

I would like to have a MySQL query that given a specified date it will return the last 7 days of results, but in the MySQL table there could be blanks.

So the table could look like this

tblMyData

TestDate    |   val1   |   val2 
2014-07-10  |   20     |   30 
2014-07-09  |   10     |   10
2014-07-07  |   11     |   22
2014-07-04  |    9     |   45

However my query would need to fill in the blanks so my results would look like this

TestDate    |   val1   |   val2 
2014-07-10  |   20     |   30 
2014-07-09  |   10     |   10
2014-07-08  |    0     |    0     <<-- Added by the query
2014-07-07  |   11     |   22
2014-07-06  |    0     |    0     <<-- Added by the query
2014-07-05  |    0     |    0     <<-- Added by the query
2014-07-04  |    9     |   45

Any ideas how I can do this?

fthiella
  • 48,073
  • 15
  • 90
  • 106
Ian Chilvers
  • 161
  • 1
  • 7

2 Answers2

2

One solution is to generate the dates with a subquery, and then join this subquery with your table.

If you only need the last 7 days then you can try with this:

select d.testdate, coalesce(t.val1,0), coalesce(t.val2,0)
from
  (select current_date as testdate
   union all select current_date - interval 1 day
   union all select current_date - interval 2 day
   union all select current_date - interval 3 day
   union all select current_date - interval 4 day
   union all select current_date - interval 5 day
   union all select current_date - interval 6 day) d
  left join tblMyData t
  on d.testdate = t.testdate

if instead of the current_date, you want the last 7 days in the table, then your query can be like this:

select m.m - interval d day, coalesce(t.val1,0), coalesce(t.val2,0)
from
  (select max(testdate) as m from tblMyData) m
  cross join
  (select 0 as d
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6) d
  left join tblMyData t
  on m.m - interval d day = t.testdate

Please see a fiddle here.

fthiella
  • 48,073
  • 15
  • 90
  • 106
  • For an alternative approach to populating a subquery with a set of dates (aliased as `d` in the above answer), see this post: http://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query. This may be desired if your possible range of dates far exceeds 7 days. – Nate Jan 05 '16 at 18:23
0

If you can use the SEQUENCE ENGINE you can do it like this. This Sample is for the last 100 day, but you can change it.

INSERT IGNORE INTO tblMyData
SELECT DATE(now() - INTERVAL seq DAY),0,0 FROM seq_0_to_100;
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39