November 12, 2008 at 9:22 am
Morning gang,
I'm trying to find members who were enrolled in a continuous manner, only one gap in enrollment is allowed and that gap has to be < 45 days long.
I'm good with the results, but I was trying to combine the two updates that are present here into one, just for fun and testing what I can do, and don't know if I'm just missing something or if it isn't possible. The second update is necessary (I think) because the member's last enrollment line has no next enrollment to compare to.
Can it be done, or should I just stick with what I have?
Thanks,
Jon
-- ========= find member's enrollment segments ===============
if object_id('tempdb..#memberEnrollments') IS NOT NULL BEGIN DROP TABLE #memberEnrollments END
CREATE TABLE #memberEnrollments (enrollment int identity(1,1),
memid varchar(50),
enrollid varchar(50),
effdate datetime,
termdate datetime,
[Age Category] varchar(15),
continuous varchar(5))
-- the commented out INSERT below creates the following data (de-identified)
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID1','enrollid1','1/1/2006','1/31/2006','25 - 29','False')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID2','enrollid1','1/2/2007','1/31/2007','01 - 04','False')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID2','enrollid2','2/1/2007','4/30/2007','01 - 04','False')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID2','enrollid3','5/1/2007','8/31/2007','01 - 04','False')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID3','enrollid1','1/1/2007','1/31/2007','05 - 10','False')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID3','enrollid2','3/1/2007','3/31/2007','05 - 10','False')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID3','enrollid3','5/1/2007','12/31/2007','05 - 10','False')
/*
INSERT INTO #memberEnrollments (memid, enrollid, effdate, termdate, [Age Category], continuous)
SELECT enrollkeys.memid,
enrollkeys.enrollid,
enrollkeys.effdate,
enrollkeys.termdate,
CASE WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END <1
THEN '< 1'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 5
THEN '01 - 04'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 10
THEN '05 - 09'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 15
THEN '10 - 14'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 18
THEN '15 - 17'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 20
THEN '18 - 19'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 25
THEN '20 - 24'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 30
THEN '25 - 29'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 35
THEN '30 - 34'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 40
THEN '35 - 39'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 45
THEN '40 - 44'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 50
THEN '45 - 49'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 55
THEN '50 - 54'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 60
THEN '55 - 59'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 65
THEN '60 - 64'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 70
THEN '65 - 69'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 75
THEN '70 - 74'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 80
THEN '75 - 79'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 85
THEN '80 - 84'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END < 90
THEN '85 - 89'
WHEN
CASE WHEN DATEADD(YY, DATEDIFF (YY, MEMBER.DOB, GETDATE()), MEMBER.DOB) > GETDATE()
THEN DATEDIFF (YY, MEMBER.DOB, GETDATE()) - 1
ELSE DATEDIFF (YY, MEMBER.DOB, GETDATE())
END >=90
THEN '90+'
END, -- member's age category
'False' -- set all continuous fields to False first
FROM enrollkeys
JOIN member ON enrollkeys.memid = member.memid AND member.sex = 'f'
WHERE enrollkeys.memid IN( '','') -- test member
AND enrollkeys.segtype = 'INT'
GROUP BY enrollkeys.memid,
enrollkeys.enrollid,
enrollkeys.effdate,
enrollkeys.termdate,
member.dob
-- this is so we can compare them in order below
ORDER BY enrollkeys.memid, enrollkeys.effdate
*/
-- display output
SELECT * FROM #memberEnrollments
-- ========== find the number of days they were disenrolled between segments ====
-- ******NOTE ************ This part isn't necessary for the script, so it got commented out,
-- but it allows us to see how the calculation is working
--Compares the termdate of enrollid1 to the effdate of enrollid2
--for the test data, it only compares two rows - memid1 only has one enrollment (nothing to compare to)
--and memid2 only needs to compare enrollid1 to enrollid2, then enrollid2 to enrollid3
--enrollid3 is not compared to anything because it is the last enrollment (enrollment <> enrollment + 1)
-- SELECT enrollkeys1.memid,
-- enrollkeys1.enrollid,
-- enrollkeys2.enrollid,
-- enrollkeys1.termdate,
-- enrollkeys2.effdate,
-- datediff(dd,enrollkeys1.termdate,enrollkeys2.effdate) AS [Disenrolled Days]
-- FROM #memberEnrollments enrollkeys1
-- JOIN #memberEnrollments enrollkeys2 ON enrollkeys1.memid = enrollkeys2.memid
-- -- make sure we're comparing the enrollment segments in order
-- WHERE enrollkeys2.enrollment = enrollkeys1.enrollment + 1
-- AND enrollkeys1.enrollid <> enrollkeys2.enrollid
-- AND datediff(dd,enrollkeys1.termdate,enrollkeys2.effdate) > 1
-- ========== instead of the data above, return a true/false value to the [Continuously Enrolled] column if they were disenrolled 45 days or not ======
-- update first through next to last enrollments
UPDATE #memberEnrollments
SET continuous = 'True '
FROM #memberEnrollments
JOIN #memberEnrollments enrollkeys2 ON #memberEnrollments.memid = enrollkeys2.memid
LEFT JOIN (
SELECT gaps.memid, count(gaps.memid) AS gapCount FROM (
SELECT enrollkeys1.memid,
enrollkeys1.enrollid AS lastEnrollID,
enrollkeys2.enrollid AS nextEnrollID,
enrollkeys1.termdate,
enrollkeys2.effdate,
datediff(dd,enrollkeys1.termdate,enrollkeys2.effdate) AS [Disenrolled Days]
FROM #memberEnrollments enrollkeys1
JOIN #memberEnrollments enrollkeys2 ON enrollkeys1.memid = enrollkeys2.memid
-- make sure we're comparing the enrollment segments in order
WHERE enrollkeys2.enrollment = enrollkeys1.enrollment + 1
AND enrollkeys1.enrollid <> enrollkeys2.enrollid
AND datediff(dd,enrollkeys1.termdate,enrollkeys2.effdate) > 1
) gaps
GROUP BY gaps.memid
) memberGaps ON memberGaps.memid = #memberEnrollments.memid
WHERE (datediff(dd,#memberEnrollments.termdate,enrollkeys2.effdate)< 45
-- make sure we're comparing the enrollment segments in order
AND enrollkeys2.enrollment = #memberEnrollments.enrollment + 1
AND #memberEnrollments.enrollid <> enrollkeys2.enrollid)
AND (memberGaps.gapCount = 1 OR memberGaps.gapCount IS NULL)
-- update the last enrollment for each member without multiple gaps to True (nothing to compare to)
UPDATE #memberEnrollments
SET continuous = 'True '
FROM #memberEnrollments
JOIN (SELECT memid, max(enrollment) maxE FROM #memberEnrollments GROUP BY memid) result
ON #memberEnrollments.enrollment = result.maxE
LEFT JOIN (
SELECT gaps.memid, count(gaps.memid) AS gapCount FROM (
SELECT enrollkeys1.memid,
enrollkeys1.enrollid AS lastEnrollID,
enrollkeys2.enrollid AS nextEnrollID,
enrollkeys1.termdate,
enrollkeys2.effdate,
datediff(dd,enrollkeys1.termdate,enrollkeys2.effdate) AS [Disenrolled Days]
FROM #memberEnrollments enrollkeys1
JOIN #memberEnrollments enrollkeys2 ON enrollkeys1.memid = enrollkeys2.memid
-- make sure we're comparing the enrollment segments in order
WHERE enrollkeys2.enrollment = enrollkeys1.enrollment + 1
AND enrollkeys1.enrollid <> enrollkeys2.enrollid
AND datediff(dd,enrollkeys1.termdate,enrollkeys2.effdate) > 1
) gaps
GROUP BY gaps.memid
) memberGaps ON memberGaps.memid = #memberEnrollments.memid
WHERE memberGaps.gapCount = 1 OR memberGaps.gapCount IS NULL
-- display output
SELECT * FROM #memberEnrollments
-- ================= now return distinct individuals that were continuously enrolled ===========
SELECT DISTINCT #memberEnrollments.memid
FROM #memberEnrollments
WHERE memid NOT IN (SELECT DISTINCT memid FROM #memberEnrollments WHERE continuous = 'False')
SELECT DISTINCT
[Age Category],
COUNT(DISTINCT #memberEnrollments.memid) AS Members
FROM #memberEnrollments
WHERE memid NOT IN (SELECT DISTINCT memid FROM #memberEnrollments WHERE continuous = 'False')
GROUP BY [Age Category]
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 12, 2008 at 11:56 am
I tackled it a bit differently. Example of the running totals[/url] theory applied below. The downside to this method is that you will get rows set to true for their first enrollmentID, even if they're not truly a continuous member. You could of course adjust that with a second update (which you were trying to avoid) to make them all false if any are false, but you don't need that if all you want out of this is to be able to determine continuous members. This approach is also extremely simple to update if your criteria for continuous members changes down the road. It may run a bit faster as well.
SETUP:
[font="Courier New"]
-- ========= find member's enrollment segments ===============
IF OBJECT_ID('tempdb..#memberEnrollments') IS NOT NULL BEGIN DROP TABLE #memberEnrollments END
CREATE TABLE #memberEnrollments (enrollment INT IDENTITY(1,1),
memid VARCHAR(50),
enrollid VARCHAR(50),
effdate DATETIME,
termdate DATETIME,
[Age Category] VARCHAR(15),
PRIMARY KEY CLUSTERED(memid, enrollid),
continuous VARCHAR(5))
-- the commented out INSERT below creates the following data (de-identified)
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID1','enrollid1','1/1/2006','1/31/2006','25 - 29','')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID2','enrollid1','1/2/2007','1/31/2007','01 - 04','')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID2','enrollid2','2/1/2007','4/30/2007','01 - 04','')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID2','enrollid3','5/1/2007','8/31/2007','01 - 04','')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID3','enrollid1','1/1/2007','1/31/2007','05 - 10','')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID3','enrollid2','3/1/2007','3/31/2007','05 - 10','')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID3','enrollid3','5/1/2007','12/31/2007','05 - 10','')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID4','enrollid1','3/1/2007','3/31/2007','05 - 10','')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID4','enrollid2','4/1/2007',NULL,'05 - 10','')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID5','enrollid1','3/1/2007','3/31/2007','05 - 10','')
INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID5','enrollid2','7/1/2007',NULL,'05 - 10','')
[/font]
Solution:
[font="Courier New"]DECLARE @TermDate DATETIME,
@TermCount INT,
@PrevMemID VARCHAR(20),
@PrevTermDate DATETIME,
@Enrollment INT
UPDATE #memberEnrollments
SET @TermDate = CASE WHEN memID = @PrevMemID AND @PrevTermDate IS NOT NULL THEN @PrevTermDate ELSE effDate END,
@TermCount = (SELECT COUNT(DISTINCT termDate) FROM #memberEnrollments WHERE memID = ME.MemID AND termDate IS NOT NULL),
Continuous = CASE WHEN memID = @PrevMemID AND ABS(DATEDIFF(d,effdate,@TermDate)) < 45 AND @TermCount < 2 THEN 'True'
WHEN memID <> @PrevMemID THEN 'True'
ELSE 'False'
END,
@PrevTermDate = termDate,
@PrevMemID = memID,
@Enrollment = Enrollment
FROM #memberEnrollments ME
SELECT DISTINCT MemID
FROM #MemberEnrollments
WHERE Continuous <> 'False'[/font]
November 13, 2008 at 2:33 pm
Wow Seth, thanks. I'll have to puzzle over that for a bit, had to read it a couple of times. Updating a variable before you set it (in the code anyway) is a new one on me, new level of set-based thinking.
I had a vague sense this was leading me to running totals, but hadn't figured out how yet.
I'll let you know if I have questions.
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 17, 2008 at 11:53 am
Seth, finally had a moment to look this one over, and had a couple of questions/comments:
When I run it as posted, memID2 is not captured, but should be.
As I took a look, the assignment of @TermCount is throwing this off,
I modified the SELECT in that assignment slightly so I could pull the data out and look at it, and got this:
SELECT ME.memid, COUNT(DISTINCT #memberEnrollments.termDate)
FROM #memberEnrollments JOIN #memberEnrollments ME ON #memberEnrollments.memid = ME.MemID
WHERE /*memID = ME.MemID
AND */#memberEnrollments.termDate IS NOT NULL
GROUP BY ME.memid
/*
memid
======
memID11
memID23
memID33
memID41
memID51
(5 row(s) affected)
*/
Which then shows me that the @TermCount < 2 in the Continuous assignment is not valid, and this is in fact the only factor that is returning data at all.
For example, if I modify the value of Continuous from Continuous = CASE
WHEN memID = @PrevMemID AND ABS(DATEDIFF(d,effdate,@TermDate)) < 45 AND @TermCount < 2
THEN 'True'
to
Continuous = CASE
WHEN memID = @PrevMemID AND ABS(DATEDIFF(d,effdate,@TermDate)) < 45 AND @TermCount < 4
THEN 'True'
then I see all the members. Changing the days from 45 down to 1, or anything else, has then no effect on discounting members.
I further noted that my join above @TermCount = (SELECT --ME.memid,
COUNT(DISTINCT #memberEnrollments.termDate)
FROM #memberEnrollments
JOIN #memberEnrollments ME ON #memberEnrollments.memid = ME.MemID
WHERE --memID = ME.MemID AND
#memberEnrollments.termDate IS NOT NULL
--GROUP BY ME.memid
), does NOT return anything when I use it, although I don't understand why that is different than your use of memid = ME.memid. Trying to just execute the SELECT the way you wrote it gives me a syntax error, since ME is referenced in the outer query, not the inner. Is there a difference (obviously there is) between the two methods, and why would they return different results?
Anyway, although I like the approach you took, somehow it seems to be completely focused on the number of termdates, and not looking at all at the gap length between records. Any suggestions?
Thanks,
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 17, 2008 at 12:53 pm
My final select query was wrong. It's always the little things! If that's what you were using to check all of your tests, that explains the results. After you run the original update statement against my provided test data, you'll get this:
enrollment memid enrollideffdate termdate Age Categorycontinuous
1memID1enrollid12006-01-01 00:00:00.0002006-01-31 00:00:00.00025 - 29True
2memID2enrollid12007-01-02 00:00:00.0002007-01-31 00:00:00.00001 - 04False
3memID2enrollid22007-02-01 00:00:00.0002007-04-30 00:00:00.00001 - 04False
4memID2enrollid32007-05-01 00:00:00.0002007-08-31 00:00:00.00001 - 04False
5memID3enrollid12007-01-01 00:00:00.0002007-01-31 00:00:00.00005 - 10False
6memID3enrollid22007-03-01 00:00:00.0002007-03-31 00:00:00.00005 - 10False
7memID3enrollid32007-05-01 00:00:00.0002007-12-31 00:00:00.00005 - 10False
8memID4enrollid12007-03-01 00:00:00.0002007-03-31 00:00:00.00005 - 10True
9memID4enrollid22007-04-01 00:00:00.000NULL 05 - 10True
10 memID5enrollid12007-03-01 00:00:00.0002007-03-31 00:00:00.00005 - 10True
11 memID5enrollid22007-07-01 00:00:00.000NULL 05 - 10False
You'll notice that for MemID 4, who had 1 gap of 1 day, both are marked as true. However, MemID5, who had one gap of approximately 90 days, the second entry is marked false.
The final select query should have been:
SELECT DISTINCT MemID
FROM #MemberEnrollments
WHERE memid NOT IN (SELECT memid from #memberEnrollments WHERE continuous = 'False')
This will return member ID's 1 and 4 as the only members who have continuous enrollment.
That said, this seems like it's missing some stuff. Should it be evaluating the fact that memID1's termdate was in January 2006 and it's now the end of 2008? That seems like it should be marked as false, but they technically haven't had a "gap" greater than 45 days, because they had no second enrollment. If they were ever to enroll again, they'd THEN be a non-continuous member, but until then, they're marked as continuous.
November 17, 2008 at 1:10 pm
Yes, it is missing stuff, I was considering only 'continuous enrollment' during the timeframe for which we had member experience, (i.e. the member's first enrollment to their last disenrollment, were they continuously enrolled) but found out that I should have been evaluating against the start and end of a calendar year also, so falling off in August would disqualify you.
Anyway, if we assumed that my original understanding were correct, that still doesn't explain how memID2 is not captured? Enrollment was from end of month to beginning of month until they finally disenrolled in August.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 17, 2008 at 1:13 pm
I was counting those as gaps, even though the new enrollment period started the next day. Let me see if I can re-work it to not count those.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply