I have two tables that look like this

Account Table

| Account Number | Account Name   |
| -------------- | -------------- |
| 12345          | Bob Jones      |
| 12346          | Jenny Smith    |
| 12347          | Mary Frederick |

Email Table

| Account Number         | Email Type    |Email                  | IsPrimary       |
| ---------------------- | --------------|-----------------------|-----------------|
| 12345                  | WORK          | [email protected]    | 0               |  
| 12345                  | HOME          | [email protected]    | 0               |
| 12345                  | WORK          | [email protected]    | 1               |
| 12345                  | HOME          | [email protected]  | 0               |
| 12346                  | HOME          | [email protected]     | 1               |
| 12346                  | WORK          | [email protected]    | 0               |
| 12346                  | HOME          | [email protected]   | 0               |
| 12347                  | HOME          | [email protected]     | 0               |

I need to end up with results like this:

| Account Number           | Account Name      | Home Email         | Work Email         | Alternate Email      |
| ------------------------ | ----------------- |--------------------|--------------------|----------------------|
| 12345                    | Bob Jones         | [email protected] | [email protected] | [email protected] |
| 12346                    | Jenny Smith       | [email protected]  | [email protected] | [email protected]  |
| 12347                    | Mary Frederick    | [email protected]  |                    |                      |

I have all possible combinations of emails such as multiple work and no home, no primaries at all, multiple primaries... etc.

I need the logic to be:

• Home Email: When type is Home. If there is a primary, choose the first one. If there is no primary, choose the first non-primary home email. Otherwise make it blank
• Work Email: Same as above but for work
• Alternate Email: Any third email on the list. If there are more than 3, choose home over work.

I have tried a ton of things and nothing has worked well. Here is my top attempt -two things don't work about it. The 'top 1' returns a lot of nulls and the code for alternates isn't always mutually exclusive to the home and work email, especially for people that have no primary checked:

select distinct
A01.AccountNumber, 
A01.FirstName,
A01.LastName,
(
    select top 1 
    case
       when (A07.Active = 1 and A07.EmailType = 'HOME' and A07.UseAsPrimary = 1)
        then A07.EmailAddress
       when (A07.Active = 1 and A07.EmailType = 'HOME')
        then A07.EmailAddress
    end 
    from A07_AccountEmails AS A07 
    where A07.AccountNumber = A01.AccountNumber

) as HomeEmail
,(
    select top 1 
    case
       when (A07.Active = 1 and A07.EmailType = 'WORK'  and A07.EmailType is not null)--and A07.UseAsPrimary = 1)
        then A07.EmailAddress
       when (A07.Active = 1 and A07.EmailType = 'WORK')
        then A07.EmailAddress
    end 
    from A07_AccountEmails AS A07 
    where A07.AccountNumber = A01.AccountNumber 

) as WorkEmail
,(
    select top 1 (A07.EmailAddress)
    from A07_AccountEmails A07
    where A07.AccountNumber = A01.AccountNumber
    and A07.Active = 1 and A07.UseAsPrimary = 0
) as AlternateEmailMulti
from A01_AccountMaster A01
left outer join A07_AccountEmails A07 on A07.AccountNumber = A01.AccountNumber
where
A01.[Status] = 'A'

CodePudding user response：

It seems a simple sub-query per email address should do the trick? I have added them in an OUTER APPLY to allow the values to be used twice, once in the select, and once to be excluded from the alternate email column.

declare @Account table (Number int, [Name] varchar(64));
declare @Email table (AccountNumber int, [Type] varchar(4), Email varchar(256), IsPrimary bit);

insert into @Account (Number, [Name])
values
(12345, 'Bob Jones'),
(12346, 'Jenny Smith'),
(12347, 'Mary Frederick');

insert into @Email (AccountNumber, [Type], Email, IsPrimary)
values
(12345, 'WORK', '[email protected]', 0),
(12345, 'HOME', '[email protected]', 0),
(12345, 'WORK', '[email protected]', 1),
(12345, 'HOME', '[email protected]', 0),
(12346, 'HOME', '[email protected]', 1),
(12346, 'WORK', '[email protected]', 0),
(12346, 'HOME', '[email protected]', 0),
(12347, 'HOME', '[email protected]', 0);

select A.Number, A.[Name]
  , E.Home [Home Email]
  , E.Work [Work Email]
  , (
    select top 1 Email
    from @Email
    where AccountNumber = A.Number
    and Email not in (E.Home, E.Work)
    order by case when [Type] = 'HOME' then 1 else 0 end desc
  )
from @Account A
outer apply (
  select 
    (select top 1 Email from @Email where AccountNumber = A.Number and [Type] = 'HOME' order by IsPrimary desc),
    (select top 1 Email from @Email where AccountNumber = A.Number and [Type] = 'WORK' order by IsPrimary desc)
) E (Home, Work);

Returns your desired results:

Note: If you provide your sample data as DDL DML (as I have here) it makes it much easier for people to assist.