SELECT Distinct c.ClientCode, c.ClientId, c.dateofbirth, c.FirstName, c.LastName,
ISNULL(a.AddrType,ca.AddrType) AddrType, 
ISNULL(a.StreetNo,ca.StreetNo) StreetNo, 
ISNULL(a.OnStreet,ca.OnStreet) OnStreet,
ISNULL(a.Unit,ca.Unit) Unit,
ISNULL(a.City,ca.City) City,
ISNULL(a.Phone,ca.Phone) Phone,  
ISNULL(cl.Ldate, 19990101) LDate  
    FROM dbo.AS_Clients AS c  
    LEFT JOIN dbo.CertificationLog AS cl ON cl.ClientId = c.ClientId and cl.LDescription = @description  
    LEFT JOIN dbo.AS_ClientFundingSource AS cfs ON cfs.ClientId = c.ClientId  
    LEFT JOIN dbo.AS_Address AS a ON a.AddrId = c.ClientId  a.AddrType = 'CH'
    LEFT JOIN dbo.AS_Address AS ca ON a.AddrId = c.ClientId  a.AddrType = 'CA'
    WHERE  cfs.FundingSourceId = 11

SELECT Distinct c.ClientCode, c.ClientId, c.dateofbirth, c.FirstName, c.LastName,
ISNULL(a.CHAddrType,a.CAAddrType) AddrType, 
ISNULL(a.CHStreetNo,a.CAStreetNo) StreetNo, 
ISNULL(a.CHOnStreet,a.CAOnStreet) OnStreet,
ISNULL(a.CHUnit,a.CAUnit) Unit,
ISNULL(a.CHCity,a.CACity) City,
ISNULL(a.CHPhone,a.CAPhone) Phone,  
ISNULL(cl.Ldate, 19990101) LDate  
    FROM dbo.AS_Clients AS c  
    LEFT JOIN dbo.CertificationLog AS cl ON cl.ClientId = c.ClientId and cl.LDescription = @description  
    LEFT JOIN dbo.AS_ClientFundingSource AS cfs ON cfs.ClientId = c.ClientId  
    LEFT JOIN 
(SELECT AddrId,
        MAX(CASE WHEN AddrType = 'CH' THEN AddrType ELSE NULL END) AS CHAddrType,
MAX(CASE WHEN AddrType = 'CH' THEN StreetNo ELSE NULL END) AS CHStreetNo,
MAX(CASE WHEN AddrType = 'CH' THEN OnStreet ELSE NULL END) AS CHOnStreet,
MAX(CASE WHEN AddrType = 'CH' THEN Unit ELSE NULL END) AS CHUnit,
MAX(CASE WHEN AddrType = 'CH' THEN City ELSE NULL END) AS CHCity,
MAX(CASE WHEN AddrType = 'CH' THEN Phone ELSE NULL END) AS CHPhone,
MAX(CASE WHEN AddrType = 'CA' THEN AddrType ELSE NULL END) AS CAAddrType,
MAX(CASE WHEN AddrType = 'CA' THEN StreetNo ELSE NULL END) AS CAStreetNo,
MAX(CASE WHEN AddrType = 'CA' THEN OnStreet ELSE NULL END) AS CAOnStreet,
MAX(CASE WHEN AddrType = 'CA' THEN Unit ELSE NULL END) AS CAUnit,
MAX(CASE WHEN AddrType = 'CA' THEN City ELSE NULL END) AS CACity,
MAX(CASE WHEN AddrType = 'CA' THEN Phone ELSE NULL END) AS CAPhone,
FROM dbo.AS_Address
GROUP BY AddrId)
 AS a ON a.AddrId = c.ClientId 
WHERE  (cfs.FundingSourceId = 11 OR cfs.FundingSourceId IS NULL)