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:
If CountCustomer = 1 then it is easy. If commission <= 0 then commission2 is 0 otherwise it is equal to commission
If CountCustomer = 2 (means a customer is on 2 brands), and one of the commission amounts > 0 and the other one is < 0, then both commission2 values should be the number which is positive. If both of the commission numbers are above 0, then these numbers should stay the same for commission2. If both commissions are < 0 then commission2 should be 0 for both. You can understand more clear from bolded rows on the table above.
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;