SQL Server – Subquery Inside a CASE Statement

casesql serversql-server-2016

I have a huge view -the view gathers information of players.
In a specific part the view assigns agents for players, those players are selected from table 'S' and are being assigned using a case statement.
I want to assign specific agents according to a aggregated value called total_deposit in table T.
so basically i want to say case make the agent name Andy when users from table S have max(total_deposit)within the conditions below (from table T) that is the code i tried:

        CASE 
        WHEN s.Username = gs.username THEN gs.agent
    when exists (select t.username from temptest t
            where t.username = s.username
            group by t.username 
             having max(t.total_deposit) >= 5000 or
                (    max(t.total_deposit) >= 1000 AND max(t.total_deposit) < 5000 )or
                      ( max(t.total_deposit) >= 500 AND max(t.total_deposit) < 1000) or
                      ( max(t.total_deposit) >= 250 AND max(t.total_deposit) < 500  )
            ) and s.Country in('Austria', 'Luxemburg', 'Switzerland', 'Lichtenstein') 

                         then 'Andy'
else 'Joy'

It didn't work because if exists needs to follow with an expression..
how can i do this? thanks

Best Answer

If I understood you correctly, all you need is to calculate max(total_deposit) and join it to your main table, it can be done by using CTE or, if you wish, a subquery, like this:

declare @s table (username varchar(100));
insert into @s values ('peter'),('ann'), ('paul'), ('john'); 

declare @t table(username varchar(100), total_deposit int); 
insert into @t 
values  ('peter', 1000),('peter', 200),
        ('ann', 100),  ('ann', 150),
        ('paul', 300);

with tot_dep as
(select username, max(total_deposit) as max_d
 from @t
 group by username) 

select s.username,
       case when t.max_d >= 500 then 'Andy'
            else 'Joy'
       end as agent
from @s s left join tot_dep t
        on s.username = t.username;

or (that is the same)

declare @s table (username varchar(100));
insert into @s values ('peter'),('ann'), ('paul'), ('john'); 

declare @t table(username varchar(100), total_deposit int); 
insert into @t 
values  ('peter', 1000),('peter', 200),
        ('ann', 100),  ('ann', 150),
        ('paul', 300);

select s.username,
       case when t.max_d >= 500 then 'Andy'
            else 'Joy'
       end as agent
from @s s left join  (select username, max(total_deposit) as max_d
                     from @t
                     group by username) t
        on s.username = t.username;
Related Question