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.

EXCEPT and INTERSECT were added in SQL2005.

The other way to do it is to use a Matrix in SSRS. You could base your report on a stored procedure that accepts the date range you want...

Grant Fritchey has posted at least a few database maintenance videos on YouTube. Definitely worth watching if you're not really comfortable with backup/restore. Absolutely something to practice at...