That was the temporary code to see it all work once, I should have qualified that... Here is the code as it is now, and it updates a temporary table that I will ultimately return to a calling program (CRN actually)
I did already change the keywords to FETCH NEXT and the While @@Fetch_status doesn't appear to work
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dt_earliest_effective_date
@num_of_days int
AS
------------------------------------------------------------------------------------------------------------------------------------
--- CONTINUOUS ENROLLMENT
--- Written by: Dottie Kennedy-Brooks
--- Written on: 4/26/05
--- What it does: We have a need to determine the continuous enrollment date of a member. The organization refers to this
--- as the continous enrollment date, but really it's the members earliest effective date without a lapse in coverage.
--- The organization does not have a clear cut business rule to determine what number of days is allowable to
--- be considered "continuous" and there are several different numbers being used for different purposes, so in
--- order to try to be consistent at least with HOW we are getting at this information, this procedure was created
--- so everyone can use this in determining "continuous enrollment"
--- if the member has several segments to their history and we are looking for the earliest effective date
--- that we have for that member without a break in coverage of > than whatever is used for @num_of_days
--- Parameters: num_of_days - the number of days that is to be used to determine the continuous enrollment
---
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)
declare @row_count int -- holds the number of members and this is used in the outer loop to det. # times to loop
declare @cursor_cnt int
-- FYI re: the row_count variable
--@row_count may seem a strange way to loop when you can use the @@Fetch_status, however I tried the fetch status
-- and when I use it in both loops (outer and inner), the inner loop is no longer seen at one point in the loop and fetch status
-- does not really qualify which cursor to look at so I believe it is looking at the cursor that is no longer and thus returning
-- the value that returns the -1 which means to stop looping, so while this seems an odd way to handle this, it was the only
-- way I could find at the time
create table #temp_earliest_eff_date (member varchar(12),
persno varchar(2),
memberid varchar(14),
earliest_eff datetime)
--set @days = @num_of_days
set @days = 90 -- for testing
set @cursor_cnt = 1
-- 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
set @row_count = (select count( absubno)
from nhpri_diam_ds01.dbo.tbl_CurrentMemberDimension
where eligibility_flag = 1 )
print @row_count
DECLARE members_cursor SCROLL CURSOR
FOR select distinct absubno, abpersno
from nhpri_diam_ds01.dbo.tbl_CurrentMemberDimension
where eligibility_flag = 1
OPEN members_cursor
--while @@Fetch_status = 0
while @cursor_cnt < (@row_count + 1)
--set @cursor_cnt= (@cursor_cnt + 1)
--print @cursor_cnt
begin
FETCH next FROM members_cursor into @memberid, @persno
set @cursor_cnt= (@cursor_cnt + 1)
----------------------------------------------------------------------------------------------
--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
open member_segments
fetch first from member_segments into @s_effdate, @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"
----------------------------------------------------------------------------------------------------------------------------------
-- 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
set @least_date = convert(datetime,left(@s_effdate,4) + '/' + substring(@s_effdate,5,2) + '/' + substring(@s_effdate,7,2) )
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
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) )
end
-- 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
-- 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
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 -- this goes with the member_segment loop
insert into #temp_earliest_eff_date (member, persno, memberid, earliest_eff)
values (@memberid, @persno, ltrim(rtrim(@memberid)) + '-' + ltrim(rtrim(@persno)), @least_date)
set @least_date = null
set @s_effdate = ''
set @s_termdate = ''
set @s_next_effdate = ''
set @s_next_termdate = ''
set @termdate = Null
set @effdate = Null
set @next_effdate = null
set @next_termdate = null
close member_segments
end -- this goes with the members_cursor loop
deallocate member_segments
select * from #temp_earliest_eff_date
---- close everything / destroy the cursor
--close member_segments
close members_cursor
deallocate members_cursor
drop table #temp_earliest_eff_date
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO