I have this statement but if the ee does not have a middle name, the field comes up null.  How can I correct please to not drop the null records:

DrvName

    = MAX(RTRIM(NameLast) + ', ' + RTRIM(NameFirst) + LEFT(NameMiddle,1) + '.'),

• Changed type Jingyang LiMicrosoft community contributor, Moderator 6 hours 38 minutes ago

Hi,

Try wrap the column with a COALESCE e.g.

DrvName

    = MAX(RTRIM(NameLast) + ', ' + RTRIM(NameFirst) + LEFT(COALESCE(NameMiddle,''),1) + '.'),

• Proposed as answer by Patrick Hurst 6 hours 19 minutes ago

ISNULL(RTRIM(NameLast),'') + ', ' + ISNULL(RTRIM(NameFirst),'') + ISNULL(LEFT(NameMiddle,1) + '.'),'') 

--Or
CONCAT(RTRIM(NameLast) , ', ' , RTRIM(NameFirst) , LEFT(NameMiddle,1) + '.') 

this

= MAX(RTRIM(NameLast) + COALESCE(', ' + RTRIM(NameFirst),'') + COALESCE(LEFT(COALESCE(NameMiddle,''),1) + '.','')),