我有3个表，列如下所示：
事务表（tran\ U table），包含“客户编号”值，如下所示：

CustomerN    Date    Usercode

职员表（staff），包含“（staff's）customer number”和“personid”，如下所示：

CustomerN    PersonID  UserCode

以及按日期列出的员工职务日志（员工历史记录），其中包含员工在职或离职的信息“personid”和“active”作为

PersonID   IsActive     positionname         StartDate   EndDate     UserCode
1          Yes          branch Manager       01.01.2020  01.01.4570  daniel
1          Yes          Sales representative 15.11.2018  31.12.2019  daniel
2          No           Sales associate      01.01.2018  31.05.2020  mary
3          Yes          Intern               01.01.2018  31.12.2019  josh
3          Yes          Sales associate      01.01.2020  28.02.2020  josh
3          Yes          Sales Representative 01.03.2020  01.01.4570  josh

如果进行交易的客户是/曾经是我们的员工（左或活动），我想得到一个包含“isactive”的列值，即“active”对于非工作人员事务处理，为“左”或“空”。
我尝试了下面的代码，但没有成功

select  b.AccountNumber, b.CustomerName ,aa.IsActive , 

(*) (select 
(*)         top 1 aa.positionname 
(*)                 from staff_history 
(*)                     where 
(*)                         b.date between aa.StartDate and aa.EndDate 
(*)                         and (b.username=aa.UserCode)) 
(*)                             as [Title], 
     from tran_table as b 
     left join staff_history as aa  WITH (NOLOCK) ON b.UserName=aa.UserCode 
    left join staff      as ab  WITH (NOLOCK) ON b.AccountNumber=ab.AccountNumber and ab.PersonId=aa.PersonId
    where 
     aa.positionname is not null 
    order by aa.positionname

编辑#1到denstudent:当然，这个代码的结果如下：

CustomerN    Date       Usercode   IsActive

15874        01.01.2020 josh       Yes
8431         05.03.2020 mary       No
55147        07.05.2020 daniel     Yes

这里的问题是，它符合进行交易的员工的情况，而不是客户（是否是员工）。所有用户代码都属于一个职员，因此不存在“null”值。
我预期的结果是：

CustomerN    Date       Usercode   IsActive

15874        01.01.2020 josh       Yes (1)
8431         05.03.2020 mary       NULL (2)
55147        07.05.2020 daniel     No (3)

(1) the customer number 15874 belongs to an active staff
(2) the customer number 8431 belongs to no a former or active staff
(3) the customer number 55147 belongs to a former staff

编辑#2到marc guillot：有很多额外的表和列。我想把它缩短到与这个查询相关的那些。但是，是的，事务处理表中的日期列与staff\u history表匹配，以查看事务处理日期上的人员职务。我已经添加了几个列和代码（带（*）符号行），如果它适合您的话。