question

Josh-0762 avatar image
0 Votes"
Josh-0762 asked NaomiNNN answered

check for designation and assign new designation else retain the same


i have tables that has employee data and there designations , we are moving few deisgnated employees to diff roles . below are the coindiotns . for all others the roles remain the same

199239-image.png


sql-server-transact-sql
image.png (17.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered

Try this:

 DROP TABLE IF EXISTS #temp;
 CREATE table #temp ( employee varchar(100), designation varchar(100))
    
         
   insert into #temp values ('AAA','IT  Sales Person'),('AAA','Dept head'),('AAA','supervisor')
   insert into #temp values ('BBBB','Sales Manager - Area'),('BBBB','Dept head')
   insert into #temp values ('CCCC','Sales Manager - Region') , ('CCCC','Dept head')
    
   SELECT employee, designation, CASE designation WHEN 'IT Sales Person' THEN 'Salesperson'
   WHEN 'Sales Manager - Area' THEN 'Sales Manager' WHEN 'Sales Manager - Region' THEN 'Salesperson' ELSE designation END AS [New Designation]
   FROM #temp
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JingyangLi avatar image
0 Votes"
JingyangLi answered Josh-0762 commented

Do you have a lookup table to hold these designation values? (you need to update only one place).
If you don't, you can update these values with your new rules use a CASE expression for column values update:


Update yourtable
Set col = Case
when col='IT Sales Person ' then 'SalesPerson'
when col='Sales Manager --Area' then ' Sales Manager'
when col='Sales Manager --Region' then ' SalesPerson'
end
Where col in ('IT Sales Person ','Sales Manager --Area','Sales Manager --Region');


Please provide your sample DDL and data if need more help.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

each employee has 2or more deisgnations , code must look through each designation /role , check for the condition and assign or retain the same

0 Votes 0 ·

You need to post your table DDL and sample data along with your expected result.

0 Votes 0 ·

here is the table



199363-image.png


0 Votes 0 ·
image.png (34.5 KiB)
Show more comments