Adding new column to Flickerbooks payments DB and generating values for it from existing data

During the last few months while working on Flickerbook there were several occasions where I had to create new columns and fill it using data from already existing columns.

This process composes of three steps:

• Run a query against database and retrieve the data necessary.
• Download data as PHP array.
• Write a loop that calculates new values using existing data and inserts them into new column.

The issue I came into is when I want to calculate hourly averages, I want to divide all earnings by all hours worked. To do that I currently use a sum of gross pay earnings and a sum of hours worked. However this approach is not entirely accurate, as gross pay not necessarily represents all payments, mainly due to pension being deducted as a salary sacrifice. This means that in order to get accurate hourly averages, if a pension is a salary sacrifice, I must add the pension deduction to gross pay and only then divide it by the sum of hours worked.

So the formula for calculating hourly averages should look like this: (Gross Pay + Pension Sacrifice)/Hours Worked.

To make my life easier I created a new column in the payments table that will store the sum of gross pay and pension sacrifice and I named this column grossPayWithPenSac . So now I need to fill this new column with data.

Querying database

To select the data necessary for this new column, I wrote this SQL query:

SELECT `weekly_payments_amount.user_id`,`weekly_payments_amount.jobID`,`weekly_payments_amount.taxPeriodNr`,`gross_pay`, `pensionBTax`, `pension`
FROM weekly_payments_amount
INNER JOIN weekly_deductions_amount
	ON weekly_payments_amount.user_id=weekly_deductions_amount.user_id 
	AND weekly_payments_amount.jobID=weekly_deductions_amount.jobID
	AND weekly_payments_amount.taxPeriodNr=weekly_deductions_amount.taxPeriodNr
WHERE 1

The data I need is user id, tax period number, job id, gross pay, a variable that determines if a pension is a sacrifice (pensionBTax) and the amount of pension.

Downloading results as PHP array

After the query has been executed on my server there is an option to download results as a PHP array. A sample of this array is demonstrated below:

$weekly_payments_amount = array(
  array('user_id' => '1','jobID' => '1','taxPeriodNr' => '716','gross_pay' => '1869.13','pensionBTax' => '1','pension' => '98.38'),
  array('user_id' => '1','jobID' => '1','taxPeriodNr' => '528','gross_pay' => '1108.20','pensionBTax' => '1','pension' => '58.33'),
  array('user_id' => '1','jobID' => '1','taxPeriodNr' => '608','gross_pay' => '964.65','pensionBTax' => '1','pension' => '50.77'),
  array('user_id' => '1','jobID' => '1','taxPeriodNr' => '736','gross_pay' => '846.68','pensionBTax' => '1','pension' => '44.56'),
  array('user_id' => '1','jobID' => '1','taxPeriodNr' => '632','gross_pay' => '844.17','pensionBTax' => '1','pension' => '44.43'),
  array('user_id' => '1','jobID' => '1','taxPeriodNr' => '740','gross_pay' => '842.04','pensionBTax' => '1','pension' => '44.32'),
  array('user_id' => '1','jobID' => '1','taxPeriodNr' => '559','gross_pay' => '836.58','pensionBTax' => '1','pension' => '44.03'),
     <.................................................................................................................>
  array('user_id' => '3','jobID' => '1','taxPeriodNr' => '705','gross_pay' => '640.96','pensionBTax' => '0','pension' => '0.00'),
  array('user_id' => '3','jobID' => '1','taxPeriodNr' => '706','gross_pay' => '435.00','pensionBTax' => '0','pension' => '0.00')
);

Generating data and inserting it into new column

Once the new column has been added to the database, I just run a for loop which picks values from the array, calculates grossPayWithPenSac and then updates it from 0 to new value for each row in the database. The for loop is demonstrated below.

for ($i=0;$i<COUNT($weekly_payments_amount); $i++){
	$user_id = $weekly_payments_amount[$i]['user_id'];
	$taxPerodNumber = $weekly_payments_amount[$i]['taxPeriodNr'];
	$jobID =$weekly_payments_amount[$i]['jobID'];
	//by default grossPayWithPenSac is equal to gross_pay
	$grossPayWithPenSac = $weekly_payments_amount[$i]['gross_pay'];
	//if a pension was a salary sacrifice, set the grossPayWithPenSac value to sum of gross_pay and pension.
	if ($weekly_payments_amount[$i]['pensionBTax'] == "1") {
		$grossPayWithPenSac = $weekly_payments_amount[$i]['gross_pay'] +$weekly_payments_amount[$i]['pension'];
	}
	//update value in the database.
	$queryInsertPayments = "UPDATE weekly_payments_amount SET grossPayWithPenSac =
	'$grossPayWithPenSac'
		WHERE
		user_id = '$user_id' AND
		jobID = '$jobID' AND
		taxPeriodNr = '$taxPerodNumber'
	";
	$resultInsertPayments = mysqli_query($weeklyPaymentsDeductions, $queryInsertPayments);
}

Summary

Now I have two values in database that represent different type of gross pay, which enables me to calculate averages a lot easier, as I don’t need to write additional queries to get the sum of gross pay and pension sacrifice.

After running this query against database.

SELECT `gross_pay`, `grossPayWithPenSac` 
FROM `weekly_payments_amount` 
WHERE `gross_pay` !=`grossPayWithPenSac`
ORDER BY `weekly_payments_amount`.`grossPayWithPenSac` DESC

I get these results:

and this is exactly what I was aiming for.

One more issue I have with my current database structure is that I don’t have any column that would store a tax year value, like “2023/2024” tax year. Without this I can’t use a group by statement and group the data by tax years. I currently retrieve the data for YOY charts by running a PHP loop for each tax year, as I know the first and last value of each weekly tax period for every tax year, and using these I retrieve the SUM values. However If I had a column of year for every tax year it would make an SQL query a lot cleaner. This will be something I will be working to sort out 😉