On 8/14/07, Baron Schwartz <[EMAIL PROTECTED]> wrote: > Hi, > > Christian High wrote: > > On 8/14/07, Baron Schwartz <[EMAIL PROTECTED]> wrote: > >> Hi, > >> > >> Christian High wrote: > >>> I have a table that includes a date and a scale reading like > >>> > >>> date scale_reading > >>> 2007-08-01 150 > >>> 2007-08-02 125 > >>> > >>> these reading may or may not be taken everyday. I need to develop a > >>> query that will subtract the scale reading on one day from the scale > >>> reading on the next most recent reading. any ideas? > >> This may explain what you're looking for: > >> > >> http://www.xaprb.com/blog/2007/02/19/how-to-subtract-in-sql-over-samples-that-wrap/ > > Very nice article that I no doubt can make use of. But in this > > instance, unless I am missing it, it doesn't help. My problem seems to > > be that the data is not necessarily entered in order. For example the > > users may enter July 7th data then enter July 5th data. I can get > > around this by ordering on the date. The problem that I cannot seem to > > get around is they may not have any data for a particular date. when I > > tried test the suggestions in the article against my data if there was > > a hole in the date column, which is what i joined on because it needs > > to subtract one day from the next most recent, it excluded these > > instances because it did not meet the join criteria. > > > > t1 is a select * view ordered by date on the above mentioned table > > > > select t1.date_column, t1.reading_column, p.date_column, p.reading_column > > join t1 p on > > (t1.date_column = p.date_column + 1). > > > > If I am missing something I would appreciate it if someone could point it > > out. > > > > I think what I need is a way to find the most recent date as commpared > > with a given date whether that date is the day before or 3 days > > before. > > I misunderstood your question. I think what you need is something like > the following. Working from the inside out, > > 1) find the most recent date previous to the current date > > select max(date) from t1 as inner_t1 where inner_t1.date < ? > > This query will not run efficiently; MySQL can't yet optimize it. A > logical equivalent that will be fast, if date is indexed, will be: > > select date from t1 as inner_t1 where inner_t1.date < ? > order by date desc limit 1 > > That's the most recent date before any given date and will be the inmots > query. Now we need to find the corresponding scale_reading: > > select scale_reading from t1 as mid_t1 > where mid_t1.date = ( ... inmost query ... ) > > That's the middle query. Now you can place that in a subquery: > > select date, scale_reading - ( ... middle query ... ) > from t1 as outer_t1 > > Finally, resolve the ? reference in the correlated subquery: > > select date, scale_reading - ( > select scale_reading from t1 as mid_t1 > where mid_t1.date = ( > select date from t1 as inner_t1 where inner_t1.date < outer_t1.date > order by date desc limit 1 > ) > ) > from t1 as outer_t1 > > I'm not attempting to run this, just reasoning about it, so I might be > wrong or there might be a syntax error. > > The next issue is t1 is a view -- it might perform terribly. You might > be better off doing it another way, either querying the tables directly, > or using a user variable: > > set @most_recent_reading := null; > select date, > scale_reading - @most_recent_reading, > @most_recent_reading := scale_reading > from t1; > > Again untested, but hopefully you get the idea. > > Baron >
Barron, That is exactly what I was looking for. I can work out whatever bugs or syntax errors there may be I just couldn't get my head completely around the logic. Thank you for your time. Cj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
