Time Utilization / Allocation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Time Utilization / Allocation

Author

Topic

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2007-11-21 : 11:02:26

My problem is very close to this topic: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66397&SearchTerms=Time,Overlap

My workers can be 'clocked in' to multiple tickets at the same time. Each time they clock into a particular Work Order, a Labor ticket is created, very-simply a row in the DB with clock in / out information (and Work Order info that we will ignore).

I've done this with Pen and Paper and am looking to get it working in SQL.

Here is my example set


CREATE TABLE #TIME (ID INT, CLOCK_IN datetime, CLOCK_OUT DATETIME, ACTUAL_HOURS DECIMAL(10,2), WEIGHTED_HOURS DECIMAL(10,2), ALLOCATION DECIMAL(13,4))
INSERT INTO #TIME 
SELECT 1, '11/21/2007 12:00:00.000', '11/21/2007 12:30:00.000', 0.50, NULL, NULL UNION ALL
SELECT 2, '11/21/2007 12:00:00.000', '11/21/2007 13:00:00.000', 1.00, NULL, NULL UNION ALL
SELECT 3, '11/21/2007 12:30:00.000', '11/21/2007 13:15:00.000', 0.75, NULL, NULL

SELECT 
	* 
FROM 
	#TIME

DROP TABLE #TIME

What I need to do, is calculate the Weighted Hours, and Allocation for each ticket, and update the row with its allocation value. Here is how I am calculating allocation (I'm sure there is some official math term for this problem):

I find the point in time when a ticket is clocked into, or out of. The first point is right at Noon. The second point is at 12:30pm. Ticket 1 (T1) is clocked out, so calculate the Weighted Hours for that Ticket. Over its 30 minute span, there was always one other ticket active (T2).. So the worker only *actually* worked on T1 for 15 minutes, which is his Weighted Hours. The ticket's allocation is found by WEIGHTED_HOURS / ACTUAL_HOURS, therefore 50%.

The 3rd point is at 1pm, when T2 is clocked out. Calculate its allocation: Over its 60 minute time span, there was always and exactly one ticket clocked in (T1, and T3). The Weighted Hours is 30, its allocation is also 50%.

The final point is at 1:15pm, T3 clocks out. For most of the time span, it shared the worker's efforts with T2, but for the final 15 minutes, the worker was only working on T3. Find the WEIGHTED_HOURS by adding time that it split with T2 (15 minutes), and the time it didn't split (15 minutes). WEIGHTED_HOURS is 30 minutes. Allocation is then 30 minutes / 45 minutes.. 66%.

So after the story problem I wrote, the table will look like this


ID     |    CLOCK IN          |       CLOCK OUT        | ACT_HRS| WEIGHTED HRS | ALLOCATION      
1	2007-11-21 12:00:00.000	2007-11-21 12:30:00.000	0.50	    .25	          .5
2	2007-11-21 12:00:00.000	2007-11-21 13:00:00.000	1.00	    .5	          .5
3	2007-11-21 12:30:00.000	2007-11-21 13:15:00.000	0.75	    .5	          .66

Confirmed by if you take the real time he was working, Noon - 1:15pm, that is 1 hour and 15 minutes. Adding the Weighted Hours gets this 1 hr and 15 minutes.

Any ideas on how I can start to tackle the problem? My ERP system's reports show that the worker in my example worked for 2.25 hours (just adds actual hours). Are there any resources/articles I should look at?

There potentially could be gaps of time, where no tickets are active. I also may need to 'force' lunch in the equasion where if a labor ticket spans lunch time, that is not counted toward the weighted hrs or allocation. This be a stored proc that runs at the end of the day, adjusting all of the labor tickets for that day.

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2007-11-26 : 09:44:02

Is SQL even the right way to solve this problem? I can't see it happening without lots of temp tables and while loops. I'm much stronger in C# which is where I might take this problem.

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2007-11-26 : 11:33:11

Probably not the most efficient, here's what I came up with


DECLARE @START AS DATETIME
DECLARE @FINISH AS DATETIME
DECLARE @CURRENT_RANGE_START AS DATETIME
DECLARE @CURRENT_RANGE_FINISH AS DATETIME
DECLARE @NEXT_POINT_START AS DATETIME
DECLARE @NEXT_POINT_FINISH AS DATETIME
DECLARE @HOURS_IN_RANGE AS DECIMAL(10,3)
DECLARE @TICKETS_IN_RANGE AS INT
DECLARE @WEIGHT_TO_ADD AS DECIMAL(10,3)

SET @START = '11/21/2007 00:00:00.000'
SET @FINISH = '11/22/2007 00:00:00.000'

CREATE TABLE #TIME (ID INT, CLOCK_IN datetime, CLOCK_OUT DATETIME, ACTUAL_HOURS DECIMAL(10,3), WEIGHTED_HOURS DECIMAL(10,3), ALLOCATION DECIMAL(13,4))
INSERT INTO #TIME 
SELECT 1, '11/21/2007 12:00:00.000', '11/21/2007 12:30:00.000', 0.500, 0.000, NULL UNION ALL
SELECT 2, '11/21/2007 12:00:00.000', '11/21/2007 13:00:00.000', 1.000, 0.000, NULL UNION ALL
--SELECT 3, '11/21/2007 12:20:00.000', '11/21/2007 13:15:00.000', 0.916, 0.000, NULL
SELECT 3, '11/21/2007 12:30:00.000', '11/21/2007 13:15:00.000', 0.750, 0.000, NULL

SELECT * FROM #TIME
/**** GET FIRST TICKET FOR THE DATE RANGE PASSED ****/
SET @CURRENT_RANGE_START = 
(
	SELECT 
		CLOCK_IN
	FROM 
		#TIME
	WHERE
		CLOCK_IN >= @START AND
		CLOCK_IN < @FINISH AND 
		WEIGHTED_HOURS = 0.000 AND
		ALLOCATION IS NULL and
		CLOCK_IN = (SELECT MIN(CLOCK_IN) FROM #TIME WHERE CLOCK_IN > @START AND	CLOCK_IN < @FINISH AND 	WEIGHTED_HOURS = 0.000 AND	ALLOCATION IS NULL) 
	GROUP BY CLOCK_IN
)

WHILE(@CURRENT_RANGE_START < @FINISH)
BEGIN

	--SELECT @CURRENT_RANGE_START AS CURRENT_RANGE_START

	SET @NEXT_POINT_START = (SELECT MIN(CLOCK_IN) FROM #TIME WHERE CLOCK_IN > @CURRENT_RANGE_START AND CLOCK_IN < @FINISH AND	ALLOCATION IS NULL)
	SET @NEXT_POINT_FINISH = (SELECT MIN(CLOCK_OUT) FROM #TIME WHERE CLOCK_OUT > @CURRENT_RANGE_START AND CLOCK_IN < @FINISH AND ALLOCATION IS NULL)

	--SELECT @NEXT_POINT_START AS NEXT_POINT_START
	--SELECT @NEXT_POINT_FINISH AS NEXT_POINT_FINISH
	
	IF(@NEXT_POINT_START IS NULL AND @NEXT_POINT_FINISH IS NULL)		
		BREAK

	IF(@NEXT_POINT_FINISH IS NULL AND @NEXT_POINT_START IS NULL)		
		BREAK

	IF(@NEXT_POINT_START >= @NEXT_POINT_FINISH)	
		SET @CURRENT_RANGE_FINISH = @NEXT_POINT_FINISH	

	IF(@NEXT_POINT_START < @NEXT_POINT_FINISH)	
		SET @CURRENT_RANGE_FINISH = @NEXT_POINT_START
	
	IF(@NEXT_POINT_START IS NULL)	
		SET @CURRENT_RANGE_FINISH = @NEXT_POINT_FINISH	

	--SELECT @CURRENT_RANGE_FINISH AS CURRENT_RANGE_FINISH 

	SET @HOURS_IN_RANGE = (DATEDIFF(MI, @CURRENT_RANGE_START, @CURRENT_RANGE_FINISH)/60.000)
	--SELECT @HOURS_IN_RANGE AS HOURS_IN_RANGE

	SET @TICKETS_IN_RANGE = (SELECT COUNT(*) FROM #TIME WHERE CLOCK_IN <= @CURRENT_RANGE_START AND CLOCK_OUT >= @CURRENT_RANGE_FINISH)
	--SELECT @TICKETS_IN_RANGE AS TICKETS_IN_RANGE

	SET @WEIGHT_TO_ADD = @HOURS_IN_RANGE / @TICKETS_IN_RANGE
	--SELECT @WEIGHT_TO_ADD AS WEIGHT_TO_ADD

	UPDATE 
		#TIME 
	SET 
		WEIGHTED_HOURS = WEIGHTED_HOURS + @WEIGHT_TO_ADD 
	WHERE 
		CLOCK_IN <= @CURRENT_RANGE_START AND 
		CLOCK_OUT >= @CURRENT_RANGE_FINISH

	SET @CURRENT_RANGE_START = @CURRENT_RANGE_FINISH
END

UPDATE 
	#TIME
SET
	ALLOCATION = (WEIGHTED_HOURS / ACTUAL_HOURS)
WHERE
	CLOCK_IN BETWEEN @START AND @FINISH

SELECT * FROM #TIME
DROP TABLE #TIME

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources