我的工作需要处理即将到期的航空里程的应用程序。的英里可以使用，但如果他们不使用特定的日期，他们将过期。我想确保我过期的里程适量，但不到期他们是否已被使用。所以，我要检查是否有足够的里程已在指定日期范围内被使用在过期之前。下面是我最初的想法：

I am working on an application that needs to handle expiring airline miles. The miles can be used, but if they are not used by a certain date, they will expire. I want to make sure I expire the proper amount of miles but don't expire them if they have been used. Therefore, I have to check if enough miles have been used in a given date range before they expire. Here are my initial thoughts:

有以下列里的表：

USER_ID的 - 外键，用户表的 number_miles的 - 英里这一创纪录的数字的 miles_type的 - 指示是否英里的加入，使用或过期的 miles_expire的 - 布尔值或指示，如果这些里程到期不的 miles_date的 - 这个记录被添加日期的 exipration_date的 - 这些里程到期（如 miles_expire 标记为true）的日期的 在处理的 - 表示该记录已被处理 - 仅适用于当 miles_type 设置为过期的 user_id - Foreign key to the users table number_miles - The number of miles for this record miles_type - Indicates if miles were added, used, or expired miles_expire - Boolean value to indicate if these miles expire or not miles_date - The date this record was added exipration_date - The date these miles expire (if the miles_expire flag is true) processed - Indicates if this record has been processed - only applicable when the miles_type is set to expire

如果我有一个记录每次里程的增加，使用，或从一个特定的用户账号过期，我想我可以计算出到期英里这样：

If I have a record every time miles are added, used, or expired from a particular user account, I figure I can calculate the expired miles as such:

对于每个用户，获得其中 miles_expire 标志设置为true记录的日期时，到期日期是当前日期，和处理之前标志设置为false。这将得到记录，应该是过期的所有英里。 获取第一个和最后到期日期从previous查询。 从previous步，利用第一和最后一个到期日期值，得到该日期范围内的所有二手英里的列表。 如果从previous步骤所使用的英里的总和小于所述止点的总和，到期的差。如果所使用的里程数等于或大于，没有什么必须要过期了。 将处理标志设置为true的第一个和最后一个到期日期范围内的所有记录。无论这些里程已过期或忽略，因为它们已被使用。 For each user, get the dates of the records where the miles_expire flag set to true, the expiration_date is before the current date, and the processed flag is set to false. This will get the records for all miles that should be expired. Get the first and last expiration_date from the previous query. Using the first and last expiration_date values from the previous step, get a list of all used miles within that date range. If the sum of the used miles from the previous step is less than the sum of the expiring points, expire the difference. If the used miles are equal or greater, nothing has to be expired. Set the processed flag to true for all records within the first and last expiration_date range. Either these miles have been expired or ignored as they have been used.

看来这符合下列要求：

在到期里程是第一次使用 在它只会检查所使用的里程，同时帧作为二手英里因此使用许多英里过去不会拯救未来英里过期 在数只到期日期后并不会，如果用户使用了足够的里程

是否有其他方面的考虑，我需要考虑？将这项工作正常到期哩？

Are there other considerations I need to take into account? Will this work to properly expire miles?

推荐答案

您的方法不会有几个原因的工作。最简单的解释大概是这样的：

Your method won't work for several reasons. The easiest to explain is probably this:

有关的每个用户，获得其中miles_expire标志设置为真记录的日期，到期日期的是当前的日期之前，和经处理的标志设置为假。这将得到记录，应该是过期的所有英里。

For each user, get the dates of the records where the miles_expire flag set to true, the expiration_date is before the current date, and the processed flag is set to false. This will get the records for all miles that should be expired.

请注意，这可能是唯一的一个记录。如果你运行你的支票，很多时候，它通常只有一个事实上的纪录。让我们假设为简单起见的最大一个记录过期每一天（如果有多个到期，你的算法仍然失败，它只是难以解释）。

Note that this could be only one record. If you run your check very often, it will typically be only one record in fact. Let's assume for simplicity that max one record expires each day (if more than one expires, your algorithm still fails, it's just harder to explain).

获取从previous查询中的第一个和最后一个到期日期。

Get the first and last expiration_date from the previous query.

您会得到相同的日期在这里，因为这里只有一个记录。

You'll get the same date here, because there's only one record.

使用从previous步骤中的第一和最后一个到期日期值，得到该日期范围内的所有二手英里的列表。

Using the first and last expiration_date values from the previous step, get a list of all used miles within that date range.

自从第一个和最后是相等的，没有范围，因此永远不会有使用英里在这个范围内。

Since first and last are equal, there is no range and so there will never be used miles in that range.

如果从previous步骤所使用的英里的总和小于所述止点的总和，到期的差。如果所使用的里程数等于或大于，没有什么必须要过期了。

If the sum of the used miles from the previous step is less than the sum of the expiring points, expire the difference. If the used miles are equal or greater, nothing has to be expired.

由于previous一步返回行，总和为零，所以你将永远过期过期英里和0的区别。即使英里已被使用。

Since the previous step returned no rows, the sum is zero, so you will always expire the difference between 'expired miles' and '0'. Even if the miles have been used.

这个过程将重复。

整体设计似乎不必要compilcated。我只想储存未使用的凭证为每个用户的列表。当他们买单，使用优惠券被标记为使用。如果凭证是一半时，它被标记为已使用和新的凭证与同有效期限发出，但以较小的量。如果有更多的优惠券比需要，即将过期的优惠券用完第一，和非到期礼券最后使用。已过期的优惠券不能用于支付，所以你不需要做任何事情来到期它们。

Overall your design seems unnecessarily compilcated. I would just store a list of unused vouchers for each user. When they pay, vouchers used are marked as used. If a voucher is half used, it is marked as used and a new voucher is issued with the same expiration date, but with a smaller amount. If there are more vouchers than needed, soon to expire vouchers are used up first, and non-expiring vouchers are used last. Vouchers that have expired can never be used for payment, so you don't need to do anything to expire them.

因此​​，在总结：你不需要到期任何凭证，只要确保支付code不允许过期或使用优惠券的使用

So in summary: you don't need to expire any vouchers, just make sure the payment code does not allow usage of expired or used vouchers.