# Monthly Cumulative Calculation from Salary deduction

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

Date really should be stored YYYY-MM-DD

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

Thank you.

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.

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

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