Create hourly time slots from start and finish time

Question

Hoping you can help me with something I've been stuck on for ages.

I have two tables. One has employee records with fields cost, start time and finish time. Other table has revenue with actual time. I want to generate a report which groups revenue and cost by hourly time slots. What's the best approach for doing this?

I am using mysql.

Code used:

SELECT
    `Employee Number`,
    `Shift Start`,
    `Shift Finish`,
    `Shift Length`,
    STR_TO_DATE(`Shift Start`, '%m/%d/%Y %h:%i:%s %p') AS ShiftStart,
    STR_TO_DATE(`Shift Finish`, '%m/%d/%Y %h:%i:%s %p') AS ShiftFinish,
    timestampdiff(
        MINUTE, STR_TO_DATE(`Shift Start`, '%m/%d/%Y %h:%i:%s %p'), 
        STR_TO_DATE(`Shift Finish`, '%m/%d/%Y %h:%i:%s %p')) / 60 AS Diff
FROM table_name;

Table structure (last column is what I've added in the query): http://ge.tt/9yzYx39?c

Answer 1

By stack exchange rules, we want to see the structure of your table, and some piece of code that says you know what you are doing and you tried your best already.