Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RandyHJ
Starting Member
5 Posts |
 Posted - 2012-04-05 : 09:32:16
|
| I have a table with hundreds of thousands of records. Inside this table is a member_id column, an effective_date column, and an end_date column.I had a member recently who had overlapping effective dates and end dates. As a result, multiple rows were being returned and displaying the wrong data in a user control. I solved that problem, but now, because we use source control and need to able to repeat the problem so we can test our push to production, I need to find other members with the same kind of problem.For example,My member had one row with an effective date of 04/01/2011 and an end date of 03/31/2012. She had a second row with an effective date of 1/1/2012 and an end date of 12/31/2012. The query returned both rows up until 4/1/2012 because of an overlap in end dates from 1/1/2012 to 3/31/2012. Now the problem has vanished for her, but is waiting to pop up again for other members with such overlaps.What I need to do, and the reason for this question, is find other members in the table with the same overlapping end date problem. I guess I'm asking for some kind of psuedo-code that will help me in making the task easier of checking through 100,000+ members to find those with two rows with overlapping end dates (i.e. two end dates in the same effective period that are greater than today's date but not equal to each other [ex. eff date 7/01/2012 and eff date 1/1/2012[ex. end date 6/30/2012 and end date 12/31/2012]An entry like this will return two rows until 7/1/2012. I've solved the problem of duplicate rows, what I need to do is find members (by member_id) with the same kind of overlapping end dates. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-05 : 10:20:54
|
just do a check likeSELECT *FROM YourTable tWHERE EXISTS(SELECT 1 FROM YourTable WHERE member_id = t.member_id AND effective_date < t.end_date AND end_date > t.effective_date) to get overlapping records and then apply UPDATE over them to change dates to correct boundary values------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
RandyHJ
Starting Member
5 Posts |
Posted - 2012-04-05 : 10:25:10
|
| Thanks, visakh16, I'm not sure what (SELECT 1 means?? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-05 : 11:41:42
|
| its just a boolean result returned for EXISTS to return true or false based on subquery resultset------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Finding members with overlapping end dates