March 16, 2015 at 6:57 am
Hello, I have a table with EmployeeID, StartDate, and EndDate with a PK of EmployeeID, StartDate. How can I check to see that there's no overlap for StartDate and EndDate for a given employee? That is, on any given day there must only be 1 row for an employee where Getdate() is Between StartDate and EndDate. For an active employee their EndDate is set to 06/06/2079.
I've tried it using Row_Number() with Over() but am returning too many rows indicating overlap when none exists.
TIA,
Ken
March 16, 2015 at 7:08 am
Without sample DDL or test data, I've had to 'make something up'. This may or may not work as a result 🙂
select *
from employee e1
where exists ( select 1
from employee e2
where e1.EmployeeId = e2.EmployeeId
and e1.StartDate <> e2.StartDate
and e2.EndDate > e1.StartDate
and e2.EndDate <= e1.EndDate )
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 16, 2015 at 9:04 am
No Phil, this is doing the trick. I'm seeing the overlaps. Thanks!
March 16, 2015 at 9:09 am
Excellent, thanks for posting back.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 16, 2015 at 5:48 pm
Use CONSTRAINTs to prevent overlaps and gaps in the first place:
Self-maintaining, Contiguous Effective Dates in Temporal Tables[/url]
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
March 17, 2015 at 6:20 am
dwain.c (3/16/2015)
Use CONSTRAINTs to prevent overlaps and gaps in the first place:Self-maintaining, Contiguous Effective Dates in Temporal Tables[/url]
..maybe gaps are ok. Otherwise, it's unclear why you would ever need to create multiple rows for a single employee.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 17, 2015 at 8:47 am
Phil Parkin (3/17/2015)
dwain.c (3/16/2015)
Use CONSTRAINTs to prevent overlaps and gaps in the first place:Self-maintaining, Contiguous Effective Dates in Temporal Tables[/url]
..maybe gaps are ok. Otherwise, it's unclear why you would ever need to create multiple rows for a single employee.
For history. From 3/1/15 - 3/10/15 employee X worked in department Y. From 3/11/2015 - 6/6/2079 they are working in department Z. I'm not sure about gaps, like if one row had an EndDate = 3/10/15 and their next row had a StartDate of 3/20/15. In our system I think we would create the 3/11/15 - 3/19/15 row; marking the employee inactive or something.
March 17, 2015 at 8:59 am
Then you should read Dwain's article!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply