Getting last highest value if current date = 0?

[RJP1]

Hi guys,

 

I have a simple table like so:

 

id timestamp value

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

30 1299784130 10000.00

31 1300168969 10500.00

 

Using the following SQL query I am able to get the higest value per day in a date range including days that don't exist in the database (for graphing):

 

SELECT Y.day, MAX(COALESCE(Z.value,0)) as value

FROM (SELECT DATE_ADD( '2011-01-01', INTERVAL a.i + b.i *10 + c.i *100

DAY ) AS DAY

FROM integers a

CROSS JOIN integers b

CROSS JOIN integers c

HAVING DAY BETWEEN '2011-03-10' AND '2011-03-20') Y

LEFT OUTER JOIN `table` Z ON Y.day = DATE(FROM_UNIXTIME(Z.timestamp))

GROUP BY Y.DAY

ORDER BY Y.DAY ASC

 

This yields:

 

day               value

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

2011-03-10 10000.00

2011-03-11 0.00

2011-03-12 0.00

2011-03-13 0.00

2011-03-14 0.00

2011-03-15 10345.47

2011-03-16 0.00

2011-03-17 0.00

2011-03-18 0.00

2011-03-19 0.00

2011-03-20 0.00

 

Which is lovely. However, can someone advise how I alter the query to ensure the dates between 2011-03-10 and 2011-03-15 have 10000.00 as their value rather than zero? In other words, if the value comes out as zero, can you help me make it check backwards in time to check for the highest previous value? Thanks!

 

RJP1

[fenway]

Sorry -- you want to make it 10000 if it's zero?

[RJP1]

Not as such, I want the days that haven't got a value to have the value of the first day previous to the zero day if that makes sense!

 

In this case 2011-03-11, 2011-03-12, 2011-03-13, 2011-03-14  would = 10000 (because 2011-03-10 = 10000) and 2011-03-16, 2011-03-17, 2011-03-18, 2011-03-19 and  2011-03-20  to = 10345.47 (because 2011-03-15 = 10345.47).

 

Doable? RJP1

[fenway]

Then why not check the max value for the max date in that range?

[RJP1]

How do you suggest I do that within my existing query? Could you possibly point me in the right direction? Cheers!

 

RJP1

[RJP1]

Can anyone help with this one? I'm struggling big time!

 

RJP1

[fenway]

I'm trying to understand why you can't simply use a derived table, get the max for each possible item, and then restrict by your desired date range.

[RJP1]

Well I'd love to, I just don't know how - hence my asking here!

 

Cheers,

 

RJP1

[fenway]

You need to write a query, separately that return the max value for a given value, and then you can easily join that in, and then easily use 1000 or the other value.