Hi Christian,
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?
cj
I have read through Baron's solution and I think I can help you do it
just a bit quicker. It won't all be in a single statement, although it
could be written that way.
I am assuming that you are going to need to so this for a range of dates
@start_date to @end_date. The variables aren't important except to help
us limit how much work we need to do.
step 1 - Start off by capturing a list of all of the dates and
scale_readings between @start_date and @end_date. The last reading will
have nothing to be subtracted from as the next date may not yet exist
(if @end_date is today) so this table will serve as our source data for
the next steps. This means that step 2 will have the smallest possible
JOIN to perform.
CREATE TEMPORARY TABLE tmp_source
SELECT `date`, `scale_reading`
FROM sourcetableorview <-- this is wherever your data is coming from, now.
WHERE `date` BETWEEN @start_date and @end_date;
step2 - Build a pair table of dates. We need to index the
tmp_source.`date` to speed this up. This step will become geometrically
slower the more dates you want to process at once. The `scale_reading`
is part of the index in order to speed up step 3.
ALTER TABLE tmp_source ADD KEY(`date`,`scale_reading`);
CREATE TEMPORARY TABLE tmp_datepairs
SELECT t_s1.`date` as date1, MIN(t_s2.`date`) as date2
FROM tmp_source t_s1
INNER JOIN tmp_source t_s2
ON t_s1.`date` < t_s2.`date`
GROUP BY t_s1.`date`;
step 3 - Use the tables we generated from steps 1 and 2 to build your
final report. I indexed both columns on tmp_datepairs in order to speed
this up.
ALTER TABLE tmp_datepairs ADD KEY(date1), ADD KEY(date2);
SELECT td.`date1` as startdate
, td.`date2` as enddate
, ts1.scale_reading as startwt
, ts2.scale_reading as endwt
, ts2.scale_reading - ts1.scale_reading as diff
FROM tmp_datepairs td
INNER JOIN tmp_source ts1
on td.date1 = ts1.`date`
INNER JOIN tmp_source ts2
on ts.date2 = ts2.`date`
step 4 - always (!!) clean up after yourself
DROP TEMPORARY TABLE IF EXISTS tmp_source, tmp_datepairs;
If you have any questions about the logic, please feel free to ask.
--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
<___/
Join the Quality Contribution Program Today!
http://dev.mysql.com/qualitycontribution.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
