--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#TeamSales','U') IS NOT NULL

DROP TABLE #TeamSales

--===== Create...

What would be the best way to get you some sample data?

For this particular COnsultant the amount should be the same because the AMount never changes regardless of the Achievelevel in which they are 60,70, and 80.

Thanks guys, I read about NULL and used your suggestion and it worked.

I did a join to the temp table to see if that would get rid of the SubQuery issue but now I get duplicate records:

SELECT v.ConsultantID

,SUM(PurchaseAmount) AS PORSalesTotal

,'PORSalesPoints'...

With a little more input from BA I found myself going at this backward. In some cases I would be returning more than one row which leads to an issue...

I see the the issue, the achievelevel changes so how can I go about suppressing the second row?

I added the MIN as you suggested and it gives me a single record but none of the Case statements seem to work. I also tried it with MAX and...

The first one worked. THanks.

That worked great. The final code was:

UPDATE t

SET points = points + 1

FROM #Temp6 t

INNER JOIN (SELECT ConsultantId

...

Can you give me an example? That sort of confused me.

Any ideas on this?

I have a question on the following code:

Update #tConsultantPoints SET points = points + 1

FROM #tConsultantPoints tCon

INNER JOIN OtherTable ot ON ....

where ot.month = @targetMonth AND...

I have a question on the following code:

Update #tConsultantPoints SET points = points + 1

FROM #tConsultantPoints tCon

INNER JOIN OtherTable ot ON ....

where ot.month = @targetMonth AND lifetimePORSales > 1000...

It is run when ever a consultant wished to see the list. So it's will be pretty sporatic.

This work greats. The final code:

DECLARE @BeginIssueDate Datetime

DECLARE @EndIssueDate DateTime

DECLARE @CertType VARCHAR(50)

DECLARE @CertGRoup VARCHAR(50)

DECLARE @CertStatus VARCHAR(50)

DECLARE @StatusID int

--Exec uspS_CertificateListing '01/01/2008','04/01/2008'

SET @BeginIssueDate = '01/01/2008'

SET @EndIssueDate ='04/01/2008'

SET @CertType = 'Retail'

SET @CertGroup =...