1. Am I going about this in the most wrongest way possible? Is there an easier, or even much better way to display year over year revenues in a view?

I think it could be reworked to merge the three getSumArray for totalPrice, total_profit, emp_total_cost in to one sql call since they are all using the same years.

2. MORE IMPORTANTLY: Any ideas how I would go about displaying this data where the Months (Jan-Dec) are displayed top to bottom as rows, and the past three years are displayed as columns?

If I understand correctly what your wanting. I don't see you retrieving the data by month I see per year. You would need to rework the code to pull each month's total, store that in a year->month->total array then I think you could make the display your talking about.

It all depends on the data and how the dates are stored.

If you want to share some example data I might be able to help more.

thanks TerrePorter.

My services table does not have a year, month or week column - just serviceDate, so I extract the year and month from that. I don't know a SQL call that can do all three sums at once. My other concern was in the way in which I was creating the arrays. Should I be building one array to pass to the view?

About the other piece, I haven't yet begun work on it - so you won't see any efforts to retrieve the months. I'm interested in learning best practices, and I'll settle for better practices. :)

I'm thinking of building a 3 multi-dimensional arrays for year and month to store revenue, profit and costs. I just can't help but think that there's a single magical object I can create that would contain it all.

The other thing I'm considering is creating a table that would store all this information immediately after a service is created. When a service is updated, so is the reporting table. This scares me in that perhaps there's a chance a service would update but not the reporting table.

Thanks

schema looks like this,

Schema::create('services', function($table){
			$table->bigIncrements('id');
			$table->date('serviceDate');
			$table->string('serviceName');
			$table->integer('serviceTypeID');
			$table->bigInteger('clientID');
			$table->integer('petQty');
			$table->float('ratePrice');
			$table->float('rateAddPrice')->nullable();
			$table->float('totalPrice');
			$table->string('serviceStatus');
			$table->integer('empID')->nullable();
			$table->boolean('billable');
			$table->bigInteger('seriesID')->nullable();
			$table->bigInteger('invoiceID')->nullable();
			$table->softDeletes();
			$table->timestamps();
		});

Thanks again.

So if my function goes through all three columns (or as many as I pass), what would the value of $sum be that I pass back? Would I be passing back an array of sums?

As for the db queries, would it be advisable to simple pull all service records, and do the filtering at the collection level?

Sounds like the summary table may be the way to go.

Thanks!

So if my function goes through all three columns (or as many as I pass), what would the value of $sum be that I pass back? Would I be passing back an array of sums?

I think you will get an array back. You will likely need to rework a few of the functions to get everything to work again.

As for the db queries, would it be advisable to simple pull all service records, and do the filtering at the collection level?

Depends on how many records your talking about, and how many you estimate it will grow to.

The more records the longer the processing time and more likely for a timeout.

Sounds like the summary table may be the way to go.

It was the best way I was able to handle the quantity of record I had to parse.

Thanks!

Your welcome :)