okay... I think this fixes the "you can't report symptoms after you're dead" issue...

CREATE VIEW vwEnrolleeFatalCycle

AS

SELECT enrollmentID

, MIN(cycle) AS FatalCycle

FROM Symptom INNER JOIN Data ON Symptom.SymptomID = Data.ID

WHERE Grade =...

Okay, here's the DELETE as a CTE, which is probably the easiest way to do it... (once you read the crib notes)

; WITH CTE_DupeAddr(DupeID, ID, Cid, FName, LName, DOB, City)...

So sue me.

--Mark the records we want to delete, just to be sure we don't do anything terrible.

UPDATE #TableA

SET IsActive = 1

WHERE [SID] IN

(SELECT MIN([SID]) AS GoodID

FROM #TableA

GROUP BY ...

So in theory they could merge two records that don't match? Could you post an example? Explain why you can't do it with either a CTE or a...

Cursor is a bad word around here... careful.

That said, I don't think you need a cursor at all.

CREATE Table #TableA

(

ID int IDENTITY(1,1),

SID INT,

FNAME varchar(50),

LNAMEvarchar(50),

DOB date,

CITYvarchar(50),

IsActive bit

);

GO

INSERT INTO #TableA

VALUES ('245','Smith','John','1/10/1998','Los Angles','0');...

Made a little progress. I added some columns just to understand what's going on (since I'm sort of stumbling around in the Dark Ages). Here's my latest attempt:

DECLARE...

What's the maximum number of cycles? Can they overlap? Presumably you want a datetime on each symptom row?

As best I can remember, protocols run for a non-predetermined period of...

Correct. It's modeling cancer treatment therapy, so sometimes they get fatal complications, and after that you can't test them anymore. (Cheery huh?)

Okay, I re-read SQLKiwi's articles on APPLY. Basically, the "left" side is the set of enrollees to "process", and the right side is the symptoms. Got that part....

Cruel Taskmaster <g>,

I have a dumb question - if I use a non-looping solution, how do I indicate that no more treatment cycles should be added for an enrollee after...

I probably am skipping a class. The challenge I couldn't get around was this one:

the "continue adding treatment cycles (groups of "symptom" records) until either an enrolled patient has...

How about a couple of things -- code we can read, and some sample data? (CREATE TABLE script(s) and INSERTs). And while I'm being demanding, how about an example...

Thanks for the ideas, everybody.

I got it to work, which is good, but I had to resort to <whisper>a dreaded cursor.</whisper> I know, it's a total cheat, but I'm...

SELECT Contact_ID, Student_ID, Contact_txMail

FROM dbo.Contact

where Student_ID in ( select Enroll_status, Student_ID

from dbo.Student_Info

where Enroll_status = 'true');

So you only want a single record from Contact for each StudentID?

SELECT Contact_ID, Student_ID,...

INTERSECT should be equivalent to INNER JOIN, as any non-matching values fall out of the result.