Ask Your Question

How to obtain the difference (subtract) decimal values in a field in two records in a table and enter this into another table? [closed]

asked 2014-03-11 09:45:09 +0200

PChitnis gravatar image

updated 2015-09-11 10:11:27 +0200

Alex Kemp gravatar image

Working in Librebase, Windows 7, 32 bit. We record KWH meter reading for a number of meters at monthly intervals. We need to obtain the difference between two subsequent readings for each meter and record the figure in another table. The table records meter identity (int), reading date, record identity (primary key). Short of re-entering previous/last reading also, how can we do this in librebase? Something like Dlookup() function in Access?

Further clarification: I record serially readings from (say) 50 meters each month. Each meter is uniquely identified (through a separate table, foreign key) and each reading has a unique id (primary key), of course the date is recorded too. After entering the data for a month, I need to know (and record in a separate table) each meters consumption. I can obviously sort or filter data by meter identity plus order of reads (and the dates), but there will be gaps (and not necessarily of a fixed number).

Years ago (mid nineties) I worked with dBase 4 & 5 where the data was read into memory variables, skip 1 or skip -1 used to move between rows and a small function used to return the sum or difference which could be recorded in another table if required. Life was simpler!

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 07:02:00.725327


I've updated my answer below.

frofa gravatar imagefrofa ( 2014-03-12 00:09:44 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2014-03-11 11:00:20 +0200

frofa gravatar image

updated 2014-03-12 00:35:56 +0200

You should be able to compute your differences using a self-join done in pure SQL. The idea is you join your meter-reading table to itself, but offset the second version ID value by one. Then you use the joined structure to calculate the difference between the 2 meter readings.

NOTE: If there are 'gaps' in your ID values, you must modify the technique by creating a second on-the-fly ID without gaps (say, by using the Rownum function). The technique is illustrated HERE.

You do not necessarily need to write the results to another table. Also see THIS thread. The details of how you do it depends on the ordering of your primary table.

Further thoughts: the existence of gaps is the reason why the self-join requires a re-numbering of the sorted rows. However, a complication in your case is that all the readings for each individual meter must be treated as a separate set of data for the purposes of calculating the differences, so using a simple self-join will not work (as in the examples given above). Here is another Q&A that seems similar to your problem, and might give you some ideas; it uses pure SQL without recourse to the ROWNUM function and seems to use nested SQL to compute the NextDate. In your case, an analogous method would be used to compute SecondReading.

edit flag offensive delete link more


Thanks. This does show the way to retrieve values from other rows. Will probably (certainly?) require designing functions and passing parameters to be able to record the calculated values in another table. Since I am still designing the tables and linkages, I may be able to find a good(?) solution.

PChitnis gravatar imagePChitnis ( 2014-03-13 06:56:23 +0200 )edit

PChitnis: Do post your working solution down the track (which might help others).

frofa gravatar imagefrofa ( 2014-03-13 11:00:42 +0200 )edit

Question Tools

1 follower


Asked: 2014-03-11 09:45:09 +0200

Seen: 804 times

Last updated: Mar 12 '14