Monthly Cumulative Calculation from Salary deduction

PHP
1

I have a table with different body of deductions, like,

mmyy-------id-------event----------------amount
01-27-2014–211-----SSANU_WELFARE----20,000
01-27-2014–211-----SSANU_DUES----------1,000
01-27-2014–221-----SSANU_WELFARE------5,000
01-27-2014–221-----SSANU_DUES----------1,000
01-27-2014–231-----SSANU_WELFARE-----35,000
01-27-2014–231-----SSANU_DUES----------1,000
02-28-2014–211-----SSANU_WELFARE-----20,000
02-28-2014–211-----SSANU_DUES----------1,000
02-28-2014–221-----SSANU_WELFARE------5,000
02-28-2014–221-----SSANU_DUES----------1,000
02-28-2014–231-----SSANU_WELFARE-----35,000
02-28-2014–231-----SSANU_DUES----------1,000
03-29-2014–211-----SSANU_WELFARE-----10,000
03-29-2014–211-----SSANU_DUES----------1,000
03-29-2014–221-----SSANU_WELFARE------5,000
03-29-2014–221-----SSANU_DUES----------1,000
03-29-2014–221-----SSANU_WELFARE-----35,000
03-29-2014–221-----SSANU_DUES----------1,000

I want each “id’s” deduction cumulatives for example;

SSANU_WELFARE for
id 211—January—20,000
id 211—February–40,000
id 211—March-----50,000

SSANU_DUES for
id 211—January—1,000
id 211—February–2,000
id 211—March-----3,000

And so on for others.

The calculation should be based on php syntax.

Thank you

2

Date really should be stored YYYY-MM-DD

3

Thanks pls ignore the date I’ll correct that as noted, help on the script please.

Thank you.

4

Hmm, well here’s an example correcting date and making accumulative with query results.

$sql = "SELECT mmyy, id, event, amount FROM SSANU";
$query = $db->prepare($sql); 
$query->execute();

$data = array();
while($row = $query->fetch(PDO::FETCH_ASSOC)){
    $parts = list( $month,$day,$year) = explode('-',$row['mmyy']);
    $month = date("F",strtotime($year.'-'.$month.'-'.$day));
    
    if(!isset($data[$row['event']][$month][$row['id']]['sum'])){$data[$row['event']][$month][$row['id']]['sum'] = 0;}
    $data[$row['event']][$month][$row['id']]['sum'] += str_replace(',','',$row['amount']);    
}
?>
<html>
<body>
<?php
foreach($data as $event => $arr):
    echo $event . " for<br />\r";
    foreach($arr as $month => $arr2):
        foreach($arr2 as $id => $sum):
            echo 'id ' . $id . '---' . $month . '---' . number_format($sum['sum']) . "<br />\r";  
        endforeach;
    endforeach;
    echo "<br />\r";
endforeach;
?>

</body>
</html>

EXAMPLE RETURNED

SSANU_WELFARE for
id 211—January—20,000
id 221—January—5,000
id 231—January—35,000
id 211—February—20,000
id 221—February—5,000
id 231—February—35,000
id 211—March—10,000
id 221—March—40,000

SSANU_DUES for
id 211—January—1,000
id 221—January—1,000
id 231—January—1,000
id 211—February—1,000
id 221—February—1,000
id 231—February—1,000
id 211—March—1,000
id 221—March—2,000

Note: January for example, is listed three times based off your sample data. One for each ID etc.

5

Order by event,date. Let the DB engine handle the grouping; then you just need to check for when the event type changes.

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.