I get the timeslots table but am not so sure I understand how the query helps me find how many minutes are in each inteval..
Example, if the agent was in Coaching from 9am to 12am I need to figure out how to get it so the query brings back that there were 30 minutes used from 9 to 930, 30 minutes from 930 to 10, 30 minutes from 10 to 1030, etc etc..
Since you want counts rounded to half hours, we can build another look up table for the math. The second table is keyed on all possible properly ordered pairs of time slots. But now we need a business rule about how to count a session that lasts less than one half hour. I will say zero, because you cannot get anything done.
CREATE TABLE Session_Hours
(slot_start_nbr SMALLINT NOT NULL,
slot_end_nbr SMALLINT NOT NULL,
CHECK (slot_start_nbr, slot_end_nbr),
PRIMARY KEY (slot_start_nbr <= slot_end_nbr),
session_hrs DECIMAL(4,2) NOT NULL);
Now we take two clock times and fit them into time slots. The slot numbers tell us the hourly count in the second look up table. This will be handy for other scheduling problems, so it is not a redundant as it first looks. You can set any rule you want with this table! Make sessions after 18:00 Hrs count double! Bill them triple for 00:00 to 06:00 Hrs, etc.
Here is a query done with a CTE to show the nesting of the code clearly.
CREATE PROCEDURE Session_Billing_Duration
(@in_start_time TIME(0), @in_end_time TIME(0))
WITH Slots (slot_start_nbr, slot_end_nbr)
(SELECT T1.timeslot_nbr, T@.timeslot_nbr
FROM timeslots AS T1, Timeslots AS T2
WHERE @in_start_time BETWEEN T1.start_time AND T1.end_time
AND @in_end_time BETWEEN T2.start_time AND T2.end_time),
SELECT H.session_hrs AS billing_hrs
FROM Session_Hours AS H, Slots AS S
WHERE S.slot_start_nbr = H.slot_start_nbr
AND S.slot_end_nbr = H.slot_end_nbr;
If you want to change the temporal granularity, it is easy to do with a spreadsheet or a quick query.