December 24, 2013 at 7:25 am
Hey all, I am trying to figure out how I can use SQL to search through a table of employees and their punch times to find any overlapping values.
If you use the code below I've created an example of what I'm trying to find. Records 8 and 9 are from the same employee and location where one record has an end time of 1:35p and the next has a start of 1:32p which gives an overlap of 3 minutes. I'm looking for a way to find these types of overlaps. I was thinking that using a CTE would work but I have so little experience with them.
Can anyone help me out here?
CREATE TABLE dbo.Employees(EmployeeId INT, LocationId INT, ActualStartDate DATETIME, ActualEndDate DATETIME)
INSERT dbo.Employees
SELECT 20347,44,'2013-11-04 10:50:41.463','2013-11-04 10:53:03.953'
UNION ALL
SELECT 20347,44,'2013-11-06 13:39:03.733','2013-11-06 13:43:00.863'
UNION ALL
SELECT 20347,44,'2013-11-06 14:42:03.697','2013-11-06 14:46:00.863'
UNION ALL
SELECT 23658,80,'2013-11-01 11:18:08.767','2013-11-01 11:26:45.523'
UNION ALL
SELECT 23658,80,'2013-11-01 13:18:10.053','2013-11-01 13:26:28.243'
UNION ALL
SELECT 23658,80,'2013-11-01 13:28:28.287','2013-11-01 13:37:02.720'
UNION ALL
SELECT 23658,80,'2013-11-01 14:42:57.433','2013-11-01 14:50:48.683'
UNION ALL
SELECT 23658,80,'2013-11-07 13:15:22.910','2013-11-07 13:35:13.887'
UNION ALL
SELECT 23658,80,'2013-11-07 13:32:00.423','2013-11-07 13:46:42.950'
UNION ALL
SELECT 23658,80,'2013-11-14 11:14:22.380','2013-11-14 11:37:29.940'
December 24, 2013 at 7:49 am
DECLARE @Employees TABLE (EmployeeId INT, LocationId INT, ActualStartDate DATETIME, ActualEndDate DATETIME)
INSERT @Employees
SELECT 20347,44,'2013-11-04 10:50:41.463','2013-11-04 10:53:03.953'
UNION ALL
SELECT 20347,44,'2013-11-06 13:39:03.733','2013-11-06 13:43:00.863'
UNION ALL
SELECT 20347,44,'2013-11-06 14:42:03.697','2013-11-06 14:46:00.863'
UNION ALL
SELECT 23658,80,'2013-11-01 11:18:08.767','2013-11-01 11:26:45.523'
UNION ALL
SELECT 23658,80,'2013-11-01 13:18:10.053','2013-11-01 13:26:28.243'
UNION ALL
SELECT 23658,80,'2013-11-01 13:28:28.287','2013-11-01 13:37:02.720'
UNION ALL
SELECT 23658,80,'2013-11-01 14:42:57.433','2013-11-01 14:50:48.683'
UNION ALL
SELECT 23658,80,'2013-11-07 13:15:22.910','2013-11-07 13:35:13.887'
UNION ALL
SELECT 23658,80,'2013-11-07 13:32:00.423','2013-11-07 13:46:42.950'
UNION ALL
SELECT 23658,80,'2013-11-14 11:14:22.380','2013-11-14 11:37:29.940'
select * from @Employees
select * from @Employees E INNER JOIN @Employees E1
ON E.EmployeeId = e1.EmployeeId
AND E.LocationId = E1.LocationId
where E1.ActualStartDate > E.ActualStartDate AND E1.ActualStartDate < E.ActualEndDate
Regards,
Mitesh OSwal
+918698619998
December 24, 2013 at 6:07 pm
I tend to shy away from self-joins on tables because I've found when they have many rows performance can be problematic.
You might want to try this instead:
SELECT EmployeeId, LocationId, ActualStartDate, ActualEndDate
FROM
(
SELECT EmployeeId, LocationId, ActualStartDate, ActualEndDate
,ActualDate, rn
,rn2=(ROW_NUMBER() OVER (PARTITION BY EmployeeId, LocationId ORDER BY ActualDate)+1)/2
FROM
(
SELECT EmployeeId, LocationId, ActualStartDate, ActualEndDate
,a=ROW_NUMBER() OVER (PARTITION BY EmployeeId, LocationId ORDER BY ActualStartDate)
,b=ROW_NUMBER() OVER (PARTITION BY EmployeeId, LocationId ORDER BY ActualEndDate)
FROM Employees
) a
CROSS APPLY
(
VALUES (ActualStartDate, a),(ActualEndDate, b)
) b (ActualDate, rn)
) a
WHERE rn2<>rn
ORDER BY EmployeeId, LocationId, ActualStartDate;
Oh and by the way, CTEs never "solve problems" on their own, or to put it another way they are not a means to an end (unless you're talking about an rCTE). They are simply a means to improve the readability of the code, e.g., to move a derived table up into a CTE. There are also a few cases where you need to make multiple references to the same derived table, which is more clear if that table is in a CTE.
Edit: If you never seen CROSS APPLY VALUES before, you can read about it in the first article in my signature links.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply