October 6, 2011 at 11:57 am
I'm going to throw a wrench in the works. All of your data has had roughly equivalent start and end times for the planned and actual data. Suppose that a person has a 15 min break scheduled but ends up taking it a 30 min late. This creates a situation where both the planned and actual data overlaps two different records on the other side of the equation. How do you want to handle that?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 6, 2011 at 11:59 am
That's pretty much the exact problem scenario I just painted in my last post drew 😛
Still thinking about how I can handle that. Haven't come up with an answer yet.
October 6, 2011 at 1:07 pm
Here is code that gives the correct result for your sample data. From there, you should be able to figure out a way to optimize it.
;WITH PlannedWork AS (
SELECT *
FROM #PlannedHours
WHERE Type = 1
)
, PlannedBreak AS (
SELECT *
FROM #PlannedHours
WHERE Type > 1
)
, ActualBreak AS (
SELECT a1.UserID, a1.TimeEnd AS TimeStart, IsNull(a2.TimeEnd, Cast(DateDiff(Day, -1, a1.TimeEnd) AS datetime)) AS TimeEnd
FROM #ActualHours AS a1
CROSS APPLY (
SELECT Min(TimeStart) AS TimeEnd
FROM #ActualHours AS a2
WHERE a1.UserID = a2.UserID
AND a1.TimeStart < a2.TimeStart
) AS a2
)
,Deviations AS (
SELECT ab.UserID, d.DeviationStart, d.DeviationEnd
FROM ActualBreak AS ab
INNER JOIN PlannedWork AS pw
ON ab.TimeStart <= pw.TimeEnd
AND pw.TimeStart <= ab.TimeEnd
CROSS APPLY (
SELECT CASE WHEN ab.TimeStart < pw.TimeStart THEN pw.TimeStart ELSE ab.TimeStart END AS DeviationStart
,CASE WHEN ab.TimeEnd > pw.TimeEnd THEN pw.TimeEnd ELSE ab.TimeEnd END AS DeviationEnd
) AS d
UNION ALL
SELECT pb.UserID, d.DeviationStart, d.DeviationEnd
FROM PlannedBreak AS pb
INNER JOIN #ActualHours AS ah
ON pb.TimeStart <= ah.TimeEnd
AND ah.TimeStart <= pb.TimeEnd
CROSS APPLY (
SELECT CASE WHEN pb.TimeStart < ah.TimeStart THEN ah.TimeStart ELSE pb.TimeStart END AS DeviationStart
,CASE WHEN pb.TimeEnd > ah.TimeEnd THEN ah.TimeEnd ELSE pb.TimeEnd END AS DeviationEnd
) AS d
)
SELECT UserID, Sum(DateDiff(Minute, DeviationStart, DeviationEnd)) AS TotalDeviation
FROM Deviations
GROUP BY UserID
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 6, 2011 at 1:23 pm
That one unfortunately gives me a result of 92 minutes, while the actual result should be 62 minutes.
October 6, 2011 at 1:26 pm
kramaswamy (10/6/2011)
That one unfortunately gives me a result of 92 minutes, while the actual result should be 62 minutes.
I'm getting 62 minutes. Which set of data are you using?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 6, 2011 at 1:37 pm
Ah good call - forgot I had modified my data set when I was experimenting with different options. Perfect, that works! Thanks!
October 6, 2011 at 1:53 pm
Excellent, tested it with a second user, and it still works, after a few minor modifications (in bold):
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,
[Type] TINYINT
)
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)
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 08:00:00', '2011-09-27 10:30:00', 1)
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 10:45:00', '2011-09-27 11:28:00', 1)
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 12:28:00', '2011-09-27 14:28:00', 1)
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 14:43:00', '2011-09-27 16:28:00', 1)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (2, '2011-09-27 08:00:00', '2011-09-27 09:00:00', 1)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (2, '2011-09-27 09:00:00', '2011-09-27 09:15:00', 2)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (2, '2011-09-27 09:15:00', '2011-09-27 11:30:00', 1)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (2, '2011-09-27 11:30:00', '2011-09-27 12:30:00', 3)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (2, '2011-09-27 12:30:00', '2011-09-27 14:00:00', 1)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (2, '2011-09-27 14:00:00', '2011-09-27 14:30:00', 2)
INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (2, '2011-09-27 14:30:00', '2011-09-27 16:45:00', 1)
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (2, '2011-09-27 08:00:00', '2011-09-27 10:30:00', 1) -- Worked from 9:00-9:15 (15)
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (2, '2011-09-27 10:45:00', '2011-09-27 11:28:00', 1) -- Break from 10:30-10:45 (15), Break At 11:28 (2)
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (2, '2011-09-27 12:28:00', '2011-09-27 14:28:00', 1) -- Work at 12:28 (2), Worked from 14:00-14:28 (28)
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (2, '2011-09-27 14:43:00', '2011-09-27 16:28:00', 1) -- Break from 14:30-14:43 (13), Worked to 16:28 (17)
;
WITH PlannedWork AS
(
SELECT *
FROM #PlannedHours
WHERE Type = 1
),
PlannedBreak AS
(
SELECT *
FROM #PlannedHours
WHERE Type > 1
),
ActualBreak AS
(
SELECT a1.UserID, a1.TimeEnd AS TimeStart, ISNULL(a2.TimeEnd, CAST(DATEDIFF(DAY, -1, a1.TimeEnd) AS DATETIME)) AS TimeEnd
FROM #ActualHours AS a1
CROSS APPLY
(
SELECT Min(TimeStart) AS TimeEnd
FROM #ActualHours AS a2
WHEREa1.UserID = a2.UserID
AND a1.TimeStart < a2.TimeStart
) AS a2
),
Deviations AS
(
SELECT ab.UserID, d.DeviationStart, d.DeviationEnd
FROM ActualBreak AS ab
INNER JOIN PlannedWork AS pw ONab.TimeStart <= pw.TimeEnd
AND pw.TimeStart <= ab.TimeEnd
AND ab.UserID = pw.UserID
CROSS APPLY
(
SELECT
(CASE WHEN ab.TimeStart < pw.TimeStart THEN pw.TimeStart ELSE ab.TimeStart END) AS DeviationStart,
(CASE WHEN ab.TimeEnd > pw.TimeEnd THEN pw.TimeEnd ELSE ab.TimeEnd END) AS DeviationEnd
) AS d
UNION ALL
SELECT pb.UserID, d.DeviationStart, d.DeviationEnd
FROM PlannedBreak AS pb
INNER JOIN #ActualHours AS ah ONpb.TimeStart <= ah.TimeEnd
AND ah.TimeStart <= pb.TimeEnd
AND pb.UserID = ah.UserID[/b]
CROSS APPLY
(
SELECT
(CASE WHEN pb.TimeStart < ah.TimeStart THEN ah.TimeStart ELSE pb.TimeStart END) AS DeviationStart,
(CASE WHEN pb.TimeEnd > ah.TimeEnd THEN ah.TimeEnd ELSE pb.TimeEnd END) AS DeviationEnd
) AS d
)
SELECT UserID, SUM(DATEDIFF(Minute, DeviationStart, DeviationEnd)) AS TotalDeviation
FROM Deviations
GROUP BY UserID
DROP TABLE #ActualHours
DROP TABLE #PlannedHours
Thanks a ton Drew!
October 7, 2011 at 11:33 am
I do not have a great depth of knowledge of SQL, but couldn't you simplify this by adding the total minutes planned to work for a day and compare them to the total minutes actually worked in the day for the difference? You could do that by person or over the entire group.
October 7, 2011 at 11:35 am
barblm (10/7/2011)
I do not have a great depth of knowledge of SQL, but couldn't you simplify this by adding the total minutes planned to work for a day and compare them to the total minutes actually worked in the day for the difference? You could do that by person or over the entire group.
That is exactly what I was going to do initially but he wanted it more granular than that. He wanted to amount of time deviance from the scheduled work blocks.
_______________________________________________________________
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 7, 2011 at 11:35 am
Oh, I see that it would not be correct as it would not add the total differences whether plus or minus.
October 7, 2011 at 11:42 am
Indeed - it's for some special calculations at the company I'm working at.
In the end I got the system working, using a similar query to the one that I posted above, based off Drew's version.
So thanks for the help everyone!
October 7, 2011 at 7:02 pm
Interesting problem, and thank you for sharing the discussion and solution. I do note that the query will ignore hours worked outside the scheduled times. For instance, if you have user 1 come back to work a half hour from 17:00--17:30, that 30 minutes is not accumulated to the total.
INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd], [Type])
VALUES (1, '2011-09-27 17:00:00', '2011-09-27 17:30:00', 1) -- another 30 min discrepancy
Running the query after this insert still returns 62 for user 1 rather than 92.
I think you've got a good handle on this, so won't try to code up the approach that came to mind when I saw the initial post. That would have been to pivot out the work start, work end, sched start and sched end times as individual events in a single set, then use the infamous "quirky update" method to run through them, accumulating deviation times as I went. Probably more complicated and almost certainly a less intuitive approach that the final one here.
---edit: hit send too quick....
October 11, 2011 at 6:10 am
Actually John, that was a fortunate coincidence, since it was actually part of the requirements to ignore any extra hours at the start or end of the day. I was going to just find a solution and then implement that as a side fix, but didn't need to as a result 😛
And yeah the "quirky update" approach was my first thought as well, but this one works out very nicely. The only small concern I have is how it will scale as the data sizes increase, but I think it should be fine.
October 13, 2011 at 2:39 am
Hello,
you know what timetable is planned ad what timetable is worked. You want to know the amount of time planned but not worked or worked but not planned, right?, you can obtain it if you calculate the intersection between planned and worked.
So I tried to obtain this intersection. If you have an planned interval P and an actual interval A, they doesn't intersect if P.TimeEnd < A.TimeStart OR A.TimeEnd < P.TimeStart; so they intersect when matching the opposite condition.
My query to obtain that intersection is
SELECT P.UserID
, CASE WHEN A.Timestart > P.TimeStart THEN A.Timestart ELSE P.Timestart END
, CASE WHEN A.TimeEnd < P.TimeEnd THEN A.TimeEnd ELSE P.TimeEnd END
FROM #PlannedHours P
INNER JOIN #ActualHours A ON A.UserID = P.UserID
AND A.TimeStart <= P.TimeEnd AND P.TimeStart <= A.TimeEnd
WHERE Type = 1
and I obtain these figures:
Planned minutes, 420
Worked minutes, 417
Intersection minutes, 392
and, from these,
minutes planned but not worked, 28
minutes worked but not planned, 25
for your total of 53.
Hope that helps,
Francesc
November 16, 2011 at 7:29 am
Hi kramaswamy
I am not so sure what this would help you but my simple suggestion and straight forward suggestion
steps
1.is to create 2 temp tables
3.select the query such as
select datediff(mm,starttime,endtime) from table where type = 1
and similar query for
select datediff(mm,starttime,endtime) from table where type = 2
3.insert the data for each type say type 1 in temp1 table and say type2 data in temp2 table
4.and finaly sum the total record of each table
and hence your done with your task.
Or
one more suggestion if you would can alter into table add a column called total min keep that column as compute column which keep the data for difference of each type and finally sum them usgin group by.
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply