Query for labor cost by hour

Question

---

Edit: See Calculate how many minutes used in a given hour for how I ultimately ended up solving this.

I know how to do something like sales by hour because I'm calculating based off when the sale was actually made and not spreading it out over time. However, I'm trying to figure out the best way to calculate labor by hour given the data below.

EmployeeID  InTime   OutTime  PayRate  OTRate  DTRate
6           59:09.0  05:17.0  10.75    16.13   21.5
6           33:45.0  54:58.0  10.75    16.13   21.5
6           59:25.0  24:07.0  10.75    16.13   21.5
6           55:18.0  29:35.0  10.75    16.13   21.5
6           59:50.0  02:17.0  10.75    16.13   21.5
7           00:45.0  40:48.0  9.25     13.88   18.5
7           00:21.0  58:41.0  9.25     13.88   18.5
13          00:34.0  27:46.0  9        13.5    18
6           55:23.0  11:02.0  10.75    16.13   21.5
6           37:03.0  30:32.0  10.75    16.13   21.5

I know I'll get it figured out eventually, but if anyone could give me some pointers to speed the process and keep me from doing something overly convulted I would sure appreciate it. I need to do some more complex reports with this information, but to begin with I'm just looking for the simplest query possible that will tell me how much the employer is spending per hour on labor. Feel free to ignore overtime/doubletime because I can incorporate that back in after I get the other part figured out.

Edit: The InTime and OutTime fields are actually DateTimes... for some reason it converted the values when I copy/pasted

Edit: Results would look like...

Hour   Labor Cost

1:00       $
2:00       $
3:00       $
4:00       $
5:00       $
etc.

where $ is how ever much was spent on labor for that hour. So the first thing I'm thinking of doing is creating a table with 24 rows; one for each hour. Then the in/out times would join to the hour field in the new table. Just trying to think of what that join expression would look like now though...

Edit: Mark reminded me that I will also need to show the date along with the hour which leads me to think that my 24 row table will actually need to be 24 * however many days I'm querying over. Additionally I realized that 1 record per hour isn't going to work well because people work partial hours so I think I need to do a record for every minute and then group/sum over those results to get accurate data.

Answer 1

Untested of course, but I believe this should work minus whatever typos I've made:

-- Assume that there is a tblNumbers table
--  that has been populated with at least the values 0 to 60.
-- First, we need to find the days for which there are time records.
; WITH Days_cte (MyDay) AS
(
    SELECT DISTINCT
        -- Strip off the time from the date.  
        DATEADD(DAY, DATEDIFF(DAY, 0, InTime), 0) AS MyDay
    FROM tblTimeSheet

    UNION

    SELECT DISTINCT
        -- Strip off the time from the date.  
        DATEADD(DAY, DATEDIFF(DAY, 0, OutTime), 0) AS MyDay
    FROM tblTimeSheet
),
-- Next, explode this into hours AND minutes for every day.
-- This cte will contain 1440 records for every day.
Times_cte (MyTime) AS
(
    SELECT
        DATEADD(MINUTE, minutes.Number, 
                DATEADD(HOUR, hours.Number, days.MyDay)) AS MyTime
    FROM
        Days_cte days JOIN
        tblNumbers hours ON hours.Number < 24 JOIN
        tblNumbers minutes ON minutes.Number < 60 LEFT JOIN
)
-- Now, determine which of these minutes 
--  falls between an employee's in and out time.
SELECT
    -- Strip off the minutes, leaving only the hour.
    DATEADD(HOUR, DATEDIFF(HOUR, 0, times.MyTime), 0) AS [Hour],
    -- Divide by 60, since the aggregation is done by the minute.
    SUM(ISNULL(ts.PayRate, 0) / 60) AS LaborCost
FROM    
    Time_cte times LEFT JOIN
    tblTimeSheet ts ON times.MyTime BETWEEN ts.InTime AND ts.OutTime
GROUP BY
    DATEADD(HOUR, DATEDIFF(HOUR, 0, times.MyTime), 0)