Overriding column value in SELECT statement based on related table having certain data or not

Question

Given that I have a read-only table Account:

ID	Type
1	Customer
2	Customer
3	Customer
4	Customer

And another RELATED read-only table Policy:

RelatedAccountID	Status
1	Active
1	Expired
3	Active
4	Expired
4	Renewed

I want to return a result where the Type column in Account functions as follows:

• If an account has expired policies with NO active policies, the type should be set to "Previous Customer"
• If an account has zero related policies, the Type should be set to "Prospect"
• Else, Type will not be overridden and should be returned as-is

So the expected values should for the Account table should look like:

ID	Type
1	Customer
2	Prospect
3	Customer
4	Previous Customer

I'm struggling to find the right combo of JOIN, subqueries, CASE statements, etc. Is this possible to do with a SELECT statement, or maybe some temp tables are needed?

Answer 1

You can do it with a single update command, dissecting your different cases with case-when:

update Account
set Account.Type = 
case
    when not exists (select 1 from Policy where Account.ID = Policy.RelatedAccountID) then 'Prospect'
    when not exists (select 1 from Policy where Account.ID = Policy.RelatedAccountID and Policy.Status = 'Active') and exists (select 1 from Policy where Account.ID = Policy.RelatedAccountID and Policy.Status = 'Expired')
    else Account.Type
end

Fiddle

create table Account(ID int, Type varchar(32) default 'Customer');

insert into Account(ID) values(1),(2),(3),(4);

create table Policy(RelatedAccountID int, Status varchar(32));

insert into Policy(RelatedAccountID, Status)
values
(1, 'Active'),
(1, 'Expired'),
(3, 'Active'),
(4, 'Expired'),
(4, 'Renewed');

update Account
set Account.Type = 
case
    when not exists (select 1 from Policy where Account.ID = Policy.RelatedAccountID) then 'Prospect'
    when not exists (select 1 from Policy where Account.ID = Policy.RelatedAccountID and Policy.Status = 'Active') and exists (select 1 from Policy where Account.ID = Policy.RelatedAccountID and Policy.Status = 'Expired') then 'Previous Customer'
    else Account.Type
end;

http://sqlfiddle.com/#!9/45193d/1/0

EDIT

Since the question's intention was to select from the tables rather than update, we can refactor our update into a select:

select Account.ID,
       case
           when max(Related.RelatedAccountID) is null then 'Prospect'
           when max(Active.RelatedAccountID) is null and max(Expired.RelatedAccountID) is not null then 'Previous Customer'
           else Account.Type
       end as Type
from Account
left join Policy Related
on Account.ID = Related.RelatedAccountID
left join Policy Active
on Account.ID = Active.RelatedAccountID and Active.Status = 'Active'
left join Policy Expired
on Account.ID = Expired.RelatedAccountID and Expired.Status = 'Expired'
group by Account.ID;

Fiddle: http://sqlfiddle.com/#!9/0a9c7c6/1/0