Warehouse Database Design Question
You are best to start with a transactional database that records the data in as much detail as you can get from the source.  You can always summarize and report from detailed data, but you can't go backwards from summary information to detailed information.

If you are getting data in the form:

  transaction_date  datetime
, pallets_taken_in  int
, pallets_sent_out  int
, total_work_effort decimal(19,2)


Then create a table with these columns.  If you get one record per day then make the transaction_date a unique index (or even primary key).

Calculating the values for your report is easy from these columns:


Pallets handled = pallets_taken_in + pallets_sent_out for a given transaction_date
Pallets/hour = (pallets_taken_in + pallets_sent_out) / total_work_effort for a given transaction_date
Pallets on hand = SUM(pallets_taken_in) - SUM(pallets_sent_out) over all records in your table. (i.e. over all dates)


Note that depending on how you key your table, you can also include physical inventory adjustment records to make allowance for correcting your balance on hand.

Depending on how much data you have and how much (and what kind of) reporting you need, it might make sense to pre-calculate some of these values and store them in a second table in a data warehouse type of scheme, but don't pre-calculate data unless you're pretty sure you need to.  Having two copies of the same data (raw transactions and data warehouse) means that you have potential discrepancies to manage.