1

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

The 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

The memberStatus column should be computed from the expirationDate and 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 SELECT, SUM() and 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,

-1

All that you have to do is to add the ExpirationPeriod to the Year of payment and add 12.31 as the date of expiration.

If someone pay 2014.05.07 for 3 years then his membership expired at 2014+3.12.31

| improve this answer | |
  • Yes. At first. If that same person pay another 3 years in 2016, how can I sun those 3 years to the 2017 expiration date from the previous payment? – phollox Sep 1 '18 at 11:31
  • @phollox Damn good question! It's not a problem to do all that within the stored routine but it's a real conundrum to fit all that logic into a single query. I've failed three attempts already but still hope it's possible. – Kondybas Sep 1 '18 at 16:28
  • I don't need a single query. It could be several. I'm sure it can be done with a macro. But I'm a newbie in SQL, and I'd prefer to avoid macros for the moment. I can easily compute a single line per year per user, with the sum of the total expirationPeriod purchased that year. But I still can't write something that consider the purchase of membership when the previous payment is still active. I know how to do it in Matlab, C, Python, etc. But not in SQL. I'd normally use either a temporary variable within a loop, or being able to check data from previous rows. – phollox Sep 1 '18 at 18:55
  • @phollox The same with SQL. You can create the empty copy of the membershipPayment table with UNIQUE constraint on the memberID. Then you have to INSERT..SELECT.. ON DUPLICATE KEY UPDATE all the data ordered by memberID, paymentDate ASC. ODKU section implements the run-length cumulation. – Kondybas Sep 2 '18 at 6:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.