How to compute a membership expiration date on SQL

datehsqldbquery

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,

Best Answer

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

Related Solutions

Mysql – select Data between two date range thesql return wrong values

Tip: Test uses of functions:

Your first use of str_to_date:

mysql> SELECT STR_TO_DATE('2018-11-9','%d %M %Y');  SHOW WARNINGS;
+-------------------------------------+
| STR_TO_DATE('2018-11-9','%d %M %Y') |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2018-11-9' for function str_to_date |
+---------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Aha -- It's year-month-day, not day-month-year:

mysql> SELECT STR_TO_DATE('2018-11-9','%Y %M %d');  SHOW WARNINGS;
+-------------------------------------+
| STR_TO_DATE('2018-11-9','%Y %M %d') |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2018-11-9' for function str_to_date |
+---------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Wait, m, not M:

mysql> SELECT STR_TO_DATE('2018-11-9','%Y %m %d');  SHOW WARNINGS;
+-------------------------------------+
| STR_TO_DATE('2018-11-9','%Y %m %d') |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2018-11-9' for function str_to_date |
+---------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Oh, yeah, the dashes need to be specified:

mysql> SELECT STR_TO_DATE('2018-11-9','%Y-%m-%d');
+-------------------------------------+
| STR_TO_DATE('2018-11-9','%Y-%m-%d') |
+-------------------------------------+
| 2018-11-09                          |
+-------------------------------------+
1 row in set (0.00 sec)

But, anyway, you can simply use the string '2018-11-9', no conversion needed!

Or, fix the string a different way (now needing slashes, but M and order are correct:

mysql> SELECT STR_TO_DATE('9/Nov/2018','%d/%M/%Y');
+--------------------------------------+
| STR_TO_DATE('9/Nov/2018','%d/%M/%Y') |
+--------------------------------------+
| 2018-11-09                           |
+--------------------------------------+
1 row in set (0.00 sec)

Sql-server – Date variable to filter result for all previous years

There are many ways to get the beginning of this year, I think the most intuitive is DATEFROMPARTS():

DECLARE @BeginningOfThisYear date = DATEFROMPARTS(YEAR(SYSUTCDATETIME()),1,1);

SELECT ... FROM dbo.Customers AS c
WHERE NOT EXISTS 
(
  SELECT 1 FROM dbo.Orders 
    WHERE CustomerID = c.CustomerID
    AND OrderDate >= @BeginningOfThisYear
);

Less intuitive ways include:

DECLARE @BeginningOfThisYear date = DATEADD(DAY, 
  1-DATEPART(DAYOFYEAR, SYSUTCDATETIME()), 
  SYSUTCDATETIME()
);

And downright terrible ways include:

DECLARE @BeginningOfThisYear date = CONVERT(date,
  CONCAT(YEAR(SYSUTCDATETIME()),'0101'));

Best Answer

Related Solutions

Mysql – select Data between two date range thesql return wrong values

Sql-server – Date variable to filter result for all previous years

Related Question