SELECT d.MPID, le.Legal_Entity_Name
FROM            dbo.PhysicianInfo AS d INNER JOIN
                         dbo.EmplPrimaryStateLog AS es ON d.ID = es.WebId AND GETDATE() BETWEEN es.DateFrom AND es.DateTo INNER JOIN
                         dbo.SiteIntegration AS si ON es.SitePrimary = si.WebCode INNER JOIN
                         dbo.hcm_department AS hd ON si.HCMDepartmentCode = hd.Department_Code INNER JOIN
                         dbo.hcm_legalEntity AS le ON hd.Legal_Entity_Code = le.Legal_Entity_Code
where d.MPID =8