Правильное размещение предложения where для RowNum во вложенных таблицах

Question

У меня есть несколько таблиц, созданных для вставки данных в основную таблицу вместо использования объединения (из-за смещения значений таблицы и, следовательно, я использую вставку в таблицу для переноса значений)

Я создал функцию RowNumber(), которая помечает каждый экземпляр серийных номеров машин, которые не прошли функциональный тест. Это связано с тем, что после переделки машин он все еще может снова выйти из строя, поэтому RowNumber важен для меня, чтобы различать дубликаты.

Для экземпляра серийный номер A не удалось 3 раза, B не удалось 2 раза и C не удалось один раз, поэтому таблица будет выглядеть следующим образом

| Serial Number | Assy Line | Test Stage | Record ID | Row Num | 
|       A       |     1     |    FUNC    |    1      |    1    |
|       A       |     1     |    FUNC    |    2      |    2    |
|       A       |     1     |    FUNC    |    3      |    3    |
|       B       |     1     |    FUNC    |    4      |    1    |
|       B       |     1     |    FUNC    |    5      |    2    |
|       C       |     1     |    FUNC    |    6      |    1    |

Для этой цели меня интересует только RowNum = 1 из-за того, что мне нужно что-то под названием "First Pass Yield"

В приведенном ниже коде требуется "Where / AND FailureTbl = '1'", но я просто не могу точно определить, где. Отсутствие RowNum = 1 приведет к показу RowNum = 2 (что является дубликатом)

Вот весь запрос

DECLARE @TodayDate DATETIME
SET @TodayDate = getDate() -10
DECLARE @DaystartTest DATETIME
SET @DaystartTest = DATEADD(HOUR, -10 , DATEADD(DAY, DATEDIFF(DAY, 0, @TodayDate), 0))
DECLARE @DayendTest DATETIME
SET @DayendTest = DATEADD(DAY, 1 , @DaystartTest)
DECLARE @DaystarteDHR DATETIME
SET @DaystarteDHR = DATEADD(DAY, DATEDIFF(DAY, 0, @TodayDate), 0)
DECLARE @DayendeDHR DATETIME
SET @DayendeDHR = DATEADD(DAY, 1, @DaystarteDHR)


--Optional: Define start and end datetime for the query. Uncomment and modify the code below if you would like to define them. 
--If the section below is not uncommented, the query will be based on today.
/*
SET @DayendeDHR = YYYY-MM-DD HH:MI:SS
SET @DayendTest = DATEADD(HOUR, -10 , @DayendeDHR)
*/


--Declare a temporary table called @FailureTbl to store query result
DECLARE @FailureTbl TABLE
(
    --Serial Number
    SerialNumber VARCHAR(11),
    --Assembly Line
    AssyLine VARCHAR(10),
    --Test Stage
    TestStage VARCHAR(11),
    --Record ID
    RecordID INT IDENTITY (1,1) PRIMARY KEY,
    RowNum Int
)

--Insert test failures into the @FailureTbl TABLE
Insert into @FailureTbl(SerialNumber, TestStage, RowNum)
SELECT 
    --Serial number
    TestResult.UnitIdentifier,
    --Test stage
    TestResult.TestStage,
    Row_Number() OVER (Partition by TestResult.UnitIdentifier Order by TestResult.UnitIdentifier) as RowNum

--Select database on SQL Server
FROM [MSPTestResult].[MSPTestResult].[Result] AS TestResult
WHERE 
    ------------------------------------------------------------------------------------
    --Define datetime of the query

    --Define the start datetime of the query
    TestResult.CreatedOn > @DaystarteDHR AND 
    --Define the end datetime of the query
    TestResult.CreatedOn < @DayendeDHR AND

    ------------------------------------------------------------------------------------
    --Define work order information

    --Only get test results for Sydney (105 is Sydney, 327 is Singapore)
    TestResult.SiteID = '105' AND
    --Define product code of the query (AirMini starts FROM 38)
    TestResult.ProductCode LIKE ('38%') AND
    --Define work order number pattern
    TestResult.WorkOrderBatch LIKE '1%' AND
    --Define serial number pattern (serial number starts FROM 22 or 23)
    (TestResult.UnitIdentifier LIKE '22%' OR TestResult.UnitIdentifier LIKE '23%') AND

    ------------------------------------------------------------------------------------
    --Define test information

    --Only get data FROM test station (excluding rework station)
    TestResult.TestStationType = 'TestStation' AND
    --Only get test failures (0 stANDs for test failed, 1 stAND for test passed)
    TestResult.IsTestPass = '0' AND
    --Only get test results for Active Customisation, Functional Test, Safety Test
    TestResult.TestStage IN ('TS_CUSTOM','TS_FUNC','TS_SAFE')



--Insert line rejects into the @FailureTbl TABLE
INSERT INTO @FailureTbl(SerialNumber, TestStage, RowNum)
SELECT 
    --Serial number
    MSPEvent.[EventReferenceColumnValue] AS SN,
    --Test stage
    TestStage = 'LINE_REJECT',
    Row_Number() OVER (Partition by MSPEvent.EventReferenceColumnValue Order by MSPEvent.EventReferenceColumnValue) as RowNum

--Select database on SQL Server
FROM
    [MSPWIP].[MSPWIP].[Event] AS MSPEvent
    JOIN [MSPWIP].[MSPWIP].[EventType] AS EventType
    ON MSPEvent.[EventTypeID] = EventType.[EventTypeID]
    JOIN [MSPWIP].[MSPWIP].[EventReferenceColumnType] AS Reftype
    ON Reftype.[EventReferenceColumnTypeID] = MSPEvent.[EventReferenceColumnTypeID]
    JOIN [MSPWIP].[MSPWIP].[WorkOrder] AS WO
    ON WO.[WorkOrderNumber] = MSPEvent.[WorkOrderNumber]
    JOIN [MSPWIP].[MSPNCP].[Incident] AS Incidents
    ON MSPEvent.EventReferenceColumnValue = Incidents.SerialNumber

WHERE
    ------------------------------------------------------------------------------------
    --Define datetime of the query

    --Define the start datetime of the query
    MSPEvent.CreatedDate > @DaystarteDHR AND
    --Define the end datetime of the query
    MSPEvent.CreatedDate < @DayendeDHR AND

    ------------------------------------------------------------------------------------
    --Define work order information

    --Only get test results for Sydney (105 is Sydney, 327 is Singapore)
    WO.[OrganisationID] = '105' AND
    --Define work order number pattern
    WO.[WorkOrderNumber] LIKE '1%' AND
    --Define product code of the query (Air10 starts FROM 38)
    WO.ProductCode LIKE '38%' AND
    --Define serial number pattern (serial number starts FROM 22 or 23)
    (MSPEvent.[EventReferenceColumnValue] LIKE '22%' OR MSPEvent.[EventReferenceColumnValue] LIKE '23%') AND

    ------------------------------------------------------------------------------------
    --Define NCP information

    --Only get NCP creation record from the database
    EventType.[Name] LIKE 'NCPCreate' AND
    --Only get Serial number from the NCP database (ignoring component rejects)
    Reftype.[NAME] LIKE 'SerialNumber' AND
    --Only get assembly related rejects
    Incidents.ReasonCode = 'Assembly' AND
    --Only get records from Monaco production line
    MSPEvent.StationName LIKE 'MC%'


----------------------------------------------------------------------------------------
--Update @FailureTbl with Assembly line information

DECLARE @TmpAssyLine VARCHAR(10)
DECLARE @TotalFailRecords INT
DECLARE @Counter1 INT
SELECT @TotalFailRecords = COUNT(*) FROM @FailureTbl
SET @Counter1 = 1

--Loop over the @FailureTbl table
WHILE @Counter1 <= @TotalFailRecords
BEGIN
    SELECT 
        --Get assembly line information based on assembly station name
        @TmpAssyLine = 
        CASE 
            WHEN (MSPEvent.StationName = 'MC.01.C1.A2' OR MSPEvent.StationName = 'MC.01.C2.A2') THEN 'AssyLine 1'
        End

    --Select database on SQL Server and from our @FailureTbl table
    FROM @FailureTbl AS FailureTbl inner JOIN [MSPWIP].[MSPWIP].[Event] AS MSPEvent
    ON FailureTbl.SerialNumber = MSPEvent.EventReferenceColumnValue
    WHERE
        --Define work order number pattern  
        MSPEvent.WorkOrderNumber LIKE '1%' AND
        --Only get records from Monaco production line
        MSPEvent.StationName LIKE 'MC%' AND
        --We check up to 5 days in the past from the MSPEvent table as some units may fail today but assembled 5 days ago
        MSPEvent.CreatedDate > DATEADD(DAY, -5 , @DaystarteDHR) AND
        --Define the end datetime of the query
        MSPEvent.CreatedDate < @DayendeDHR AND
        --We only check assembly station 2 (don't get assembly station 1 records to aovid duplicates)
        right(MSPEvent.StationName, 2) = 'A2' AND
        FailureTbl.RowNum = '1' AND
        FailureTbl.RecordID = @Counter1

    --Update @FailureTbl with assembly line information
    UPDATE @FailureTbl
        SET AssyLine = @TmpAssyLine
    WHERE RecordID = @Counter1

    SET @Counter1 = @Counter1 + 1
End

--Output query result
SELECT *FROM @FailureTbl

Я предполагаю, потому что я реализовал счетчик, который увеличивает идентификатор записи для каждого случая сбоя, но я не уверен.

Спасибо за помощь!