There is an issue with your data structure in that there is no guaranteed order of your rows. It's possible that they could come out of the tables like you've described, but it's also possible that they could come out in a different order.

Unless you address that, there would be no way to create a reliable range of rows between PRIMARY_FLAG = 'Y' and PRIMARY_FLAG = 'N'. For all we know, it's possible to have dozens of NULL rows between those two values.

However, With this in mind, here are two options. Both are unreliable since the order out of your tables aren't guaranteed -

CREATE TABLE T1
(
    ID INT
  , NAME VARCHAR(12)
);

CREATE TABLE T2
(
    ID INT
  , ASSET_NUMBER INT
  , PRIMARY_FLAG CHAR(1)
);

INSERT INTO T1
VALUES
(1, 'BASD')
, (1, 'KLJY')
, (2, 'Brush')
, (2, 'RISHI')
, (3, 'Paint');

INSERT INTO T2
VALUES
(1, 5478, 'Y')
, (1, NULL, NULL)
, (2, NULL, 'Y')
, (2, 8956, NULL)
, (2, NULL, 'N')
, (3, 2547, 'Y')
, (3, NULL, NULL);

Original query -

SELECT t1.ID
     , t1.Name
     , t2.Asset_Number
FROM T1
    LEFT JOIN t2
        ON t1.ID = t2.ID
           AND Primary_Flag = 'Y';

Very next row if ASSET_NUMBER is NULL -

SELECT t1.ID
     , t1.Name
     , X.ASSET_NUMBER
FROM t1
    OUTER APPLY
(
    SELECT COALESCE(ASSET_NUMBER, LEAD(ASSET_NUMBER, 1) OVER (PARTITION BY ID ORDER BY ID)) AS ASSET_NUMBER
         , PRIMARY_FLAG
    FROM T2
    WHERE t1.ID = ID
) AS X
WHERE X.PRIMARY_FLAG = 'Y';

First non- NULL value for a given ID after a NULLASSET_NUMBER where PRIMARY_FLAG = 'Y' -

WITH T2_
AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS rn
         , *
    FROM T2
)
SELECT t1.ID
     , t1.Name
     , T3.ASSET_NUMBER
FROM T1
    OUTER APPLY
(
    SELECT TOP 1
           COALESCE(T3.asset_number, T4.ASSET_NUMBER) AS ASSET_NUMBER
    FROM T2_ AS T3
        OUTER APPLY
    (
        SELECT TOP 1
               ASSET_NUMBER
        FROM T2_ AS T4
        WHERE T3.ID = T4.ID
              AND T3.ASSET_NUMBER IS NULL
              AND T3.PRIMARY_FLAG = 'Y'
              AND T4.rn > T3.rn
        ORDER BY rn ASC
    ) AS T4
    WHERE T3.ID = t1.ID
    ORDER BY T3.rn
) AS T3;

fiddle

There is a problem with your data structure... But this is a repetition :)

with
  t1 as (
    select
      id, name
    from
      (VALUES
         (0, 'BASE'),
         (1, 'BASD'),
         (1, 'KLJY'),
         (2, 'Brush'),
         (2, 'RISHI'),
         (3, 'Paint')) as t(id, name)
  ),
  T2 as (
    select
      id, asset_number, primary_flag
    from
      (VALUES
         (1, 5478, 'Y'),
         (1, -1, NULL),
         (2, NULL, 'Y'),
         (2, 8956, NULL),
         (2, NULL, 'N'),
         (3, 2547, 'Y'),
         (3, NULL, NULL) ) as t(id, asset_number, primary_flag)
  ),
  t3 as (
    select
      t1.*,
      t2.asset_number,
      row_number() over(partition by t1.id, t1.name
                        order by t2.primary_flag) rn
    from
      t1
    left join (
      select
        id, asset_number,
        coalesce(primary_flag, 'Z') primary_flag
      from t2
      where
        asset_number is not null and
        (primary_flag is null or
         primary_flag = 'Y')
    ) t2
    on t1.id = t2.id
  )
select *
from t3
where rn = 1
;

+----+-------+--------------+----+
| id | name  | asset_number | rn |
+----+-------+--------------+----+
|  0 | BASE  | null         |  1 |
|  1 | BASD  | 5478         |  1 |
|  1 | KLJY  | 5478         |  1 |
|  2 | Brush | 8956         |  1 |
|  2 | RISHI | 8956         |  1 |
|  3 | Paint | 2547         |  1 |
+----+-------+--------------+----+

db<>fiddle