I know this might not be the best place to ask MySql-related questions, but nevertheless hope someone could at least point me to the right direction.
I did a lot of googling around, but still ended up here.
The problem I have is this. I have a database set up in MySql that holds membership data for a small organization. One of the tables holds data about the members, like names, date joined and date left. In another table I keep track of decisions made about the monthly membership fees.
The challenge comes when I try to calculate the fees due for each member. Had it only been one membership fee valid after date X, I would not have had any trouble. However, if, say, membership fee has changed over time, I can’t figure out a way to calculate the amounts due so that each member’s contribution is calculated depending on the date he or she joined, and the fee amount as it is changing over time.
Is anyone aware of a guide in MySql that specifically discusses such issues?
I appreciate any feedback, and thank you in advance.
This should be fairly simple if you have dates when fees are implemented
in your table of monthly membership fees. You could just compare
date_joined (user table) with date_implemented (fee table)… find the
smallest value of date_implemented that is after date_joined.
SELECT fees.amount FROM fees WHERE fees.date_implemented = (SELECT
MIN(fees.date_implemented) FROM fees WHERE fees.date_implemented >
users.date_joined) AND users.name=‘userNameHere’;
I think that will do it, though I’m not sure. Replace ‘userNameHere’
appropriately (along with table/column names.
Good luck.
hermesrules wrote:
> I know this might not be the best place to ask MySql-related questions,
> but nevertheless hope someone could at least point me to the right
> direction.
>
> I did a lot of googling around, but still ended up here.
>
> The problem I have is this. I have a database set up in MySql that
> holds membership data for a small organization. One of the tables holds
> data about the members, like names, date joined and date left. In
> another table I keep track of decisions made about the monthly
> membership fees.
>
> The challenge comes when I try to calculate the fees due for each
> member. Had it only been one membership fee valid after date X, I would
> not have had any trouble. However, if, say, membership fee has changed
> over time, I can’t figure out a way to calculate the amounts due so that
> each member’s contribution is calculated depending on the date he or she
> joined, and the fee amount as it is changing over time.
>
> Is anyone aware of a guide in MySql that specifically discusses such
> issues?
>
> I appreciate any feedback, and thank you in advance.
>
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
IF SO, then add the CASE statement to your select statement. In the following, ‘join_date’ is the field in your database that contains the date the member joined. And date ‘x’ is assumed to be 01/01/1990 and date ‘y’ is 26/07/1996. This example will return 10 or 20 or 30 respectively.
CASE join_date
WHEN < “19900101” THEN SELECT 10
WHEN between “19900101” and “19960726” THEN SELECT 20
ELSE SELECT 30
END CASE