February 18, 2014 at 3:33 pm
I am drawing a blank on this one.
I have an employee activity table that tracks the start and stop of each event throughout the shift.
I want to calculate the time difference between the EndTime and the next StartTime. The EventID is not necessarily in a sequential order (the events are not always 1, 2, 3, 4).
I was thinking of trying to use OVER PARTITION, but not sure if there is a more efficient way. I will be working with roughly 1000 rows of data at a time.
I have included a sample script to build the table with some sample data to work with. See below:
-----------Script to generate simple working table
CREATE TABLE dbo.ActivityTable (EventID int IDENTITY(1,1) NOT NULL, EmployeeID int NOT NULL, StartTime smalldatetime NOT NULL, EndTime smalldatetime NOT NULL)
GO
INSERT INTO dbo.ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1439, CONVERT(smalldatetime,'Jan 23 2014 7:11PM'), CONVERT(smalldatetime,'Jan 23 2014 7:39PM')
INSERT INTO dbo.ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1439, CONVERT(smalldatetime,'Jan 23 2014 7:41PM'), CONVERT(smalldatetime,'Jan 23 2014 7:48PM')
INSERT INTO dbo.ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1440, CONVERT(smalldatetime,'Jan 26 2014 5:55PM'), CONVERT(smalldatetime,'Jan 26 2014 6:43PM')
INSERT INTO dbo.ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1440, CONVERT(smalldatetime,'Jan 27 2014 7:00PM'), CONVERT(smalldatetime,'Jan 27 2014 7:06PM')
INSERT INTO dbo.ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1440, CONVERT(smalldatetime,'Jan 27 2014 9:53PM'), CONVERT(smalldatetime,'Jan 27 2014 10:07PM')
INSERT INTO dbo.ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1440, CONVERT(smalldatetime,'Jan 27 2014 10:46PM'), CONVERT(smalldatetime,'Jan 27 2014 11:00PM')
INSERT INTO dbo.ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1439, CONVERT(smalldatetime,'Jan 30 2014 3:19PM'), CONVERT(smalldatetime,'Jan 30 2014 3:48PM')
INSERT INTO dbo.ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1439, CONVERT(smalldatetime,'Jan 30 2014 3:39PM'), CONVERT(smalldatetime,'Jan 30 2014 3:43PM')
INSERT INTO dbo.ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1439, CONVERT(smalldatetime,'Jan 30 2014 5:19PM'), CONVERT(smalldatetime,'Jan 30 2014 5:44PM')
INSERT INTO dbo.ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1439, CONVERT(smalldatetime,'Jan 30 2014 5:46PM'), CONVERT(smalldatetime,'Jan 30 2014 5:55PM')
February 18, 2014 at 3:47 pm
Just so you know, your data's a hair off, the start times seem a little out of sync (See rn #3 for 1439), but this is the basic gist:
/*
CREATE TABLE #ActivityTable (EventID int IDENTITY(1,1) NOT NULL, EmployeeID int NOT NULL, StartTime smalldatetime NOT NULL, EndTime smalldatetime NOT NULL)
GO
INSERT INTO #ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1439, CONVERT(smalldatetime,'Jan 23 2014 7:11PM'), CONVERT(smalldatetime,'Jan 23 2014 7:39PM')
INSERT INTO #ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1439, CONVERT(smalldatetime,'Jan 23 2014 7:41PM'), CONVERT(smalldatetime,'Jan 23 2014 7:48PM')
INSERT INTO #ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1440, CONVERT(smalldatetime,'Jan 26 2014 5:55PM'), CONVERT(smalldatetime,'Jan 26 2014 6:43PM')
INSERT INTO #ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1440, CONVERT(smalldatetime,'Jan 27 2014 7:00PM'), CONVERT(smalldatetime,'Jan 27 2014 7:06PM')
INSERT INTO #ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1440, CONVERT(smalldatetime,'Jan 27 2014 9:53PM'), CONVERT(smalldatetime,'Jan 27 2014 10:07PM')
INSERT INTO #ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1440, CONVERT(smalldatetime,'Jan 27 2014 10:46PM'), CONVERT(smalldatetime,'Jan 27 2014 11:00PM')
INSERT INTO #ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1439, CONVERT(smalldatetime,'Jan 30 2014 3:19PM'), CONVERT(smalldatetime,'Jan 30 2014 3:48PM')
INSERT INTO #ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1439, CONVERT(smalldatetime,'Jan 30 2014 3:39PM'), CONVERT(smalldatetime,'Jan 30 2014 3:43PM')
INSERT INTO #ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1439, CONVERT(smalldatetime,'Jan 30 2014 5:19PM'), CONVERT(smalldatetime,'Jan 30 2014 5:44PM')
INSERT INTO #ActivityTable(EmployeeID, StartTime, EndTime) SELECT 1439, CONVERT(smalldatetime,'Jan 30 2014 5:46PM'), CONVERT(smalldatetime,'Jan 30 2014 5:55PM')
*/
SELECT * FROM #ActivityTable
ORDER BY EmployeeID, StartTime
; WITH OrderedList AS
(SELECT
ROW_NUMBER() OVER ( PARTITION BY EmployeeID ORDER BY StartTime) AS rn,
*
FROM
#ActivityTable
)
SELECT
PriorRow.rn,
PriorRow.EmployeeID,
PriorRow.EndTime,
NextRow.StartTime,
DATEDIFF( n, PriorRow.EndTime, NextRow.StartTime) AS MinGap
FROM
-- No outer join needed, we care about gaps.
OrderedList AS PriorRow
JOIN
OrderedList AS NextRow
ONPriorRow.EmployeeID = NextRow.EmployeeID
AND PriorRow.rn + 1 = NextRow.rn
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 18, 2014 at 3:54 pm
Not only is this to look for performance metrics, but also to look for data integrity issues.
Thank you for the quick response! I can definitely work through this now. Seems like this will work perfectly.
A thousand thanks!
February 18, 2014 at 4:06 pm
justin.jones 30639 (2/18/2014)
Not only is this to look for performance metrics, but also to look for data integrity issues.Thank you for the quick response! I can definitely work through this now. Seems like this will work perfectly.
A thousand thanks!
My pleasure. Thanks for posting a question with easy to work with sample information and a clear explanation of what you were looking for.
Good luck.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply