在SQL查询中查找客户端的最短日期比较列和多行

我有一个问题，就像这样

with CTE AS (
        select 
            CLIENT.client_number AS CLIENT_NUMBER, 
            CAST(CLient.create_date AS DATE)AS CLIENT_CREATE_DATE, 
            CAST(ACCOUNT.account_open AS DATE) AS ACCOUNT_CREATE_DATE 
            from ACCOUNT
LEFT JOIN CLIENT ON CAST (CLIENT.client_number AS NUMERIC) = ACCOUNT.client_number
)
select 
CLIENT_NUMBER,
CLIENT_CREATE_DATE,
ACCOUNT_CREATE_DATE,
'' NEW_CLIENT_DATE
FROM CTE
ORDER BY CLIENT_NUMBER

我得到的结果是:

CLIENT_NUMBER   CLIENT_CREATE_DATE  ACCOUNT_CREATE_DATE     NEW_CLIENT_DATE
5                   1/10/2014           8/21/2001                
7                   1/10/2014           3/24/2010   
7                   1/10/2014           10/31/2011  
13                  1/10/2014           4/5/2012    
16                  1/10/2014           10/16/2003  
16                  1/10/2014           6/14/2022   
16                  1/10/2014           8/24/2011   
17                  1/10/2014           11/12/2014  
17                  1/10/2014           12/10/2014  
18                  1/10/2014           10/23/2003  

如何确定每个客户端的最早日期并将其插入到"NEW_CLIENT_DATE"列？目标是将每个客户端的"CLIENT_CREATE_DATE"与所有关联的"ACCOUNT_CREATE_DATE"值进行比较.如果"CLIENT_CREATE_DATE"是最小的，则将其设置为"NEW_CLIENT_DATE"；否则，将其设置为该客户端的最小"ACCOUNT_CREATE_DATE".我已try 使用CTE并try 分区，但我不确定如何继续.有没有人能指导我或提供解决方案？

输出应如下所示:

CLIENT_NUMBER   CLIENT_CREATE_DATE  ACCOUNT_CREATE_DATE     NEW_CLIENT_DATE
5                   1/10/2014           8/21/2001               8/21/2001
7                   1/10/2014           3/24/2010               3/24/2010
7                   1/10/2014           10/31/2011              3/24/2010
13                  1/10/2014           4/5/2012                4/5/2012
16                  1/10/2014           10/16/2003              10/16/2003
16                  1/10/2014           6/14/2022               10/16/2003
16                  1/10/2014           8/24/2011               10/16/2003
17                  1/10/2014           11/12/2014              1/10/2014
17                  1/10/2014           12/10/2014              1/10/2014
18                  1/10/2014           10/23/2003              10/23/2003