How to calculate the difference between values in preceeding records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dmisen@gmail.com - 25 Feb 2006 13:21 GMT

I have data on air pollution coming from sources across the country,
and control options for reducing pollution from each source. The
example below shows data for:
- 2 sources (plant_ID "001" and "008")
- total tons of pollution emitted from each source (20 and 25 tons,
respectively)
- an ID code for the each control device that could be applied to
reduce the emissions
- the control efficiency for each control device (percent that it
would reduce the emissions)
- the tons that each control device would reduce the emissions

I need to create an Access query that will calculate the incremental
tons reduced ("inc_red"), i.e., the difference between the tons reduced
by the device listed in the current record minus the tons reduced by
the device in the previous record, if the previous record is for the
same plant. Eg., the 1st control device is listed in record # 2 and it
would reduce emissions a total of 10 tons. The 2nd control device is
listed in record # 3 and it would reduce emissions a total of 10 tons.
The incremental difference is calculated by subtracting the reduction
for record # 2 from the reduction for record # 3, which equals zero
(because they are both 10). I have greatly simplified the problem, but
this gets at the essence of my problem. I have a database of over
100,000 records for which I need to do similar calculations, and the
database is updated regularly.

ID plant_ID total_tons_emitted control_ID CE tons_reduced
inc_red
1 001 20 no control
2 001 20 N01 0.5 10 10
3 001 20 N08 0.5 10 0
4 001 20 N03 0.8 16 6
5 008 25 no control
6 008 25 N04 0.4 10 10
7 008 25 N07 0.6 15 5

Reply to this Message

Bob Quintal - 25 Feb 2006 15:07 GMT

> I have data on air pollution coming from sources across the
> country, and control options for reducing pollution from each
[quoted text clipped - 38 lines]
> 10 7 008 25 N07 0.6 15
> 5

You could use a subquery to return the preceding record, based
on matching plant_ID, Control_ID, and smaller ID or entrydate.

SELECT * from table,
table.Tons_reduced - (SELECT last(tons_reduced)
     FROM table ALIAS Subtable
     WHERE subtable.Control_ID = table.control_id
     AND subtable.plant_ID = table.plant_ID
     AND subtable.ID < table.ID) as inc_red
FROM table;

-
Bob Quintal

PA is y I've altered my email address.

Reply to this Message

John Mishefske - 26 Feb 2006 06:08 GMT

> You could use a subquery to return the preceding record, based
> on matching plant_ID, Control_ID, and smaller ID or entrydate.
[quoted text clipped - 6 lines]
> AND subtable.ID < table.ID) as inc_red
> FROM table;

Unless I misunderstood the post I don't think this is going to
work for a couple of reasons. The Last() function doesn't give
you the last record and without an ORDER BY clause the order
returned by the database is undefined and cannot be depending on.

Instead a solution is to look for the previous value with a
SELECT TOP 1 query (which is not portable to other DBMS systems -
it is a JET only feature).

SELECT * from table As t,
table.Tons_reduced - (SELECT TOP 1 tons_reduced
     FROM table ALIAS Subtable
     WHERE subtable.Control_ID = table.control_id
     AND subtable.plant_ID = table.plant_ID
     AND subtable.ID < t.ID
ORDER BY subtable.ID Desc) as inc_red
FROM table;

The Tons_reduced field could perhaps be substituted in the
subquery's "AND subtable.ID < t.ID" instead of ID.

Signature

'---------------
'John Mishefske
'---------------

Reply to this Message

Lyle Fairfield - 26 Feb 2006 14:07 GMT

> Instead a solution is to look for the previous value with a
> SELECT TOP 1 query (which is not portable to other DBMS systems -
> it is a JET only feature).

>From Ms-SQL Books on Line:

"You can use the TOP clause to limit the number of rows that are
returned in the result set.

TOP ( expression ) [ PERCENT ] [ WITH TIES ]

expression is a numeric expression that specifies the number of rows to
be returned; or if PERCENT is specified, the percentage (specified by
expression) of the result set rows is returned. For example:

TOP (120) /*Return the top 120 rows of the result set. */
TOP (15) PERCENT /* Return the top 15 percent of the result set. */.
TOP(@n) /* Return the top @n rows of the result set, with the variable
declaration: DECLARE @n AS BIGINT; SET @n = 2 */."

Reply to this Message

Bob Quintal - 26 Feb 2006 14:28 GMT

>> You could use a subquery to return the preceding record, based
>> on matching plant_ID, Control_ID, and smaller ID or entrydate.
[quoted text clipped - 27 lines]
> The Tons_reduced field could perhaps be substituted in the
> subquery's "AND subtable.ID < t.ID" instead of ID.

Interesting observation: you correctly picked up I had an error
in my code, but offered a different correction, then a third
variant on the theme..

I typed last() instead of the intended max()

Again we prove that there's more than one way...

Signature

Bob Quintal

PA is y I've altered my email address.

Reply to this Message