OK, now that I am venturing into unknown waters, I have the XML proc working but my next question would how do I create the String of consultantIDs? This is...

I have changed the '?' to ' ' and returns a blank recordset.

Sorry you lost me. This is what the SP looks like now:

ALTER PROCEDURE [consultantreports].[uspS_DownlineRepromotions]

@CONSULTANTID NVARCHAR(10)

AS

SET NOCOUNT ON

SET @ConsultantID = @ConsultantID

--Select * from Repromotes WHere ConsultantID = '0002354'

DECLARE

This works fine for a single consultantID but what if I need to loop through multiple consultantIDs, how would that work?

I found that out. I think it working now. I have a couple of dev testing it right now.

I am trying you suggestion but it keeps erroring:

DECLARE@ConsultantIDASnVarChar(50)

SET @ConsultantID = '0002617';

INSERT INTO #Temp

EXEC uspS_Repromotes @COnsultantID

Msg 208, Level 16, State 0, Line 4

Invalid object name '#Temp'.

Upon futher invesitgation I don't think a UDF will work here as a UDF can not perform DMLs statement against a base table. SO I am back to square one.

Thinking about this, is there a way to call a stored procedure from a SELECT Statement?

Something like Select ConsultantID, AChieveLevel,

AchieveTItle, usp_GetRepromote(@ConsultantID), ENdPeriodDate

FROM Volume

or something along those lines?

Thanks everybody for there replies. I trying the CTE version of it and it seems to be working. It has to go throught the Volume table which contains 2.7 million...

THis might help give you an idea of the records that needed to be pulled out (see attachment) the high lighted rows are the rows that need to be returned:

This almost works but not quite, here is another record set:

COnsultantID Level Title ...

SELECT DISTINCT b.ConsultantID,

b.AchieveLevel,

b.AchieveTitle,

b.PeriodEndDate

FROM #Temp a

INNER JOIN #Temp b ON a.ConsultantID = b.ConsultantID

AND a.PaidLevel =b.AchieveLevel AND a.PeriodEndDate < b.PeriodEndDate

ORDER BY b.PeriodEndDate

This returns the entire recordset not the...

SELECT ConsultantID

, Level

, Title

, PeriodEndDate

FROM

( SELECT row_number()...

I adapted your query and it looks like this:

SELECT ConsultantID

, Level

, Title

...

The only issue with this is the Downline (Heirarchical Tree) is updated every 30 minutes and this could be a pretty big impact on performance when trying to refresh the...