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

score 0 · Answer 1 · answered Oct 22 '11 at 11:09

0

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.

answered Oct 22 '11 at 11:09

Ifewalter

114
2
6

1

By SO rules, we don't want to see this type of comments posted as an answer either – Icarus Oct 22 '11 at 11:13
@Icarus: lfewater has no rep for comments, though. – ypercubeᵀᴹ Oct 22 '11 at 11:27
Sorry mate, I've added it now. – jksdua Oct 22 '11 at 11:30
:D, @lcarus, judging by his auto-generated ID, he's new here (as i am). someone had to tell him or he won't get answers. – Ifewalter Oct 22 '11 at 12:06

Create hourly time slots from start and finish time

1 Answers1