Declare @WorkOrder_Number char(10)
Set @WorkOrder_Number='xr1200'
SELECT Op_ID AS 'Badge', System_ID AS 'Station', Date_Time AS 'Date', Serial_Number AS 'Serial Number',
CASE WHEN System_ID Like '%Label%' THEN WorkOrder_Seq
  ELSE (SELECT WorkOrder_Seq
        FROM ACP_Parts API
        WHERE API.Serial_Number=APO.Serial_Number AND
              System_ID Like '%Label%')
  END AS 'Seq#',
Test_Result AS 'Status Result'
FROM ACP_Parts APO 
WHERE
  Serial_Number IN (
    SELECT Serial_Number FROM ACP_Parts
    WHERE (WorkOrder_Number=@WorkOrder_Number))
ORDER BY Serial_Number, System_ID

DECLARE @WorkOrder_Number VARCHAR(10)

SET @WorkOrder_Number = 'xr1200'

SELECT		apo.Op_ID AS Badge,
		apo.System_ID AS Station,
		apo.Date_Time AS [Date],
		apo.Serial_Number AS [Serial Number],
		doh.WorkOrder_Seq AS [Seq#],
		apo.Test_Result AS [Status Result]
FROM		ACP_Parts AS apo
INNER JOIN	(
			SELECT		Serial_Number,
					MAX(CASE WHEN System_ID LIKE '%Label%' THEN WorkOrder_Seq ELSE NULL END) AS WorkOrder_Seq
			FROM		ACP_Parts
			WHERE		WorkOrder_Number = @WorkOrder_Number
			GROUP BY	Serial_Number
		) AS doh ON doh.Serial_Number = apo.Serial_Number
ORDER BY	apo.Serial_Number,
		apo.System_ID