LukeP LukeP - 5 months ago 9
SQL Question

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

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

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;
Comments