CODEWITHSUNDEEP
sqlsql-server
Dee
Dee

Reputation: 19

Condition statement in SQL Server

I am trying to retrieve an agent's collection based on his/her role. An agent can have two roles i.e. collector and team lead. In this case, I need to retrieve row for team lead only.

Tried using case statement. But no luck.

select 
    commission_date, agent_id, agent, team, role, target, collection,  
    commission, final_payout 
from 
    ambank.dbo.commissions
where 
    commission_date = '2019-06-30' 
    and (target > 0 or final_payout > 0)
    and agent_id in (4446, 2267)
order by 
    agent

Current result:

com_date        agent_id    agent   team        role        target  collection  commission  final_payout
2019-06-30      4446        AIZAT   Team A      Collector   130000     100        0        0
2019-06-30      4446        AIZAT   Team B      Collector   130000    18767.68       0        0
2019-06-30      2267        ERIC    Team C      Collector   130000    34200          0        0
2019-06-30      2267        ERIC    Team Lead   Non-collector   650000    209789.99    0          0

Expected result:

com_date    agent_id    agent   team        role        target  collection  commission  final_payout
2019-06-30  4446        AIZAT   Team A      Collector   130000  100                 0    0
2019-06-30  4446        AIZAT   Team B      Collector   130000  18767.68            0    0
2019-06-30  2267        ERIC    Team Lead   Non-collector   650000  209789.99        0    0

Upvotes: 1

Views: 108

Answers (4)

Shushil Bohara
Shushil Bohara

Reputation: 5656

Simply, We can achieve that using OUTER APPLY also, we can check if Team Lead exists for that agent or not then add condition in where clause accordingly

select 
    commission_date, 
    agent_id, 
    agent, 
    team, 
    role, 
    target, 
    collection,  
    commission, 
    final_payout 
from ambank.dbo.commissions c
outer apply(select count(*) cnt 
            from ambank.dbo.commissions c1
            where c.agent_id = c1.agent_id
            and c1.team = 'Team Lead') c1
where commission_date = '2019-06-30' 
and (target > 0 or final_payout > 0)
and agent_id in (4446, 2267)
AND (c1.cnt = 0 OR c.team = 'Team Lead')
order by agent

Upvotes: 1

Dale K
Dale K

Reputation: 27249

I think you need a window function e.g.

select *
from (
  select commission_date, agent_id, agent, team, [role], [target], [collection], commission, final_payout 
    , row_number() over(partition by agent_id order by case when team = 'Team Lead' then 1 else 0 end desc) row#
  from ambank.dbo.commissions
  where commission_date = '2019-06-30' and ([target] > 0 or final_payout > 0)
  and agent_id in (4446,2267)
) x
where row# = 1
order by agent;

Upvotes: 3

mkRabbani
mkRabbani

Reputation: 16908

You can check this below option-

SELECT * FROM your_table
WHERE agent_id NOT IN (
    SELECT agent_id FROM your_table 
    WHERE  team = 'team lead'
)

UNION ALL

SELECT * FROM your_table
WHERE agent_id IN(
    SELECT agent_id FROM your_table 
    WHERE  team = 'team lead'
)
AND team = 'team lead'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

I think this is a prioritization query. Select the team lead, if any, and if there are none, select all the rows.

If so:

select c.*
from ambank.dbo.commissions c
where commission_date = '2019-06-30' and
      (target > 0 or final_payout > 0) and
      agent_id in (4446, 2267) and
      (c.team = 'Team Lead' or
       not exists (select 1
                   from ambank.dbo.commissions c2
                   where c2.agent_id = c.agent_id and
                         c2.commission_date = c.commission_date and
                         (c2.target > 0 or c2.final_payout > 0) and
                         c2.team <> 'Team Lead'
                  )
      )
order by agent ;

You can also express this with window functions:

select c.*
from (select c.*,
             sum(case when c.team = 'Team Lead' then 1 else 0 end) over (partition by agent_id) as num_team_lead
      from ambank.dbo.commissions c
      where commission_date = '2019-06-30' and
            (target > 0 or final_payout > 0) and
            agent_id in (4446, 2267) 
     ) c
where c.team = 'Team Lead' or num_team_lead = 0
order by agent ;

Upvotes: 1

Related Questions