ROW_NUMBER() OVER
    (PARTITION BY CHECKSUM(BR_ProviderID, PA_AssignmentType, BR_AddressID, BR_AddressTypeID)
    ORDER BY
    PA_RSAssignmentOrder ASC,
    EffectiveDate ASC,
    TerminationDate DESC,
    PA_ReimbursementScheduleID ASC
) AS RSAssignmentRank

RowID   BR_ProviderID   PA_ReimbursementScheduleID  PA_AssignmentType   BR_AddressID    BR_AddressTypeID    PA_RSAssignmentOrder    BR_AddressTerminationDate   EffectiveDate   TerminationDate RSAssignmentRank
7   D13Z0K6UHH  AJW Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   4/15/2004   12/31/2009  1
10  D13Z0K6UHH  AJW Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   4/15/2004   12/31/2009  2
12  D13Z0K6UHH  AJW Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   4/15/2004   12/31/2009  3
14  D13Z0K6UHH  AJW Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   4/15/2004   12/31/2009  4
268 D13Z0K6UHH  AJW Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   6/1/2007    12/31/2009  37
311 D13Z0K6UHH  H15 Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   1/1/2010    7/31/2010   38
343 D13Z0K6UHH  H15 Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   1/1/2010    7/31/2010   39
421 D13Z0K6UHH  H15 Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   1/1/2010    7/31/2010   60
653 D13Z0K6UHH  CZO Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   8/1/2010    3/14/2011   61
655 D13Z0K6UHH  CZO Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   8/1/2010    3/14/2011   62
730 D13Z0K6UHH  CZO Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   8/1/2010    3/14/2011   81
732 D13Z0K6UHH  CZO Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   8/1/2010    3/14/2011   82
763 D13Z0K6UHH  CZO Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   8/1/2010    3/14/2011   83
775 D2V50Z1NT5  C11 Standard    D2V50UH142  _REI0PVM65  1   1/1/2500    12/28/2009  12/31/2010  1
784 D2V50Z1NT5  C11 Standard    D2V50UH142  _REI0PVM65  2   1/1/2500    1/1/2011    5/17/2011   2
790 D2V50Z1NT5  F11 Standard    D2V50UH142  _REI0PVM65  2   1/1/2500    5/18/2011   9/25/2012   3
794 D2V50Z1NT5  C11 Standard    D2V50UH142  _REI0PVM65  2   1/1/2500    9/26/2012   1/1/2500    4

RowID   BR_ProviderID   PA_ReimbursementScheduleID  PA_AssignmentType   BR_AddressID    BR_AddressTypeID    PA_RSAssignmentOrder    BR_AddressTerminationDate   EffectiveDate   TerminationDate RSAssignmentRank
7   D13Z0K6UHH  AJW Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   4/15/2004   12/31/2009  1
311 D13Z0K6UHH  H15 Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   1/1/2010    7/31/2010   38
653 D13Z0K6UHH  CZO Standard    D1O90UXIBK  _REI0PVM65  2   3/14/2011   8/1/2010    3/14/2011   61
775 D2V50Z1NT5  C11 Standard    D2V50UH142  _REI0PVM65  1   1/1/2500    12/28/2009  12/31/2010  1
784 D2V50Z1NT5  C11 Standard    D2V50UH142  _REI0PVM65  2   1/1/2500    1/1/2011    5/17/2011   2
790 D2V50Z1NT5  F11 Standard    D2V50UH142  _REI0PVM65  2   1/1/2500    5/18/2011   9/25/2012   3
794 D2V50Z1NT5  C11 Standard    D2V50UH142  _REI0PVM65  2   1/1/2500    9/26/2012   1/1/2500    4

WITH
    cteRSFinder (
        RowID,
        BR_ProviderID,
        PA_ReimbursementScheduleID,
        PA_AssignmentType,
        BR_AddressID,
        BR_AddressTypeID,
        PA_AddressID,
        PA_RSAssignmentOrder,
        PA_AddressTypeID,
        BR_AddressTerminationDate,
        EffectiveDate,
        TerminationDate,
        RSAssignmentRank
    )
AS (
    SELECT
        RowID,
        BR_ProviderID,
        PA_ReimbursementScheduleID,
        PA_AssignmentType,
        BR_AddressID,
        BR_AddressTypeID,
        PA_AddressID,
        PA_RSAssignmentOrder,
        PA_AddressTypeID,
        BR_AddressTerminationDate,
        EffectiveDate,
        TerminationDate,
        @RSAssignmentRank

    FROM
        #TempAllPossibleRSAssignments taprsa

    WHERE
        RSAssignmentRank = 1

    UNION ALL

        SELECT TOP 1
            taprsa.RowID,
            taprsa.BR_ProviderID,
            taprsa.PA_ReimbursementScheduleID,
            taprsa.PA_AssignmentType,
            taprsa.BR_AddressID,
            taprsa.BR_AddressTypeID,
            taprsa.PA_AddressID,
            taprsa.PA_RSAssignmentOrder,
            taprsa.PA_AddressTypeID,
            taprsa.BR_AddressTerminationDate,
            taprsa.EffectiveDate,
            taprsa.TerminationDate,
            taprsa.RSAssignmentRank

        FROM
            #TempAllPossibleRSAssignments taprsa

        INNER JOIN cteRSFinder cteLoop
            ON
            taprsa.BR_ProviderID        =   cteLoop.BR_ProviderID       AND
            taprsa.PA_AssignmentType    =   cteLoop.PA_AssignmentType   AND
            taprsa.BR_AddressID         =   cteLoop.BR_AddressID        AND
            taprsa.BR_AddressTypeID     =   cteLoop.BR_AddressTypeID

        WHERE
            taprsa.EffectiveDate = DateAdd( dd, 1, cteLoop.TerminationDate )
            AND taprsa.RSAssignmentRank > cteLoop.RSAssignmentRank
)

INSERT INTO #TempFinalRSAssignments
    SELECT DISTINCT
        cteRSFinder.BR_ProviderID               AS ProviderID,
        cteRSFinder.PA_ReimbursementScheduleID  AS ReimbursementScheduleID,
        cteRSFinder.PA_AssignmentType           AS AssignmentType,
        cteRSFinder.BR_AddressID                AS AddressID,
        cteRSFinder.BR_AddressTypeID            AS AddressTypeID,
        cteRSFinder.EffectiveDate,
        cteRSFinder.TerminationDate,
        GetDate()                               AS MaintenanceDate,
        0                                       AS RuleID,
        cteRSFinder.BR_AddressTerminationDate,
        cteRSFinder.RSAssignmentRank

    FROM
        cteRSFinder