custom aggregation function using OVER while falling back to a default value

Question

Problem

This is a follow up to another question that I asked. But that one was a little to general to be useful. Now, I have new, more specific issue that I am focused on.

I have a table of accounts:

acct_num | ssn       | branch | open_date    |close_date  | 
----------------------------------------------------------| 
0123456  | 123456789 | 01     | 01/01/2000   | NULL       | 
0123457  | 123456789 | 02     | 03/05/2004   | NULL       | 
1234405  | 322145678 | 04     | 04/16/2016   | 05/01/2016 |   

Notice how the ssn 123456789 has two accounts.

I want to create a data set that augments each row in this table with a column named mbr_open_date.

If an ssn has an open account, then mbr_open_date should be the open_date on the account with the earliest open_date and a non-null close_date.

If an ssn does not have an open account, then the mbr_open_date should just fall back to the minimum open_date.

So the result set I am expecting from the above example is:

acct_num | ssn       | branch | open_date    |close_date  | mbr_open_date | 
--------------------------------------------------------------------------|  
0123456  | 123456789 | 01     | 01/01/2000   | NULL       | 01/01/2000    | 
0123457  | 123456789 | 02     | 03/05/2004   | NULL       | 01/01/2000    | 
1234405  | 322145678 | 04     | 04/16/2016   | 05/01/2016 | 04/16/2016    | 

---

Attempted Solutions

The first thing I tried was:

SELECT

  *
  , (
    SELECT MAX(ssn_open)
    FROM (VALUES
      (MIN(CASE WHEN is_open = 1 THEN open_date END) OVER (PARTITION BY ssn)),
      (MIN(open_date) OVER (PARTITION BY ssn))
    ) as candidates(ssn_open)
  ) mbr_open_date

FROM Account

This yeilds the following error:

Windowed functions can only appear in the SELECT or ORDER BY clauses.

So, then I tried this:

SELECT

  *
  , CASE WHEN
    ((MIN(CASE WHEN is_open = 1 THEN open_date END) OVER (PARTITION BY ssn)) as ssn_open) IS NULL
    THEN ssn_open ELSE (MIN(open_date) OVER (PARTITION BY ssn))
  END mbr_open_date

FROM Account

This yields the following error:

Incorrect syntax near the keyword 'as'.

Now, I'm out of ideas.

Can anyone help me out?

Answer 1

Why do you need a subquery? I think COALESCE() captures the logic you want:

SELECT . . .,
       COALESCE(MIN(CASE WHEN is_open = 1 THEN open_date END) OVER (PARTITION BY ssn),
                MIN(open_date) OVER (PARTITION BY ssn)
               ) as mbr_open_date
FROM Account;