Using just the base test data (w/o the updates)

; WITH CTE AS (

SELECT

t.KitID,

t.SubjID,

t.VISIT,

t.VISITNUM,

t.LBDTC,

ROW_NUMBER() OVER (ORDER BY t.SubjID,t.LBDTC,t.KitID) AS rn1,

ROW_NUMBER() OVER (PARTITION BY t.VISITNUM ORDER BY t.SubjID,t.LBDTC,t.KitID) AS rn2

FROM

#testing t

)

SELECT

c1.KitID,

c1.SubjID,

c1.VISIT,

c1.VISITNUM,

c1.LBDTC,

CASE

WHEN c1.VISITNUM = c2.VISITNUM THEN CAST(ROW_NUMBER() OVER (PARTITION BY c1.VISITNUM + c2.VISITNUM, c1.rn1 - c1.rn2 ORDER BY c1.rn1) AS VARCHAR(5))

ELSE ''

END AS VisitSeq

FROM

CTE c1

LEFT JOIN CTE c2

ON c1.rn1 = c2.rn1 + 1

AND c1.VISITNUM = c2.VISITNUM

ORDER BY

c1.rn1