Looping "poor practice"... what is the alternative? 3

[Veejc]

Hi all

I have been reading lots of posts and in most I am getting the picture that looping is "poor practice"... and that you should build a user defined function.

Can someone post a basic example of how you would accomplish simulated looping in a function?

Here is the "english" of what I am trying to accomplish.

I have a table of distinct values.
I want to "loop" through that table and for every record I want to then query another table to find any related records in there. ( a bunch of related segments) Then from there I need to make some comparisons to some date values that are found in the latter table and return a simple date.

I dont' expect anyone to write this for me, I'm just looking for a basic example of how to handle this "looping" for lack of a better term. I can then pick it apart and build what I need.

Thanks!

 

[tc3596]

Select
field1,
field2,
dbo.functionname(@param1) --or no params

From
table1


in your function return a date value

 

[SQLBill]

You don't need looping. Just JOIN the two tables together on the related record and then apply the filter (WHERE).

Provide an example of your schema and data and we can point you in the right direction.

When we ask for an example or sample of schema/tables/data...we aren't looking for the actual information (it would be nice, but we understand it might be 'proprietary'). So if you can't provide the true information, provide 'cover' information...for example...

Table1
ID PartNO
1 00001

Table2
PartNo Description
00001 Chair


-SQLBill

Posting advice: FAQ481-4875

 

[Veejc]

Here's the basic setup of what I'm dealing with

Member Table - these are distinct values
Member ID 345678
Member ID 393939

There is a history table that captures all history for every member, so each member could have limitless "segments" of enrollment if you will... something like this

Member HIstory
Member ID EnrollmentDate TerminationDate
345678 12/01/2004 01/31/2005
345678 02/01/2004 04/01/2005
345678 04/02/2005

From the distinct Member id value, I want to capture all of the "segments" and perform some comparisons on the date values from one segment to the next. I.e.

If I am looking for the "earliest enrollment date" for a member id that does not have > 90 days for a break in coverage (not > 90 days between the term date from one segment and the enrolldate from the next segment)

That may be too much info? I can elaborate if you need me to, but are you saying that inner joins essentially take the place of looping?

 

[Jasen]

Something like this perhaps:

***************************
select top 1 EnrollmentDate
from (Select History_Table.MemberID
From History_Table
where MemberID = '345678') as v

join Member_Table on Member_Table.MemberID = v.MemberID
join History_table.MemberID = History_Table.MemberID = Member_Table.MemberID

order by EnrollmentDate asc

****************************

No looping. Simply grab all the matching dates and sort them ascending, then only select the top result.
You may have to do some formatting or casting of the date to get it to sort properly.

________
Remember, you're unique... just like everyone else.

 

[Jasen]

oops, sorry, that second join clause should read
like the first one,
join table1.col ON table1.col = table2.col

________
Remember, you're unique... just like everyone else.

 

[Veejc]

Hi Jasen

I am looking to compare dates from one "segment" to another, and return only one date (based on the comparison) for each member record.. so it's not just grabbing the "top X".

If that subquery returns ALL of the records for that member it could be several records that it returns. How would I then basically start with the earliest date out of what was returned and compare fields from one segment to the next and so on until I run out of records for the member and then move on. I completely understand how looping works and that's how I was going to tackle this until I was told that was very poor practice, but I'm fuzzy on how to calculate from one segment to another while still holding all the values for everything without looping.

Does that make sense?

 

[Veejc]

Okay I'll wait just a few more days before I make a solution using a loop. I am not clear on exactly what the alternative to looping would be for my scenario, can anyone shed some light?

 

[johnherman]

This looks like the old continuous enrollment problem for HEDIS reporting and I remember that someone had a solution for it in one of these forums (either SQL Server or Oracle). If I have some time, I'll do a search, otherwise try searching on enrollment in those forums.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[Veejc]

just seeing the keyword HEDIS made my skin crawl.... yech!! but you are correct, it's insurance related

but, thank you VERY Much! I will certainly search on that!

 

[Rousseau10]

OH yes, enrollment date and term date makes my skin cralw too. This is the data I have to deal with too.
Anyhow I have the same inclination to do loop through recordsets to do things and I too have been told the perfomance is better using a query, so if you have time learn to do it with a query so you get in the habit for the long haul, otherwise, just do it how you feel you have the most control over the data.
I have a question for you I usually use access and VBA modules to loop through recordsets, how do you do this in sql server. Can you loop in T-SQL?

I was the first American Soldier to put a basketball hoop up in Iraq, only to have it stolen by a soldier from different camp. Newbee - Adam

 

[miq]

Hi,
Just don't say looping is bad. Sometimes its the only solution but avoid cursors for looping instead use T-sql. more then 90% problems of looping can be done without using cursors.

when you get your looping thing done just post it here and we will try to optimize it. Yes, you can loop through records in T-SQL but need to do it using different techniques because their is no For / While thing in TSQL.

B.R,
miq

 

[tc3596]

there are while loops...

While 1 <= (Select count(*) from table)
Begin
....t-sql statements....
End

 

[miq]

yes,
there is a while loop for the record. But i mean not as efficient / dynamic as they could be in vb secondly, even while loops can be avoided using cobmination of select, colasce, charindex / patindex, temp tables, @@rowcount, Case etc,.

B.R,
miq

 

[Veejc]

Yes, I concur, that the performance is NOT ideal, however I cannot for the life of me determine any other way to do this, because for my purposes, I am not simply doing "stuff" with the values in ONE record, I need to compare one record to the next ... and forgive me, but looping is the only way that I know how.


I am 90% finished with the loop and I do plan to post it here to see if anyone can help me optimize it, thanks for your offer to have a look, and of course if you can help me break it into smaller pieces so I don't have to loop, even better... stay tuned, tomorrow I will post the code...

Thanks again!

 

[dpatrickcollins]

Here you go. Let me know if this is what you were wanting to achieve.

Select Top 1 MH.EnrollmentDate
From
MemberHistory MH,
MemberHistory MH_recent

WHERE
/*must be same member */
MH.MemberID = MH_recent.MemberID and
/*must not be same history record*/
MH.EnrollDate <> MH_recent.EnrollDate and
/*and term date is the most recent*/
MH_recent.TerminationDate IN (
Select Top 1 MH_sub.TerminationDate
From MH_sub
Where
MH_sub.MemberID = MH.MemberID and
MH_sub.EnrollDate <> MH_sub.EnrollDate and
DATEDIFF(day, MH_sub.TerminationDate, MH.EnrollDate) < 90
ORDER BY MH_sub.TerminationDate DESC)

ORDER BY MH.EnrollDate

 

[maswien]

Just for fun, loop is not always bad.

update players
set start_date= h.max_date
from players p inner join
(select member_id, max(start_date) as max_date
from history group by member_id) h
on p.member_id = h.member_id

while @@rowcount > 0
begin
update players
set start_date
=(select min(start_date) as min_date
from history
where datediff(dd, end_date, players.start_date ) < 90
and players.member_id = member_id )
where start_date
<>(select min(start_date) as min_date
from history
where datediff(dd, end_date, players.start_date ) < 90
and players.member_id = member_id )
end

 

[maswien]

ther should be a temporary table:

select member_id, getdate() into #tab from players

update #tab set start_date= h.max_date
 from players p inner join 
 (select member_id, max(start_date) as max_date  
    from history group by member_id) h 
      on p.member_id = h.member_id  

while @@rowcount > 0
begin
update #tab set start_date
   =(select min(start_date) as min_date   
      from history 
     where datediff(dd, end_date, players.start_date ) < 90 
         and players.member_id = member_id )
 where exists 
   (select * from history 
     where datediff(dd, end_date, #tab.start_date ) < 90 
     and datediff(dd, end_date, #tab.start_date ) > 0 )  
end

 

[maswien]

typo

select member_id, getdate() as start_date 
 into #tab from players

update #tab set start_date= h.max_date
 from #tab p inner join 
 (select member_id, max(start_date) as max_date  
    from history group by member_id) h 
      on p.member_id = h.member_id  

while @@rowcount > 0
begin
update #tab set start_date
   =(select min(start_date) as min_date   
      from history 
     where datediff(dd, end_date, #tab.start_date ) < 90 
         and #tab.member_id = member_id )
 where exists 
   (select * from history 
     where datediff(dd, end_date, #tab.start_date ) < 90 
     and datediff(dd, end_date, #tab.start_date ) > 0 )  
end

 

[Veejc]

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