That did it.

Final code took this shape I di d use your function.

WITH ALevel (ConsultantID, AchieveLevel, AchieveTitle, PeriodEnddate) AS

(

SELECT ConsultantID, max(AchieveLevel), AchieveTitle, PeriodEndDate AS DirectReports

...

That's the issue. I can do it if I just wanted distinct AchieveLevels but that is not the case. I need one record for each change in AchieveLevel.

The consultantID is the unique identifier.

Anybody have any thoughts on this? I am really stuck!

There seems to be something missing:

selectachievetitle

,achieveLevel

,max(periodenddate) AS PeriodEndDate

from Volume

where ConsultantID = @ConsultantID

GROUP BY AchieveTitle

,AChievelevel

order by periodenddate

This returns

NAC Consultant01 2001-05-31 00:00:00.000

Consultant 02 2002-04-30 00:00:00.000

Team Manager...

That last one was the ticket. Thanks.

This is really close:

select achievetitle,achieveLevel, max(periodenddate)

from Volume where ConsultantID = '0000288'

GROUP BY AchieveTitle, AChievelevel

But it needs to appear in PeriodEndate order but when I put a group by on with...

I think I solved my issue: Final code:

SELECT A.ConsultantID

,A.EffectiveDate

,A.FirstName + ' ' + A.LastName as ConsultantName

,A.BillToFirstName + ' ' + A.BillToLastName as BillToName

,BillToAddressLine1

,BillToAddressLine2

,BillToCity

,BillToState

,BillToZip

,BillToPhone

,A.ShiptoFirstName + ' ' + A.ShipToLastName as ShipToName

...

This is the code that I am currently working with.

DECLARE@ConsultantIDASnVarChar(50)

,@PeriodDateASDateTime

SET @ConsultantID = '0000344'

SET @PeriodDate = '2007-12-03 11:26:44.263'

SELECT A.ConsultantID

,A.EffectiveDate

,A.FirstName + ' ' + A.LastName AS ConsultantName

,A.SponsorID

,(SELECT DISTINCT ...

part of the issue is that the consultant table has multiple records for a given consultant (it's not a one to one). Also we are passing in the consultantID.

You are so correct, I looked at the query plan and found I was missing few indexes, which I appl;ied and now its a speedy proc.

Thanks

It would be along these lines:

ConID PeriodEndDate ...

I think that will work for the initial view which I just need to indicate that a person has had a least one re-promote in their history. The trick will...

I was just told that the criteria changed:

It's only to be flagged if they go to a lower level then back up to the same level so in this case...

Thank you, that was it. You nailed it. Thank you:D