Check this For better sql conditions

CREATE VIEW [dbo].[vw_irpts_members_import]
AS

Select
dbo.f_auto_eeid(Empl_id) as auto_eeid,
Case When dob is not null then
Case When ssn is Not Null then
case when Isnumeric(Rtrim(REPLACE(ssn,'-','')))=1 then
LEFT(Rtrim(REPLACE(ssn,'-','')),4)+REPLACE(CONVERT(VARCHAR(10), dob, 1), '/', '')
else
case When ssn4 is Not Null then
case when Isnumeric(Rtrim(ssn4))=1 then
Rtrim(ssn4)+REPLACE(CONVERT(VARCHAR(10), dob, 1), '/', '')
END
eND
eND
else
case When ssn4 is Not Null then
case when Isnumeric(Rtrim(ssn4))=1 then
Rtrim(ssn4)+REPLACE(CONVERT(VARCHAR(10), dob, 1), '/', '')
END
eND
end

End
as
'auto_dwid',
Case When ssn is Not Null then
Case when CHARINDEX('-',ssn) >0 then
ssn
End
End
'auto_ssn',
Case When ssn is Not Null then
case when Isnumeric(Rtrim(REPLACE(ssn,'-','')))=1 then
RIGHT(Rtrim(REPLACE(ssn,'-','')),4)
End
End

'auto_ssn4',
Dob as auto_dob,
case
When Relation='Employee' OR Relation='E' then
'E'
When Relation='SPOUSE' OR Relation='S' then
'S'
End
as auto_relation
from tbl_irpts_members






GO