Hi auto....code without the SalesRepId.......based on previous code provided....still using the temp table.

IF OBJECT_ID('#tempsales') IS NOT NULL DROP TABLE #tempsales

SELECT CustID

, SalesRepID

, SaleDate

, eligibility

INTO #tempsales

FROM Sales

CREATE CLUSTERED INDEX cix_jls ON #tempsales

(CustID,

/*SalesRepID, */

SaleDate) /*required for QU to run correctly*/

/*the quirky update code */

DECLARE @CustID INT

, @SalesRepID INT

, @SaleDate DATETIME

, @eligibility INT

, @eligibilityDate DATETIME

, @SafetyCounter BIGINT

, @LockDays TINYINT

SET @eligibilityDate = (select min(saledate) from #tempsales)

SET @LockDays = 1

SET @SafetyCounter = 1

;WITH cte_tempsales /*Adds a "safety counter" to the rows in the expected processing order which is used as a check in QU*/

AS (

SELECT SafetyCounter = ROW_NUMBER() OVER

(ORDER BY

CustID,

/*SalesRepID, */

SaleDate)

, CustID

/*, SalesRepID*/

, SaleDate

, eligibility

FROM #tempsales

)

UPDATE cte_tempsales

SET @eligibility = eligibility =

CASE /*This CASE does the safety check*/

WHEN SafetyCounter = @SafetyCounter /*Checks sequence of processing*/

THEN CASE

WHEN @CustID = CustID

/*AND @SalesRepID = SalesRepID */

AND SaleDate < DATEADD(dd, @LockDays, @eligibilityDate)

THEN 0 /* ineligible*/

ELSE 1 /* eligible*/

END

ELSE 1/0 /*- force error if QU out of sync*/

END

, @SafetyCounter = @SafetyCounter + 1

, @CustID = CustID

/*, @SalesRepID = SalesRepID*/

, @eligibilityDate =

CASE

WHEN @eligibility = 1

THEN SaleDate

ELSE @eligibilityDate

END

FROM cte_tempsales WITH (TABLOCKX) /* required for QU*/

OPTION (MAXDOP 1)/* required for QU*/

/*results*/

SELECT top 10000 *

FROM #tempsales

order by custid,SaleDate