Please see attachment, it shows what the end product is doing which is a report services report. I am going to see if they can redeploy the report and see...

Not quite, if the repromote is the currentlevel then don't display. The script works but it is still not suppressing the repromote is at the current level.

--if the consultant has...

Tried adding the DISTINCT clause and it still doing the same. I am think there needs to be a tie between the AchieveLevel and the latest periodenddate in order to...

Your right it is working but not quite right.

INSERT INTO #C

SELECT a.ConsultantID

,a.AchieveLevel

,a.AchieveTitle

,a.PeriodEndDate

, a.Repflag

FROM Volume a

INNER JOIN (SELECT x.i.value('.', 'VARCHAR(20)') AS [ConsultantID]

FROM @x.nodes('//i') x(i) ) as b

on a.ConsultantID = b.ConsultantID

AND...

Did not work. I think for this clause we need something like

SELECT a.ConsultantID

,a.AchieveLevel

,a.AchieveTitle

,a.PeriodEndDate

, a.Repflag

FROM Volume a

INNER JOIN (SELECT x.i.value('.', 'VARCHAR(20)') AS [ConsultantID]

FROM @x.nodes('//i') x(i) ) as b

on a.ConsultantID =...

I figured out what the issue was, I was calling a old version of the proc. It seems to be working now but this lead to another question. I also...

Same error. THe line, I think, that the error indicates in the message is

SELECT * FROM

(SELECT DISTINCT ConB.ConsultantID

, ConB.AchieveLevel

, ConB.AchieveTitle

, ConB.PeriodEndDate

, ConB.RepFlag

FROM #C ConA

INNER JOIN #C ConB ON...

Some may have repromotes and some may not. When I executed the following query:

EXEC [consultantreports].uspS_DownlineRepromotions2 '0004073|0007039|0017122|0024661|0025122|0032877|0036461|0038602|0040483|0042212|0052266|0053254|0054365|0056485|0056593|0058200|0059601|0060183|0060551|0061561|0061798|0062514|0063969|0065526|0067565|0067785|0067892|0069314|'

This is the result set:

0004073 ...

I get:

0004073|

0007039|

0017122|

0024661|

0025122|

0032877|

0036461|

0038602|

0040483|

0042212|

0052266|

0053254|

0054365|

0056485|

0056593|

0058200|

0059601|

0060183|

0060551|

0061561|

0061798|

0062514|

0063969|

0065526|

0067565|

0067785|

0067892|

0069314|

But when I run this:

DECLARE @ConIDs VARCHAR(MAX)

SET @ConIDs =

(SELECT DISTINCT RTRIM(ConsultantID) + '|'

FROM contemp

FOR XML PATH(''))

--select @conids

EXEC uspS_DownlineRepromotions2 @ConIDs

I get empty recordset back.

I created the string and ran the following command and it works:

EXEC [consultantreports].uspS_DownlineRepromotions2 '0004073|0007039|0017122|0024661|0025122|0032877|0036461|0038602|0040483|0042212|0052266|0053254|0054365|0056485|0056593|0058200|0059601|0060183|0060551|0061561|0061798|0062514|0063969|0065526|0067565|0067785|0067892|0069314|'

I see the problem I think its the site stripping of the XML code in the proc. But rest assured its in the proc.

I must have left it out:

ALTER PROCEDURE [consultantreports].[uspS_DownlineRepromotions2]

@CONSULTANTID VARCHAR(MAX)

AS

SET NOCOUNT ON

DECLARE @x XML

SET @x = ' '

CREATE TABLE #C

(ConsultantID CHAR(20)

,AchieveLevel CHAR(2)

,AchieveTitle CHAR(50)

,PeriodEndDate DATETIME

,RepFlag VarChar(2))

INSERT INTO #C

SELECT a.ConsultantID

,a.AchieveLevel

,a.AchieveTitle

...

Not quite, it works by itself...

I have copied the consultantIDs into a table, how would you suggest that I get them in to the string?