Adding Date Interval

[I-AM-OBODO]

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!

[kicken]

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.

[I-AM-OBODO]

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

[Barand]

 

 

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 |
+-----------+------------+------------+

[I-AM-OBODO]

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>";

[Barand]

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

 

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

[I-AM-OBODO]

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

 

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

[Barand]

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 December 24, 2014 by Barand

[I-AM-OBODO]

Thanks kicken and berand you both got my best answer both unfortunately only one has to be chosen. thanks a thrillion dozen time. some day i know i'll give back to this wonderful forum. cheers

[I-AM-OBODO]

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?

[kicken]

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.

[Barand]

Or you could sidestep the problems and not allow any dates after the 28th of any month

 

edit : If the recurring value is monthly or higher

Edited December 25, 2014 by Barand

[I-AM-OBODO]

Hmmmm. Thanks