0

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

| improve this question | |
1

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;
| improve this answer | |
  • Hi i tried to use WITH but this select is being used as a subquery..so it doesn't work. its such a huge view i don't know what to do to make this work lol – sql_girl Sep 4 '18 at 9:50
  • should i go all the way to the start of the query and then place the with? thanks for your time. – sql_girl Sep 4 '18 at 9:50
  • Yes, your view should start with: " create view ... as with tot_dep as...." and then you join it to the main query – sepupic Sep 4 '18 at 9:55
  • thanks i'm a bit confused cause i have like 8 parts of CTE on this view. this view already starts with a CTE..i'm gonna try to figure it out. thank you – sql_girl Sep 4 '18 at 10:01
  • If you want to keep it as CTE, just add this CTE after the last one (separated by comma). Or you can add it as subquery as well – sepupic Sep 4 '18 at 10:03
0

Have you tried (SELECT 1 from temptest t where t.username = s.username.....

instead of t.username?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.