WOW, thanks for all the responses! I finished the "loop" and I have not yet looked to see if the previous responses do what I want so forgive me if an alternative has already been posted... here's the code
declare @memberid varchar(12) -- used to hold the member id
declare @persno varchar(2) -- used to hold the person number
declare @s_effdate varchar(8) -- holds the original string value of abeffdt from jmeligmo
declare @s_termdate varchar(8) -- holds the original string value of abtermdt from jmeligmo
declare @effdate datetime -- holds the converted value of effdate
declare @termdate datetime -- holds the converted value of termdate
declare @s_next_effdate varchar(8) -- holds the 'next' string value of abeffdt
declare @s_next_termdate varchar(8) -- holds the 'next' string value of abtermdate
declare @next_effdate datetime -- holds the next converted value of eff date
declare @next_termdate datetime -- holds the next converted value of term date
declare @least_date datetime -- holds the 'continuous enrollment date'
declare @days int -- holds the number of days allowable for continuous enrollment (user defined)
--set @days = @num_of_days
set @days = 90 -- for testing
-- FYI: this cursor will ONLY hold active members per the eligibility flag clause
-- sometimes the member could have a termdate in the most current segment
-- but this will not affect anything because if there is a date there and member is
-- still active then that elig. flag will remain correct so that is the only criteria you
-- need to be concerned with
DECLARE members_cursor SCROLL CURSOR
FOR select distinct absubno, abpersno
from nhpri_diam_ds01.dbo.tbl_CurrentMemberDimension
where eligibility_flag = 1
OPEN members_cursor
FETCH First FROM members_cursor into @memberid, @persno
----------------------------------------------------------------------------------------------
--we have one memberid so grab that member's segments
--I've discovered that it's best to return the dates in their original varchar format because
--if I convert at the time of gathering then if there is a null value, this will crash the statement
--because the value is not convertable to a datetime
-- the order of this cursor needs to be ascending on the abeffdt as you see here in order to
-- work correctly, don't change this or you will be looking at the segments upside down
-----------------------------------------------------------------------------------------------
-- START LOOPING through the member_cursor
-- we have the member cursor to loop through but now
-- we need to loop through the member segments, that's
-- what this cursor is for
declare member_segments scroll cursor
for select abeffdt as effdate,
abtermdt as TERMDaTe
from nhpri_diam_ds01.dbo.jmeligm0_dat
where absubno = @memberid and
abpersno = @persno
order by absubno, abpersno, abeffdt asc
print @memberid + @persno
open member_segments
fetch first from member_segments into @s_effdate, @s_termdate
print 'eff date-' + @s_effdate + ' termdate-' + @s_termdate
-- this will remain the "least date" until we meet the condition of a new effective date, which is dynamic and defined
-- by whatever the user has passed in as number of days acceptable to determine "continuous enrollment"
set @least_date = convert(datetime,left(@s_effdate,4) + '/' + substring(@s_effdate,5,2) + '/' + substring(@s_effdate,7,2) )
----------------------------------------------------------------------------------------------------------------------------------
-- THE CODE IN THE FOLLOWING SECTION IS WHERE WE DETERMINE THE 'CONTINUOUS ENROLLMENT --
----------------------------------------------------------------------------------------------------------------------------------
-- We've pulled in another row so now compare that row's values to the previous to determine if we have a new effective date
-- the value in the abtermdt field is never null, it's just blank when there isn't one there, so is not null is not a valid condition
if (@s_effdate <> '')
begin
set @effdate = convert(datetime,left(@s_effdate,4) + '/' + substring(@s_effdate,5,2) + '/' + substring(@s_effdate,7,2) )
end
if (@s_termdate <> '')
begin
set @termdate = convert(datetime,left(@s_termdate,4) + '/' + substring(@s_termdate,5,2) + '/' + substring(@s_termdate,7,2) )
end
while @@Fetch_status = 0
begin
print 'next e ' + @s_next_effdate + ' next t -' + @s_next_termdate
fetch next from member_segments into @s_next_effdate, @s_next_termdate
if (@s_next_effdate <> '')
begin
set @next_effdate = convert(datetime,left(@s_next_effdate,4) + '/' + substring(@s_next_effdate,5,2) + '/' + substring(@s_next_effdate,7,2) )
print @termdate
print @next_effdate
-- we need to compare the new effective date against the previous termdate before we get the new termdate
if (datediff(day, @termdate, @next_effdate) ) > @days
begin
declare @numdays int
set @numdays = datediff(day, @next_effdate,@termdate)
print @numdays
-- if the condition for the number of days between segments meets what the user passed in
-- then we modify the @least date to hold the new next_effective date as the new least date
set @least_date = @next_effdate
print 'new eff date'
end
else
print 'same eff date'
end
if (@s_next_termdate <> '')
begin
set @termdate = convert(datetime,left(@s_next_termdate,4) + '/' + substring(@s_next_termdate,5,2) + '/' + substring(@s_next_termdate,7,2) )
end
--end begin loop
end
print 'earliest effective date'
print @least_date
close member_segments
close members_cursor
deallocate member_segments
deallocate members_cursor
