Jump to content

Adding Date Interval


I-AM-OBODO
Go to solution Solved by Barand,

Recommended Posts

Hi all.

In my database, i have a column recurring which is derived from a multiple option

form field with values: Weekly, Bi-Monthly, Monthly, Quarterly, Half Yearly and

Yearly. I want to have as next due the current date plus the recurring value. eg

current date = 2014-11-24
recurring = monthly
next due = current date + recurring (in the next due will be 2014-12-24)

so i did:

$stmt = $pdo->query("SELECT recurring, due_date FROM $table");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $recur = $row['recurring'];
    $date_due = $row['due_date'];
}
$weekly = "Weekly";
$bi_monthly = "Bi-Monthly";
$monthly = "Monthly";
$quarterly = "Quarterly";
$half_yearly = "Half Yearly";
$yearly = "Yearly";

if(strcmp($recur, $weekly) == 0){
    $recurs = "7 DAY";
}elseif(strcmp($recur, $bi_monthly) == 0){
    $recurs = "14 DAY";
}elseif(strcmp($recur, $monthly) == 0){
    $recurs = "1 MONTH";
}elseif(strcmp($recur, $quarterly) == 0){
    $recurs = "3 MONTH";
}elseif(strcmp($recur, $half_yearly) == 0){
    $recurs = "6 MONTH";
}elseif(strcmp($recur, $yearly) == 0){
    $recurs = "1 YEAR";
}

$stmt = $pdo->query("SELECT ADDDATE('$date_due', INTERVAL $recurs) as nex_due FROM

$table");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$nex_due = $row['nex_due'];


$stmt = $pdo->prepare("SELECT * FROM $table ORDER BY trans_id DESC");
$stmt->execute();
$num_rows = $stmt->rowCount();
    
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Print out the contents of each row into a table
echo "<tr><td>";
echo $row['trans_ref'];
echo "</td><td>";
echo $row['acct_num'];
echo "</td><td>";
echo ucwords($row['payee']);
echo "</td><td>";
echo ucwords($row['company']);
echo "</td><td>";
echo $row['acct_no'];
echo "</td><td>";
echo number_format($row['amt'],2);
echo "</td><td>";
echo $row['purpose'];
echo "</td><td>";
echo $row['recurring'];
echo "</td><td>";
echo $row['due_date'];
echo "</td><td>";
echo $nex_due;
echo "</td><td>";
echo "<strong>".$row['status']."</strong>";
echo "</td><td>";
echo "<strong>".$row['pay_status']."</strong>";
echo "</td><td>";
}

The problem is that it's giving me as next due the value of the first row even

when the recurring is different!
Link to comment
Share on other sites

You could do this in the query with a CASE statement.

 

SELECT recurring, due_date, 
    CASE recurring
       WHEN 'weekly' THEN due_date + INTERVAL 7 DAY
       WHEN 'bi-weekly' THEN due_date + INTERVAL 14 DAY
       WHEN 'monthly' THEN due_date + INTERVAL 1 MONTH
       WHEN 'quarterly' THEN due_date + INTERVAL 3 MONTH
       WHEN 'half-yearly' THEN due_date + INTERVAL 6 MONTH
       WHEN 'yearly' THEN due_date + INTERVAL 1 YEAR
   END as next_due
FROM table
Note that you'll have to determine in what manner you want to handle dates that may not be valid for all intervals. For example if the due day were Feb. 29th and the interval was yearly, or Jan 31st and the interval was monthly. You can check mysql's default behavior in those instances and see if that is acceptable or if you want to do something different.
  • Like 1
Link to comment
Share on other sites

You could do this in the query with a CASE statement.

 

SELECT recurring, due_date, 
    CASE recurring
       WHEN 'weekly' THEN due_date + INTERVAL 7 DAY
       WHEN 'bi-weekly' THEN due_date + INTERVAL 14 DAY
       WHEN 'monthly' THEN due_date + INTERVAL 1 MONTH
       WHEN 'quarterly' THEN due_date + INTERVAL 3 MONTH
       WHEN 'half-yearly' THEN due_date + INTERVAL 6 MONTH
       WHEN 'yearly' THEN due_date + INTERVAL 1 YEAR
   END as next_due
FROM table
Note that you'll have to determine in what manner you want to handle dates that may not be valid for all intervals. For example if the due day were Feb. 29th and the interval was yearly, or Jan 31st and the interval was monthly. You can check mysql's default behavior in those instances and see if that is acceptable or if you want to do something different.

 

thanks but what you did is a better and simpler way of doing what i did but it still did not solve my problem. The problem is that it calculates the next due for a single row and gives the result to all the rows in the table even when they are different dates.

I need something that will give me the values of different rows/dates eg

2014-12-25 ------ 2015-01-25 (Monthly)

2014-12-10 ------ 2014-12-24 (Bi-Weekly)

 

thanks

Link to comment
Share on other sites

 

 

The problem is that it calculates the next due for a single row and gives the result to all the rows in the table even when they are different dates.

 

Then you are processing the results incorrectly

mysql> SELECT recurring, due_date,
    ->     CASE recurring
    ->        WHEN 'weekly' THEN due_date + INTERVAL 7 DAY
    ->        WHEN 'bi-weekly' THEN due_date + INTERVAL 14 DAY
    ->        WHEN 'monthly' THEN due_date + INTERVAL 1 MONTH
    ->        WHEN 'quarterly' THEN due_date + INTERVAL 3 MONTH
    ->        WHEN 'half-yearly' THEN due_date + INTERVAL 6 MONTH
    ->        WHEN 'yearly' THEN due_date + INTERVAL 1 YEAR
    ->    END as next_due
    -> FROM tblchidi;
+-----------+------------+------------+
| recurring | due_date   | next_due   |
+-----------+------------+------------+
| monthly   | 2014-12-25 | 2015-01-25 |
| Bi-Weekly | 2014-12-10 | 2014-12-24 |
| yearly    | 2014-12-20 | 2015-12-20 |
| quarterly | 2014-12-24 | 2015-03-24 |
+-----------+------------+------------+
Link to comment
Share on other sites

I noticed that the problem is with my code but i can't figure out where the problem is from or if something is wrong with my code.
Maybe there's a better way of re-writting my code.

Thanks, here's my code:

$table_name = "Chidi";
$stmt2 = $pdo->query("SELECT recurring, due_date, CASE recurring WHEN 'weekly' THEN due_date + INTERVAL 7 DAY WHEN 'bi-weekly' THEN due_date + INTERVAL 14 DAY WHEN 'monthly' THEN due_date + INTERVAL 1 MONTH WHEN 'quarterly' THEN due_date + INTERVAL 3 MONTH WHEN 'half-yearly' THEN due_date + INTERVAL 6 MONTH WHEN 'yearly' THEN due_date + INTERVAL 1 YEAR END as next_due FROM $table_name");
while($row2 = $stmt2->fetch(PDO::FETCH_ASSOC)){
$next_due = $row2['next_due'];
}

$stmt = $pdo->prepare("SELECT * FROM $tbl_name ORDER BY trans_id DESC LIMIT $start, $limit");
$stmt->execute();


echo "<table width='100%' border='1' bordercolor='#0cc' cellspacing='0' cellpadding='2'>";
echo "<tr>
<th bgcolor='#444444' align='center'><font color='#fff'>Trans Ref</th>
<th bgcolor='#444444' align='center'><font color='#fff'>Subscriber's A/C No</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Subscriber's Name</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Services Provider</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Provider's A/C No</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Amount</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Service</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Recurring</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Due Date</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Next Due Date</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Status</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Payment Status</font></th>

</tr>";
// keeps getting the next row until there are no more to get
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['trans_ref'];
echo "</td><td>";
echo $row['acct_num'];
echo "</td><td>";
echo ucwords($row['payee']);
echo "</td><td>";
echo ucwords($row['company']);
echo "</td><td>";
echo $row['acct_no'];
echo "</td><td>";
echo number_format($row['amt'],2);
echo "</td><td>";
echo $row['purpose'];
echo "</td><td>";
echo $row['recurring'];
echo "</td><td>";
echo $row['due_date'];
echo "</td><td>";
echo $next_due;
echo "</td><td>";
echo "<strong>".$row['status']."</strong>";
echo "</td><td>";
echo "<strong>".$row['pay_status']."</strong>";
}
echo "</table>";
Link to comment
Share on other sites

I really don't know whether to laugh or cry when I read your code. :facewall:

 

You are supposed to incorporate the CASE statement that Kicken gave you into your query so you calculate the next_due for every row.

Oops! I'd rather you laugh than cry. lolz. I did that on the top of my query if you checked. Maybe I'd do it along with other query? was thinking it wouldnt work. Will give it a shot. But a point to the right direction wouldnt do any harm! :)

Thanks

Link to comment
Share on other sites

  • Solution

Does this help?

SELECT trans_ref
, acct_num
, payee
, company
, acct_no
, amt
, purpose
, recurring
, due_date
, CASE recurring
    WHEN 'weekly' THEN due_date + INTERVAL 7 DAY
    WHEN 'bi-weekly' THEN due_date + INTERVAL 14 DAY
    WHEN 'monthly' THEN due_date + INTERVAL 1 MONTH
    WHEN 'quarterly' THEN due_date + INTERVAL 3 MONTH
    WHEN 'half-yearly' THEN due_date + INTERVAL 6 MONTH
    WHEN 'yearly' THEN due_date + INTERVAL 1 YEAR
    END as next_due
, status
, pay_status
FROM $tbl_name 
ORDER BY trans_id DESC 
LIMIT $start, $limit

And throw away that useless first query.

Edited by Barand
Link to comment
Share on other sites

You could do this in the query with a CASE statement.

 

SELECT recurring, due_date, 
    CASE recurring
       WHEN 'weekly' THEN due_date + INTERVAL 7 DAY
       WHEN 'bi-weekly' THEN due_date + INTERVAL 14 DAY
       WHEN 'monthly' THEN due_date + INTERVAL 1 MONTH
       WHEN 'quarterly' THEN due_date + INTERVAL 3 MONTH
       WHEN 'half-yearly' THEN due_date + INTERVAL 6 MONTH
       WHEN 'yearly' THEN due_date + INTERVAL 1 YEAR
   END as next_due
FROM table
Note that you'll have to determine in what manner you want to handle dates that may not be valid for all intervals. For example if the due day were Feb. 29th and the interval was yearly, or Jan 31st and the interval was monthly. You can check mysql's default behavior in those instances and see if that is acceptable or if you want to do something different.

 

But how can the invalid dates for intervals be handled, like the case you sighted with feb 29th?

Link to comment
Share on other sites

But how can the invalid dates for intervals be handled, like the case you sighted with feb 29th?

That's up to you. First you have to decide what you want to happen. After you decide that, you write the appropriate logic to make that happen.

 

For example Feb 29th + 1 Year. Should it roll back a day to Feb 28th, or forward a day to Mar 1st. Mysql's default behavior if you overflow a month is to roll it back to the last day of the month. So Feb 29th + 1 year would be Feb 28th. Jan 31st + 1 Month would be Feb 28th.

 

If that policy is fine for you, then you don't need to do anything special for the calculation.

 

The other thing to consider is if these are intended to recur several times, how are you going to handle that? For example, if you just replace the original due date with the new calculated date, you'll no longer be accurate for these end dates. Jan 31st + 1 month would end up making the new date Feb 28th. After that all your subsequent calculations would be based on the 28th.

 

Again, if that policy is fine for you, there's nothing special that needs done. Otherwise you'll need to come up with a strategy to handle those cased and implement it.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.