This is a follow up to this previously posted question. I'm using LibreOffice Base to work with some membership data. Further expansion might be possible in the future, but for now, the embedded database is fine, and it provides portability (I could send the file to someone and they can see the data, without haing to connect to an SQL server). The downside: LibreOffice Base uses HSQLDB version 1.8 (from circa 2008). Hence, the available functions are limited.
I have several tables and forms and queries to add and retrieve data from my database, including payments. Among the payments, my culprit are the membership renewal. There are several types of membership, each with different prices, and in some cases, different expiration periods. Members can purchase membership for 1 year or 3 years, and they could do it at any given point in time. Each membership expire on December 31st of the calendar year.
I have a table (actually a View produced by a filtering Query) called
"membershipPayment" where I have
memberID paymentID paymentDate expirePeriod 0 0 2012-02-02 1 0 10 2013-02-15 3 0 30 2014-01-10 1 0 60 2015-08-15 3 1 5 2012-03-12 1 1 15 2013-04-01 1 1 20 2013-10-01 3 1 35 2014-02-01 1
expirePeriod is the number of years of membership associated with that payment. Notice that in the example, in some cases members are renewing membership when their membership is still active due to a previous payment. I need to query this table/view to summarize these payments and have an expiration date and membership status, with one line per member
memberID expirationDate memberStatus 0 2019-12-31 active 1 2017-12-31 inactive
memberStatus column should be computed from the
CURRENT_DATE. No problems here. My issue is how to compute the
expirationDate. I would like to use only a Query, if possible. I don't know how to loop (or if it's even possible to do such thing) along the rows of the
"membershipPayment" table to increment the
expirationDate based on the value of
expirePeriod, or to keep track of a value from a previous row while checking another. I managed to compute the total number of membership purchased on a sigle year using
GROUP BY, according to the answer of the previously cited post. But keeping track of the
expirationDate, still no clue.
If macros are required, any help or advice is appreciated.
Thanks for any advice or comment, even if it's to point that my question is trivial,