April 5, 2012 at 7:48 am
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.
April 5, 2012 at 8:26 am
Hi and welcome to SSC. In order for anybody to begin being able to help you, you first have to help us. You have not provided anywhere near enough information.
Take a quick read of this article. http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
At the very least we need some ddl, sample data and desired output. To find out the best practices on posting this type of information take a look at the first link in my signature.
_______________________________________________________________
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/
April 5, 2012 at 9:42 am
Throwing a wild guess:
-- =========================================== --
-- SETUP --
-- =========================================== --
-- CLEANUP
IF OBJECT_ID('tempdb..#members') IS NOT NULL
DROP TABLE #members;
-- CREATE A TEST TABLE
CREATE TABLE #members (
member_id int NOT NULL,
effective_date date NOT NULL,
end_date date NOT NULL
)
-- INSERT SOME TEST VALUES
INSERT INTO #members VALUES (1, '04/01/2011', '03/31/2012');
INSERT INTO #members VALUES (1, '01/01/2012', '12/31/2012');
INSERT INTO #members VALUES (2, '05/01/2011', '07/31/2012');
INSERT INTO #members VALUES (2, '01/01/2012', '10/31/2012');
INSERT INTO #members VALUES (3, '01/01/2012', '12/31/2012');
INSERT INTO #members VALUES (4, '01/01/2011', '12/31/2011');
INSERT INTO #members VALUES (4, '01/01/2012', '12/31/2012');
-- =========================================== --
-- SOLUTION --
-- =========================================== --
SELECT *
FROM #members AS m1
WHERE EXISTS (
SELECT *
FROM #members AS m2
WHERE m1.member_id = m2.member_id
AND (
m1.effective_date BETWEEN m2.effective_date AND m2.end_date
OR
m1.end_date BETWEEN m2.effective_date AND m2.end_date
)
AND NOT (
m1.effective_date = m2.effective_date
AND m1.end_date = m2.end_date
)
)
-- Gianluca Sartori
April 5, 2012 at 9:47 am
Thanks, I'll look into it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply