SELECT DISTINCT
c.Contract_Number
, o.SKU_Description
, o.Vendor_Flag
, "Evaporator Coil" AS [Product Type]
, "00" & MAX(s.Target_Sequence) AS [Greatest Current Seq]
, "NULL" AS [Current Sequence_Number]
, "00" & (MAX(s.Target_Sequence)+1) AS [New Sequence_Number]
, "NULL" AS [Current Product_ID]
, "1018" AS [New Product_ID]
, "NULL" AS [Current Brand_Code]
, "LNN" AS [New Brand_code]
, "NULL" AS [Current Model_Number]
, b.Model_Number AS [Correct Model_Number]
, "NULL" AS [Current Serial_Number]