On Aug 14, 2007, at 8:38 AM, 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/
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
Hi Christian,
I don't know if you are in control of the data, But would it be
possible to add a column to the database something like "Read" and
have the value either 1 or 0? then do something like: Select * from
view ordered by date where Read="1";?That way you wouldn't have any
gaps for fields and could then just do the math fairly easily I think...
But I'm just starting out with MySQL so I may have made a huge
mistake :) In fact... It's quite probable :)
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
