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/ > > Baron >
Baron, 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. Thanks, cj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
