Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
05-03-2013, 01:24 PM #1
- Join Date
- Apr 2013
- Thanked 0 Times in 0 Posts
dynamically create recurring dates
Hey guys...I honestly don't know where to start.
1. I need to do is select the "startingdate"column entry for a particular user then run a code that will generate and output that date every months a due date.
2. Inset payments into the generated report from the payments table for specific user.
3. Calculate the difference between the entries that are either due date to due date or between the payments and due date.
05-03-2013, 07:16 PM #2
There are several ways to do this.
One way is to just calculate the starting date and subsequent due dates all in PHP (or JSP or ASP) code and then use code to check whether the next payment in line (coming from a DB table) is before, on, or after the due date. That's really not hard to do once you figure out the algorithm (logic) of it.
Another way is to create a separate table that has every possible date in it for the next few years. Dirt simple table:
CREATE TABLE allDates ( theDate date PRIMARY KEY );
Then, to pick up all the due dates you can write a query that joins that table to your payments table. Say the payment is due on the 13th of the month. So you just do (example onlyl)
SELECT theDate FROM allDates WHERE DAY(theDate) = 13 AND theDate BETWEEN '2013-1-13' AND '2016-12-13' ORDER BY theDateAn optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Users who have thanked Old Pedant for this post: