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!

 

[dpatrickcollins]

Veejc and Vongrunt,

The original query (mine) would retrieve ALL discontinous enrollment dates aka first segments.

Maswien's query (thanks for the brilliant assist) further limits it to only the FIRST of discontinuous enrollment dates, according to the spec.

 

[Veejc]

I was still working out your first example, I thought I had it but then I realized that it wasn't looking at all segments.

I am having trouble getting that example to look at ALL segments. Either that or most likely I'm having trouble understand how it IS looking at all segments based on what I see in the code. For example.

(this is your code)
select t0.member_id, max(t0.start_date)
from history t0
where not exists
(select * from history t1
where datediff(dd, t1.end_date, t0.start_date ) <= 90
and datediff(dd, t1.end_date, t0.start_date ) > 0
and t0.member_id = t1.member_id
)
group by t0.member_id

I don't understand how this is comparing ALL Segments. In the subquery you have the datediff looking for <=90 days, but that could return several segments in itself, I'm not grasping how that is really comparing one specific record to another, which is what I need to be doing. I'm working on understanding the "english" around what it's doing, can you help me with that? That might clear it up for me.

 

[dpatrickcollins]

Yes. I'll try at least. In English, the query is saying, "Give me all the rows you find (main part of query) that simply have no (NOT EXISTS) prior segment within the past 90 days. That is the definition for "beginning segment" we have chosen (and I believe it holds).

The subquery FYI specifically says, Give me all the rows that occur within 90 days leading up to the above row (selected in main part of query.

In the above, I am referring to my version. Maswien's simply adds to that and says, "Of these, give me the first begin segment for each member.

I believe you are wanting/thinking that we just got to loop through all of those segments, so where are they? But the premise in this example is that we don't. Begin segments simply will not have a prior qualifying (<90) segment.

If you need all the segments IN ADDITION TO the beginning segment (not part of the stated spec, just holler.

 

[Veejc]

Okay!!!!

Now I get it. I couldn't put the english together, but you have helped tremendously! Thank you so much! As a result, here is the code that WORKS FINALLY!!!!!!

select t2.absubno, t2.abpersno, max(t0.abeffdt)
from dbo.tbl_CurrentMemberDimension as t2 inner join dbo.jmeligm0_dat as t0
on t2.absubno = t0.absubno and t2.abpersno = t0.abpersno
where not exists
(select * from dbo.jmeligm0_dat t1
where datediff(dd, t1.abtermdt, t0.abeffdt ) <= 90
and datediff(dd, t1.abtermdt, t0.abeffdt ) > 0
and t2.absubno = t1.absubno and t2.abpersno = t1.abpersno
)
group by t2.absubno, t2.abpersno
order by t2.absubno, t2.abpersno

I had the need to link to another table to find out who the "active members" are, so that's the extra field and extra query....

I can't believe how much you've all helped me, it took a different kind of thinking and I appreciate it very much!!!

 

[vongrunt]

Irrelevant side note: I was confused between "earliest" and MAX() while skipping half of replies. Gotta master the art of reading one of these days.

Elegant. Worth a star or two.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[maswien]

I realized the original post using the wrong word 'earliest' after I wrote my code, it's half reading and half guess, that maybe what you mean 'art of reading'