Home Finding Minimum Dates for Clients in SQL Query compare columns and multiple rows

Questions

Finding Minimum Dates for Clients in SQL Query compare columns and multiple rows

October 13, 2023

i have a query where like this

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

and the result i get is :

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

How can I determine the earliest date for each client and insert it into the "NEW_CLIENT_DATE" column? The goal is to compare the "CLIENT_CREATE_DATE" with all associated "ACCOUNT_CREATE_DATE" values for each client. If "CLIENT_CREATE_DATE" is the smallest, then set "NEW_CLIENT_DATE" to it; otherwise, set it to the smallest "ACCOUNT_CREATE_DATE" for that client. I’ve attempted to use a CTE and tried partitioning, but I’m unsure how to proceed. Can someone guide me or provide a solution?

Output should be something like:

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

>Solution :

We can use the window functions here along with CASE logic:

SELECT 
    CLIENT_NUMBER,
    CLIENT_CREATE_DATE,
    ACCOUNT_CREATE_DATE,
    CASE WHEN CLIENT_CREATE_DATE < MIN(ACCOUNT_CREATE_DATE) OVER (PARTITION BY CLIENT_NUMBER)
         THEN CLIENT_CREATE_DATE
         ELSE MIN(ACCOUNT_CREATE_DATE) OVER (PARTITION BY CLIENT_NUMBER) END AS NEW_CLIENT_DATE
FROM CTE
ORDER BY CLIENT_NUMBER;