Calculating a Subscription's Expiration Date

Discussion created by bright_guy on Aug 29, 2014
Latest reply on Sep 2, 2014 by bright_guy

This is a good one I think. Any help is much appreciated.



Report the status of a user's subscription.


Background / Use Case

A mobile file is free on FM Go. Paid subscriptions are offered on the hosted file. The hosted file allows the user to sync the mobile file and share data with other users. Three types of subscriptions are offered: one month, six months, or twelve months. Users can purchase any type of subscription at any time.


Sample Data

The hosted file includes a [User] table and a [UserLog] table. The latter records user "actions" and corresponding time stamp. For example:


timestamp | action | monthsPurchased | c_expirationDate

2012-08-28 17:33:46 | Paid one month. | 1 | 2012-09-28

2012-08-28 17:53:22 | Paid six months. | 6 | 2013-02-28

2013-08-28 23:31:36 | Paid six months. | 6 | 2014-02-28

2013-08-28 23:34:14 | Paid twelve months. | 12 | 2014-08-28


Commentary on Sample Data

For the sake of defining some terms, and also for the sake of using plain language...

Line 1 - the user "subscribes"

Line 2 - the user "renews" subscription

Line 3 - the user "resubscribes"

Line 4 - the user "renews" subscription



Notice the calculated result in line 2? The expiration date is wrong because it fails to take into account the user's prior purchase. The correct result should be 2013-03-28.


Line 3 is correct because the user's subscription lapsed before she re-subscribed.


Line 4 is also wrong for the same reason as line 2. The correct result for the expiration date should be 2015-02-28


The existing calculation incorrectly adds the [monthsPurchased] to the [timeStamp] without accounting for prior [monthsPurchased]. Renewals are consistently wrong.



Am I overthinking this? I mean, it's not exactly a unique use case. Maybe I don't need to bother with expiration dates in the [UserLog]. Maybe the calculation should reside in the parent [User] table. I'll need one there anyway to report the user status: Are they paid up or not?


I just think it's cleaner to see a -- correct -- expiration date in the log.



Thanks in advance for your help,