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
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
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.
each employee has 2or more deisgnations , code must look through each designation /role , check for the condition and assign or retain the same
You need to post your table DDL and sample data along with your expected result.
15 people are following this question.
