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

[PChitnis]

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!

[frofa]

I’ve updated my answer below.

[frofa]

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.

[PChitnis]

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.

[frofa]

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