Apache OpenOffice Community Forum

Hi,
I seem to remember a post on the subject, but can't find it.
My Problem: I want to use the result of a calculation in the first row input for the calculation of the second row.
I found "Lag" and "Previous" and others in other Dbase application, but none in HSQLDB.
Can someone recommend a way around it with "Join" or save the result of the first row and the put it int the second row etc..
My dbase is quite small so a complicated way around is acceptable. Thank you Let

You might be able to solve your problem with a join if the rows contain some information from which their order can be deduced. It would help to know more about the structure of your table.
I am not sure whether you want the rows of a query to depend on previous rows in the query or the contents of a table to depend on previous rows. If it is a table, this is probably not a good idea. Table rows should contain independent pieces of information.

det wrote:I found "Lag" and "Previous" and others in other Dbase application, but none in HSQLDB.

HSQLDB is a relational database where rows are unordered. Read about the term "relation" in secion 3 of the linked article. There is no concept of a "first" row.

dBase, as I understand it, was not a relational database. Instead procedural commands and functions were used to open and traverse records and manipulate field values. Its vendor labelled dBase a "relational database" although it did not meet the criteria defined for the relational model. I believe you'll find that neither OpenOffice Base nor HSQLDB supports those procedural commands from dBase.

det wrote:My Problem: I want to use the result of a calculation in the first row input for the calculation of the second row.

This may be a fundamental problem for you with no good solution other than to redesign your database concept. Perhaps if you describe what you want to accomplish a volunteer will have some ideas for you.

1.I could store the Query Result in a Spreadsheet not in DBase Table.
2. I have quite some medical Bills each month. I pay Deductible and Copay up to a limit, the insurer pays the rest. Each time I pay the limits change.
PrevDedLim in Row 1 = DedLim in Row 0
PrevCoLim in Row1 = CoLim in Row 0

It appears your example is based on a spreadsheet mentality, which you need to leave behind when working with databases.
When using relational database you don't normally store a value that can be calculated in a query.

I pay Deductible and Copay up to a limit, the insurer pays the rest.

That implies that Deductible Limit and Copay Limit are actually constants. However, you also say

Each time I pay the limits change.

Which contradicts your first statement. I suspect what you mean is the difference between the amount paid and the limit changes.
I uploaded a database that demonstrates something similar to what I believe you are wanting.
It has one Table, one Query and 2 Forms.
One form is to enter new bills. The second form displays the results of the query where all the calculations are performed.
In the query I set a Deductible Limit at 200.00 and the Copay Limit at 100.00. The query is a little too complex for the table design GUI, so to view or make any changes Edit in SQL View must to used.

You are absolutely right, my spreadsheet mentality gets in the way.
And thanks for your excellent example. It directs me towards a solution.
Your Forms require entries for DeductionAmt and CopayAmt.
However, these are exactly what I want to establish by a query.
I'll try to edit your SQL and let you know what happens.
Det

Sorry, I could not get it to work. My solution is to do the calculation in as spreadsheet and add it to the database. The problem I have to do this for every block of new entries, very inefficient!

The better solution, of course, wait for an update of HSQLDB, that contains the "Lag" and "Previous" Functions.

The better solution, of course, wait for an update of HSQLDB, that contains the "Lag" and "Previous" Functions.

Those are not database functions, they are spreadsheet functions so your wait will likely be very long.

Your Forms require entries for DeductionAmt and CopayAmt.
However, these are exactly what I want to establish by a query.

Perhaps, we can help you if you can tell us how they are calculated.

det,
when you have a SQL issue then you must supply the necessary relevant data/information required to enable other forum users to respond with a possible solution.
it is possible to mimic the lag function used by some databases.
1) provide a few rows of TABLE data, field names, table names.
2) describe your primary key. is it an auto increment integer?
3) i assume you have a date field. is it unique?
4) tell us what calculation you wish to perform (fieldname1 operator or expression fieldname2 etc.)
5) using the sample TABLE data show us the expected query result.

HSQLDB is a relational database where rows are unordered. Read about the term "relation" in secion 3 of the linked article. There is no concept of a "first" row.

You are absolute right with this statement, but fortunate there is a way to query the data such that an ordering can be applied to the tuples / rows.

UnklDonald418 wrote:

The better solution, of course, wait for an update of HSQLDB, that contains the "Lag" and "Previous" Functions.

Those are not database functions, they are spreadsheet functions so your wait will likely be very long.

Your Forms require entries for DeductionAmt and CopayAmt.
However, these are exactly what I want to establish by a query.

Perhaps, we can help you if you can tell us how they are calculated.

I like to make a comment about Lag / Previous type of functions. These kind of functions do exists in modern databases (the commercial ones and ProgreSql) and are part of the Windowing-set of functions. Extreme powerful. All (?) other opensource based databases lack those unfortunate.

I added to the sample database a query that calculates the running totals per month/year.
The query "OverviewPerMonthYear" shows the same columns as you showed in your two line example.
The query MEDBILLS_PART_BY_M_Y is an help query that does the partition and contains the limits of Copay and Deduction, so you might need to change that.
When you need to fill it is not clear to me if there is a fixed ratio between the Bill and Copay and Deduction. If there is we can get it into the query and you are done. When not, you have to fill it in the form, but you like to list in the same form e.g. a subform the bills of that month, those will show your the remaining Copay and Deduction available and you and fill in the part you like to apply to your bill.
Have fun.

eremmel , thank you for your correction. Those functions didn't appear in my references (apparently outdated) so I wasn't aware of them.
I did encounter odd errors when trying to open Demo25_MedBills-v2.odb The first with OpenOffice 4.1.5, and the second with both LibreOffice 5.4.7.2 and 6.0.4.2

When I try to open my version is gives the same errors. Looks like corrupted... Will have to figure it out again this weekend.

Link below is a database just like user eremmel posted, BUT, it is an "Embedded HSQL Version 1.8.0.10" database, and, not from HSQL Version 2.4.0.
I did create two VIEWS from the Queries eremmel created, so, it could run through the Base Parser.

I will leave it to other Forum Users to continue any explanations / issues / solutions for user det

Sliderule

NOTE: Per the wise comment from user eremmel below, I updated the file type of the above attachment to .odb . Sorry for my mistake.

Thanks, Sliderule!

Just for others, rename the file of Sliderule by replacing '.zip' with '.odb'
I played in the past with split database and had point my class path to hsqldb.jar v2.x. So that was the cause...

Thank you for your efforts. Unfortunately they do no address my original post. Deduction and Copay is exactly what I want to find, but in your answers they are Table Entries.
The limit per months is 19.45 for Deductible (I pay) and 88.83 for CoPay (I Pay). The rest is
paid by the Gov. The Copay is 34.8 % of the Residual Bill.

General Bill Data (such as Date, Medication, Period, Amount/Period etc) are collected in Table “Bills” with a running ID for each Entry. The period is shown as 1802, meaning Feb. 2018.

In order to calculate my Deductible & CoPay, I had to create the Table “CalcTable”.
As requested these tables are attached (“CalcTable” only partially).
After Column “Bills” the Calculations are:
1)PrDedRes = Previous Deductible Residual = DedRes of previous Row or Manual Entry.
2)Ded = Deductible = Bill or PrDedRes, 3)DedRes = Deductible Residual = PrDedRed – Ded, 4)PrCoRes = Previous CoPay Residual = CoRes of previous Row or Manual Entry,
5)BilRes = Bill Residual = Bill – Deductible, 6)CoCalc = CoPay Calculated = BillRes x 0.348,
7)CO = CoPay = lesser of PrCoRes or CoCalc, 8)CoRes = PrCoRes – CO,
9)GS = Private Insurer = Bill – QuePaid, 10)QuePaid = Gov. Paid = Bill – Ded – Co.

Except for Column 1 and 4, Dbase can handle the Calculations. The Calculations may distract from the real problem, namely column 1 and 4. The “CalcTable” was created in Calc, would it be possible to create it in Dbase? I know my calc mentality gets in the way, but is there a better way? Det

As time allowed, I've continued looking at your problem but I'm not sure I totally understand your spreadsheet.
Using 19.45 as the beginning monthly deductible and a Copay rate of 34.8%, let's look at a simple example to confirm how your spreadsheet works.
For a given Month
Bill 1 – Bill Amt = 10.00 Since that is less than 19.45 Deductible Amt = 10.00. Copay = 0.00. Insurance = 0.00
Bill 2 – Bill Amt = 20.00 the remaining Deductible Amt = 9.45, Copay = 3.67 (34.8% of 10.55 ), Insurance = 6.88
Bill 3 – Bill Amt = 30.00 since this was already paid Deductible Amt = 0.00, Copay = 10.44 (34.8% of 30.00), Insurance = 19.56
Is this is correct?

I'm sorry my table is a bit confusing as it starts with the values of the previous year.
The Starting Values for Deductible and Copay for each month are in green. The attached revised Calc Sheet should make things clearer. Thank you. Det

In my sample database I made a query with running totals over month/year. I did that on fields Ded and Copay. But you can apply the same query pattern and calculate a running total on calculated Ded and calculated Copay and Bill. Based on those totals you can reconstruct the individual Ded and Copay per Bill (at least it is my intuition).
I had a short look at your explanation of your calculation, but looked to me too complicated because you stayed with to your calc-model.

As fas as I understand what you want to calculate (In my home country we have another system):
Bill : Amount is given.
Ded: The amount of the Bill you have to pay with max amount you have to pay per month applied 100% per bill
Copay: The percentage that is returned per bill for the amount above MaxDed with a limit per month and a max by obamacare(?).
Insurer: The remaining amount (Bill - Ded - Copay)
(Please correct this if this is wrong)

When you succeed in making a Calc sheet with these 4 columns and the running totals of those per month (use running totals up to the Bill you are processing not including) you can change that into a query and let the database to the work.

It is a nice challenge but it take some time (and I need to find time and to understand). The drawback of giving you a solution that you did not 'discovered' yourself is that you stay depended on this forum for each and every change.

Let me clarify: The column Bill should read Price. Maybe that led to the confusion.
My situation: The Statement from the pharmacy gives me the Price of the medication and the amount I have to pay. I want to check the if the amount I have to pay is correct. The rules for my payment have been stated earlier: up to 19.45/Month for Deductible and up to 69.35/Month for Copay.
My purpose for the Data Base is twofold:
1. Record the Purchase of the medication
2. Check the correctness of the amount I have to pay
Hopefully things are a lot clearer now. Det

You have the tab 'Bills' in your sheet CalcTableOO1.ods. Does this sheet map one to one on your database data model?
I observe the following:
- A medication can have up to two follow up subscriptions in different periods. Does all need to be considerd in the calculations?
- Some periods have value 0 what does that mean? I guess no refund allowed?

The attached sheet CalcTableOO2.ods contains the calculation based on running totals. I think is possible with SQL based on running totals per Prd (up to, not including the current Bill line). This are the steps taken:
- added to your sheet an new sheet in which I added all the bills in a normalized way (moved follow up subscriptions to the first Prd and Bill column.
- sorted the list based on Prd, ID
- Added running total on Bill (Sum Bill) based on Bill
- Added running total on Ded (Sum Ded) based on Bill, Sum Bill and Max Ded
- Added running total on Copay (normalized at 100% iso 34.8%) based on Sum Bill, Sum Ded, Max Copay.
- With the above three running totals and Bill we can calculate Ded, Copay and Insurence.

Please give feedback on your database table structure and confirm that the calculation is right.

I might then write the query that will give the same results as in the Calc sheet. ( if someone else want to give it a try, that is fine to me as well. It is a nice exercise for a SQL course.

I uploaded see if that is on the right track.

It has one table, MedBills2 with data copied from your spreadsheet as well as some other test data.
There are a series of queries, Query1_A through Query1_F that make use of the ability of one query to use another query as its data source. I tried to keep each query as simple as possible so that hopefully you will be able to understand all the steps needed to generate the data you need. Query1_FinalDet pulls it all together to display values for Bill Amt, Deductible, CoPay, and Insurance Share.

Dear Unkl,

Nice and clear solution. One remark though. In your SKEY calculation is I think that you should move one ')':
( YEAR( "Date" ) * 100 ) + MONTH( "Date" ) ) * 10000 + ID
It does not harm because the period is anyway YYYYMM, but when you order on SKEY result can look weird.

In fact you do not need SKEY and could have used ID due to the period is kept equal.

An other tip: Add to Query1_A the constants:
..., 19.45 AS "MAXDED", 69.38 AS "MAXCOPAY", 0.348 AS "PCTCOPAY"
So there is a central place for maintaining those constants and easy to change when the values change.

Even better is to populate e.g. a table with PERIOD, MAXDED, MAXCOPAY, PCTCOPAY so the calculation stays historical correct when new rates are issued.

eremmel thanks for the advice.
Originally, I used ID in the Running Totals (Differences?) but when I added 3 test records with dates from 2017 they appeared at the bottom of the list not the top. It also occurred to me that if you are entering new rows in the table, say for the month of July, and there in your stack of invoices is one you missed from perhaps 2/28/2018, without SKEY that entry also won't appear in the correct place when the Query1_FinalDet is run. Of course even with SKEY it won't appear in the correct place without an ORDER BY "SKEY" clause. Somehow that failed to make it into the upload.
I found another problem so I incorporated the recommendation to define the constants in Query1_A and fixed the known issues. Hopefully, no more surprises.
Just for fun I included Query2_FinalDet to see what it might look like with everything rolled up in a single query. That would be a challenge to decipher!

det,
i've uploaded an attachment with 2 queries.
q1.Setup: selects bill data & creates 2 running sums. created a view from this query which is the data source for the second query.
q2.Output: calculates & displays the field values in the image of your spread sheet.
i have built this database to reflect your spreadsheet.
the original IDs & periods are used. this restricts development but allows you to verify the output.
obviously in its current form input is difficult because the IDs are both manually input & filtered by Period.
if you confirm that it meets your needs then i may find the time to enhance its usability.
it also has the ability to use variable values for "DedLimit" & "CopayLimit".
a database is definitely the correct tool for this project. a spreadsheet will be corrupted sooner or later no matter how carefull the user.
i would also like to say that i found this project very interesting/unusual.

i did download the attachment uploaded by UnklDonald418.
the output is as it should be but because it does not contain the field "Period" it's impossible for me to easily verify the accuracy.
you would need to confirm the accuracy before it could be used with confidence.

To keep things simple and focused I left out some details. I have shown the Pharmacy Statement in
the attached CalcTableOO3 in the Normalized Sheet. If I buy more than the Doctor prescribed Pills, I can still have the excess pills covered by the next period. That is why there are 2 or sometimes 3 Bills on one Statement. All the answers are in this Statement. My purpose is to verify what I have to pay.
The Bill Table (DB) and the Calc Table are not linked or compatible, because I re-arranged the the Bill Table by Period requiring more rows.
The Normalized Sheet still uses results of the previous row, how can this be handled by a query?
I added two columns to help to get the right answer. I am still trying to find out if or how previous row results are used in Demo25_MedBills-v5.odb. The results do not match the official Values on the Statement of the Pharmacy. What is a running Total? Just the Sum in a query?
Hopefully misconceptions are removed. Det

Hello Uncl
I'm impressed! I started on Base about 2 months and didn't get much beyond simple SQL.
Thanks for taking the time do represent my calc table in a query. So there must be a way to store the result of a calculation and use it in new calculations of other queries. It will take me some time to understand the SQL and why not all results agree with the Pharmacy results listed in the Normalizing sheet of CalcTableOO3. Thanks again Det

det,
i'm sorry that my effort does not match your expectations.
in my first post i did ask you to show us what you have along with your expected results.
you uploaded a spreadsheet without explanatory notes which failed to shed a coherent insight into your exact issue/objective.
a second spreadsheet was then uploaded which revealed the basic mechanics behind the project to which i responded with my uploaded attachment.
it would save everyones time & effort if you could answer any questions asked by those of us who are trying to help after all it's you who would be the beneficiary.

In one of those middle of the night AHA! moments, it finally occurred to me how to get the output sorted into the proper order without the SKEY field. Sometimes it's hard to see the forest with all those trees blocking the view.
I made a couple of other tweaks that will hopefully help you get closer to your goal. Perhaps a little overview will also help you understand how this works.
Query1_A defines preliminary values that will be needed later on. If something changes like "MAXDED" or "PCTCOPAY", you only need to make one change here.
Query1_B is where a Running Total is calculated, which is the sum of the current Bill amount plus all previous Bill amounts. That result is subtracted from "MAXDED" (19.45) and assigned the name "RemDed".
Query1_C uses "RemDed" in a CASE WHEN statement to determine the actual deductible amount "DedAmt".
Query1_D calculates a preliminary CoPay amount "CCP"
Query1_E calculates "RTCP" which is a running total of the "CCP" field. In previous versions I included the value for the current record in that total, but in this version it only sums the values for the previous rows. I think that will resolve one issue.
Query1_F uses a CASE WHEN statement to determine the actual "CoPay" amount.
Query1_FinalDet does the final calculations for the values to be displayed on the form Display_FinalDet.

Dear chrisb,
I'm sorry you felt I did not value your contribution. I immediately sent tables, calculations for each
column and 3 versions of a spreadsheet with all explanations. Please let me know what you are missing and I gladly supply. Det
Right now I will study Demo25_MedBills-v6.odb to understand what running Totals are.