CODEWITHSUNDEEP
sqlsql-servercase
Veli K
Veli K

Reputation: 35

Case When Expression in Different Way

I need a new column by assigning a value from an existing column in SQL Server.

I want to get commission2 column according to commission amounts.

There are customers which can be included 1 or 2 brands and commission amounts as well.

Customerid CountCustomer Brand commission commission2
1000 1 B -28 0
1001 2 B 12 12
1001 2 F 252 252
1002 1 B 62 62
1003 1 B 5 5
1004 1 B -61 0
1005 1 F 0 0
1006 1 F 32 32
1007 1 F 0 0
1008 1 B -496 0
1009 1 F 1 1
1010 1 B 1 1
1011 1 B 0 0
1012 1 F -10 0
1013 1 B 82 82
1014 1 B 54 54
1015 1 B 18 18
1016 1 B 0 0
1017 2 B 0 0
1017 2 F -21 0
1018 1 B 0 0
1019 1 B 5 5
1020 1 B -3 0
1021 1 F -1 0
1022 1 B 95 95
1023 1 B -20 0
1024 1 B 0 0
1025 2 B -65 10
1025 2 F 10 10
1026 2 B 24 24
1026 2 F -7 24
1027 2 B 0 0
1027 2 F 0 0

Here are the rules:

Upvotes: 2

Views: 113

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

Use window functions to see the number of entries per customer as well as the minimum and maximum commissions:

select 
  customerid,
  count(*) over (partition by customerid) as countcustomer,
  brand,
  commission,
  case 
    when count(*) over (partition by customerid) = 1 then
      case when commission < 0 then 0 else commission end
    else
      case 
        when max(commission) (partition by customerid) <= 0 then
          0
        when min(commission) (partition by customerid) >  0 then
          commission
        else
          max(commission) (partition by customerid)
      end
  end as commission2
from mytable
order by customerid, brand;

Or, as the multi-row conditions work for the single-row customers, too, just:

select 
  customerid,
  count(*) over (partition by customerid) as countcustomer,
  brand,
  commission,
  case 
    when max(commission) (partition by customerid) <= 0 then
      0
    when min(commission) (partition by customerid) >  0 then
      commission
    else
      max(commission) (partition by customerid)
  end as commission2
from mytable
order by customerid, brand;

Upvotes: 4

Teun Kruijer
Teun Kruijer

Reputation: 152

SELECT
    Customerid  
    ,CountCustomer  
    ,Brand  
    ,commission
    CASE WHEN CountCustomer = 1 AND commission >= 0 THEN commission
         WHEN CountCustomer = 2 AND commission > 0 AND commission2 < 0 THEN commission
         WHEN CountCustomer = 2 AND commission < 0 AND commission2 > 0 THEN commission2
         WHEN CountCustomer = 2 AND commission < 0 AND commission2 < 0 THEN 0   
    ELSE commission2 AS 'commission2'
FROM MyTable

I tried my best to follow your explanation. You can modify the statements as you need them. This should return the column commission2 as you want it.

Upvotes: 0

Vitor Xavier
Vitor Xavier

Reputation: 27

I dont think that's the logic you're seeking, but if that's what I understood, the CASE WHEN should be like this:

SELECT Customerid, CountCustomer, Brand, comission,
         CASE WHEN CountCustomer = 1 AND commission > 0 THEN commision
            WHEN CountCustomer = 1 AND commission < 0 THEN 0
            WHEN CountCustomer = 2 AND commission > 0 THEN comission
            WHEN CountCustomer = 2 AND commission < 0 THEN 0
            END AS commission2
FROM YourTable

Upvotes: 0

Related Questions