I would check to see that when you're doing an assignment like that, are you returning a single value... Does all the code work as expected?

Normally, I would document this...

Maybe start by telling us what you think is the logic. Not sure of the question, though... One usually starts with a question about what one wants to see...

In Crystal Reports, when adding parameters, it allowed you to easily display a description field with the parameter field value.

For example, I could display the project name with the project...

Since you're kinda new here, I fixed your SQL so that it works...

use tempdb;

go

CREATE TABLE ProviderData (PROVIDER_ID CHAR(10),

PATIENT_ID CHAR(10),

CARE_LEVEL CHAR(10),

BEGIN_DT DATETIME,

END_DT DATETIME);

GO

INSERT INTO ProviderData(Provider_ID, Patient_ID, Care_level, Begin_Dt, End_Dt) VALUES...

Are you doing this in SSRS? Use a Matrix... does the pivot for you.

Use DelimitedSplit8K and grab the 3rd item?

Yes, because otherwise SSRS will see it as a single long text string. If you're passing it to a stored procedure as a parameter value, then you absolutely need...

If you do that, you'll have to parse the values yourself. Try DelimitedSplit8K[/url] and put that in your WHERE clause.

Marie,

That's what I was thinking... It's just easier to do this in steps. Then maybe put them all in a stored procedure, if this has to be done more...

so write it to a temp table and do a series of updates on it.

I tend to validate my design by collecting a list of questions the database has to answer. If I can work through how to design the queries (not necessarily...

REPLACE is your friend. =)

Hope you're okay with ugly...

SELECT ID

, col

, rn

FROM

(

SELECT ID

, Col

, Prefix

, NumPart

, ROW_NUMBER() OVER (PARTITION BY Prefix ORDER BY NumPart DESC) AS rn

FROM (

SELECT ID

, col

, LEFT(col,1) AS Prefix

, CAST(RIGHT(col,2)...

Steve,

See if Kevin Boles has any horror stories that aren't confidential. =)

Use a Tally table and a deliberate cross join?