October 5, 2011 at 2:56 pm
Hi all,
Got a bit of a strange problem that I need to solve.
Lets say I have two tables, like so:
CREATE TABLE #PlannedHours
(
ID INT IDENTITY PRIMARY KEY,
UserID INT,
[TimeStart] SMALLDATETIME,
[TimeEnd] SMALLDATETIME,
[Type] TINYINT
)
CREATE TABLE #ActualHours
(
ID INT IDENTITY PRIMARY KEY,
UserID INT,
[TimeStart] SMALLDATETIME,
[TimeEnd] SMALLDATETIME
)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 08:00:00', '2011-09-27 09:45:00', 1)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 09:45:00', '2011-09-27 10:00:00', 2)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 10:00:00', '2011-09-27 11:30:00', 1)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 11:30:00', '2011-09-27 12:30:00', 3)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 12:30:00', '2011-09-27 14:15:00', 2)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 14:15:00', '2011-09-27 16:30:00', 1)
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd])
VALUES (1, '2011-09-27 08:00:00', '2011-09-27 09:55:00')
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd])
VALUES (1, '2011-09-27 10:11:00', '2011-09-27 11:28:00')
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd])
VALUES (1, '2011-09-27 12:28:00', '2011-09-27 14:28:00')
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd])
VALUES (1, '2011-09-27 14:43:00', '2011-09-27 16:28:00')
These tables contain a schedule that is supposed to be followed, as well as a schedule which was actually followed. The Type value in #PlannedHours is not really required - the only thing to know is that Type = 1 is the hours that are supposed to be worked, while Type > 1 are hours which are breaks.
The result that I should obtain from my query against these two sets of data, is the amount of time that the user was working when they were not supposed to be working, and the amount of time that they were not working when they were supposed to be.
So, as an example calculation for this set of data,
Row 1: From 8:00AM to 9:55 AM. Was supposed to work from 8:00AM to 9:45 AM -- 10 minutes
Row 2: From 10:11 AM to 11:28 AM. Was supposed to work from 10:00AM to 11:30 AM -- 13 minutes
Row 3: From 12:28 PM to 14:28 PM. Was supposed to work from 12:30 PM to 14:15 PM -- 15 minutes
Row 4: From 14:43 PM to 16:28 PM. Was supposed to work from 14:30 PM to 16:30 PM -- 15 minutes
The total sum of those is 53 minutes, which is the result I need.
Now, I know I can do these calculations by going row-by-row - start from a single record, scan the table, find the block that it should fit into, and then calculate how much it doesn't fit in.
But I'd like to avoid that - its obviously a messy calculation.
I'm wondering if there's any other approach. I'm heading out for the day and I'll be thinking about it overnight, but if anyone has any suggestions I'd love to hear them.
October 5, 2011 at 3:17 pm
Nice job setting up the data!!!
I don't think you desired output matches what you stated you want for expected results.
Row 1: From 8:00AM to 9:55 AM. Was supposed to work from 8:00AM to 9:45 AM -- 10 minutes
Row 2: From 10:11 AM to 11:28 AM. Was supposed to work from 10:00AM to 11:30 AM -- 13 minutes
Row 3: From 12:28 PM to 14:28 PM. Was supposed to work from 12:30 PM to 14:15 PM -- 15 minutes
Row 4: From 14:43 PM to 16:28 PM. Was supposed to work from 14:30 PM to 16:30 PM -- 15 minutes
Row1 = ID 1 does equal 10 minutes.
Row2 = ID2 your planned data is 9:45 - 10:00 and 10:11 - 11:28 as actual that would be 62 minutes.
Row3 = ID3 your planned data is 10:00 - 11:30 and actual is 12:28 - 14:28 that would be 30 minutes.
Row4 = ID4 your planned is 11:30 - 12:30 and actual is 14:43 - 16:28 that would be 45 minutes.
Assuming my calculations and my interpretation of your issue are correct you can do this by getting a sum of the amount of minutes planned - amount of minutes actual.
To see each row calculation you could do something like this.
select DATEDIFF(n, ph.timeend, ah.timeend) - DATEDIFF(n, ph.timestart, ah.timestart) as MinutesDifferent, *
from #PlannedHours ph
join #ActualHours ah on ph.ID = ah.ID
Now if as you say you just want to grand total you would sum the calculation like this:
select sum(DATEDIFF(n, ph.timeend, ah.timeend) - DATEDIFF(n, ph.timestart, ah.timestart)) as TotalMinutesDifferent
from #PlannedHours ph
join #ActualHours ah on ph.ID = ah.ID
Does that sound like it what you are looking for?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 7:10 am
I'll have to check your query when I get to work, but no, the calculations I made were correct.
The records that are Type = 1 are the ones which are the hours the person is supposed to be working. Those are the ones that need to be matched against the hours that the person actually worked. The ones which are Type > 1 are breaks / lunch hours, and can probably be ignored - I only put them there so that the times for the day were a complete block instead of having gaps.
October 6, 2011 at 8:25 am
kramaswamy (10/6/2011)
I'll have to check your query when I get to work, but no, the calculations I made were correct.The records that are Type = 1 are the ones which are the hours the person is supposed to be working. Those are the ones that need to be matched against the hours that the person actually worked. The ones which are Type > 1 are breaks / lunch hours, and can probably be ignored - I only put them there so that the times for the day were a complete block instead of having gaps.
Well that business rule would have been nice to know. So now it seems like you just want to total minutes from planned where type = 1 MINUS total minutes of acutal hours?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 8:49 am
kramaswamy (10/5/2011)
Row 1: From 8:00AM to 9:55 AM. Was supposed to work from 8:00AM to 9:45 AM -- 10 minutesRow 2: From 10:11 AM to 11:28 AM. Was supposed to work from 10:00AM to 11:30 AM -- 13 minutes
Row 3: From 12:28 PM to 14:28 PM. Was supposed to work from 12:30 PM to 14:15 PM -- 15 minutes
Row 4: From 14:43 PM to 16:28 PM. Was supposed to work from 14:30 PM to 16:30 PM -- 15 minutes
I think that you're leaving out the sign. In row 1 he worked 10 minutes more than was scheduled, but in row 2 he worked 13 minutes less. Shouldn't those two rows have different signs?
Row 3 has a type of 2 which should be a break, but you say that he was supposed to be working (which should be Type 1).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 6, 2011 at 9:02 am
The other problem I see with the data is that if only Type1 from Planned are scheduled work blocks there are only 3 of those in the PlannedHours table but 4 actual work blocks.
Planned
8:00 - 9:45 = 105 minutes
10:00 - 11:30 = 90 minutes
14:15 - 16:30 = 135 minutes
Grand total planned = 330 minutes
Actual
8:00 - 9:55 = 115 minutes
10:11 - 11:28 = 77 minutes
12:28 - 14:28 = 120 minutes
14:43 - 16:28 = 105 minutes
Grand total actual = 417 minutes
Unless there is something I am missing that is a difference of 87 minutes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 9:39 am
Yeah sorry looking at the data I realise that I made a mistake on that record. It should be:
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 08:00:00', '2011-09-27 09:45:00', 1)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 09:45:00', '2011-09-27 10:00:00', 2)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 10:00:00', '2011-09-27 11:30:00', 1)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 11:30:00', '2011-09-27 12:30:00', 3)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 12:30:00', '2011-09-27 14:15:00', 1)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 14:15:00', '2011-09-27 14:30:00', 2)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 14:30:00', '2011-09-27 16:30:00', 1)
Apologies!
October 6, 2011 at 9:40 am
In this case Drew, it's supposed to be a sum of both the times that the person worked when they were not supposed to, and also didn't work when they were. So the signs should be the same.
October 6, 2011 at 9:51 am
Even with the correction I just can't come up with your 53 minutes. It just doesn't add up. The grand total here is only 3 minutes. Work block 3 and work block 4 offset each other because one is 15 over and the other is 15 under.
select DATEDIFF(N, timestart, timeend), *
from #PlannedHours
where TYPE = 1
select sum(DATEDIFF(N, timestart, timeend))
from #PlannedHours
where TYPE = 1
select DATEDIFF(N, timestart, timeend), *
from #ActualHours
select sum(DATEDIFF(N, timestart, timeend))
from #ActualHours
I am willing to help but you need to explain how you came up with 53 minutes. Oh wait...I think you are looking for a running total??? You want the sum of the differences NOT a difference of the sums??? Let me know which one you are looking for. They are both pretty straight forward but will be different in the way you code them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 10:14 am
Yeah it would be a sum of the differences - the minutes which are out of sync with the schedule that the person should be working. The 15 minutes over and 15 minutes under both add together to count for 30 minutes in total
October 6, 2011 at 10:20 am
Do you have a way to tie the actual hours to the planned hours? I see a UserID which tells who the individual is but there is nothing that says the third work block from the planned table (ID 11) matches the actual work block (ID 3). You could use ROW_NUMBER so that the same entry by timestart is the match but that is pretty sloppy. There is no way to make sure you don't have a missing record. If we used that approach it would be impossible to determine that there was a record missing (like in your original dataset). Seems that you should add a PlannedHoursID or something like that to the actual hours. Let me know if you want to just use ROW_NUMBER and you can deal with validation somewhere else. That approach screams at me as being wrong but with the data you have that may be all you can do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 10:22 am
Yeah I know what you mean Sean. Unfortunately, no, I don't have anything like that.
But I wonder if this solution would work:
WITH cte AS
(
SELECT
#ActualHours.UserID,
#ActualHours.TimeStart,
#ActualHours.TimeEnd,
ABS(DATEDIFF(mi, #PlannedHours.TimeStart, #ActualHours.TimeStart)) AS StartDiff,
ABS(DATEDIFF(mi, #PlannedHours.TimeEnd, #ActualHours.TimeEnd)) AS EndDiff
FROM #ActualHours
JOIN #PlannedHours ON#ActualHours.UserID = #PlannedHours.UserID
AND #PlannedHours.[Type] = 1
),
cte2 AS
(
SELECT TimeStart, TimeEnd, MIN(StartDiff) AS TotalStartDiff, MIN(EndDiff) AS TotalEndDiff FROM cte
GROUP BY TimeStart, TimeEnd
)
SELECT SUM(TotalStartDiff) + SUM(TotalEndDiff)
FROM cte2
I feel like it can't be right, since I'm ignoring pretty much everything except the actual data, but it does give me the right answer ...
October 6, 2011 at 10:30 am
I think that works with a minor caveat, this only works when there is one UserID in the table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 10:42 am
Mm.. yeah I'll worry about tackling the multiple UserIDs after.
Discovered a problem though:
Let's say I change the data to this:
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd])
VALUES (1, '2011-09-27 08:00:00', '2011-09-27 10:30:00') -- + 15 min (Worked for 15 minutes of break time)
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd])
VALUES (1, '2011-09-27 10:45:00', '2011-09-27 11:28:00') -- - 15 min (Did not work for 15 minutes of work time), - 2 min (Did not work for 2 minutes of work time)
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd])
VALUES (1, '2011-09-27 12:28:00', '2011-09-27 14:28:00') -- - 2 min (Worked for 2 minutes of break time), + 13 min (Worked for 13 minutes of break time)
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd])
VALUES (1, '2011-09-27 14:43:00', '2011-09-27 16:28:00') -- - 13 min (Did not work for 13 minutes of work time), - 2 min (Did not work for 13 minutes of work time)
-- 92 min total
What my code will do, is see the TimeEnd at 10:30, and say that's 45 minutes out of sync with the schedule. This is incorrect based off the calculations above. The total time should work out to 15 + 15 + 2 + 2+ 13 + 13 + 2 = 62 minutes.
October 6, 2011 at 10:48 am
Yeah I was originally thinking of going about this in blocks. Get the total minutes scheduled for each block then the number of minutes worked for each block but I don't think that is really what you are after either. In other words if they started and ended two minutes late the method I described would be 0 and you want that to be 4. Seems to me the whole structure is just missing some stuff. You have the data but it is so loosely tied together that this is likely to never work 100%. I got a couple meetings to get to but I will think about a way to see if we can make this work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply