CODEWITHSUNDEEP
sqlsql-server
Adi
Adi

Reputation: 329

Assign values based on priority

I have a column "Designation" in which for some employees there are multiple values. So I want to keep only 1 value in it and also assign that value based on its priority from "designation priority table". example:

Agentdetails:
accountid  designation
1455       owner / Ceo
2035       /Application Consultant/Director
1233       /General / It
1453       /Hrd/It Support/Engineering/It Mgr/ Assistant Marketing Manager 



Designation Priority:
CEO/Application Consultant/IT Mgr/IT    1
Owner/Director                          2
It staff/HRD                            1

so now as per the priority my agentdetails table should look like:

accountid  designation
1455       Ceo               ( as ceo is 1 so it should be taken and rest values should be removed)
2035       Application Consultant
1233       It                ( as there is no general and it value is 1)
1453       Hrd               (Here hrd as it's value is 1 and comes first )

Let me know how to achieve this in SQL. Is there any function or do I need to create a UDF?

Let me know any suggestions.

Upvotes: 0

Views: 277

Answers (1)

Venkataraman R
Venkataraman R

Reputation: 12959

You have to use STRING_SPLIT to split the multi-valued string into separate rows for each table and then join them to get the result.

Even though below code works, I would suggest you make few changes to the datamodel and data quality issues:

  • Keep column values atomic (First Normal Form)
  • Represent values in case-insensitive manner . In this case, designation. If you have got case sensitive collation, it will be an issue
  • Remove Extra spaces around the designations as it can cause JOINS to fail
  • Try to represent all the designations in the designations master table, as it can avoid junk designation values to come up in the agent table, if there is no match present in the designation table.

Reference to SQLFiddle

Test setup

CREATE TABLE AgentDetails
(accountid int, designation VARCHAR(8000))

INSERT INTO AgentDetails
VALUES
('1455','owner / Ceo'),
('2035','/Application Consultant/Director'),
('1233','/General / It'),
('1453','/Hrd/It Support/Engineering/It Mgr/ Assistant Marketing Manager')
;

CREATE TABLE DesignationPriority
(Designation VARCHAR(8000), Priority int)

INSERT INTO DesignationPriority
VALUES
('CEO/Application Consultant/IT Mgr/IT',1),
('Owner/Director', 2),
('It staff/HRD', 1);

Query to Execute

;WITH CTE_AgentDetails AS
(
SELECT accountId, TRIM(value) AS Designation
FROM AgentDetails
CROSS APPLY STRING_SPLIT(designation,'/')
WHERE LEN(value) > 0
), CTE_DesignationPriority AS
(
SELECT Priority, TRIM(value) AS Designation
FROM DesignationPriority
CROSS APPLY STRING_SPLIT(Designation,'/')
)
SELECT accountID, Designation
FROM
(
SELECT accountID
  ,a.Designation
  , ROW_NUMBER() OVER(PARTITION BY a.AccountID ORDER BY CASE WHEN p.Priority IS NULL THEN 999 ELSE p.priority END) AS rnk
FROM
CTE_AgentDetails AS a
LEFT OUTER JOIN CTE_DesignationPriority AS p
ON p.Designation = a.Designation

) AS t 
WHERE rnk = 1;

If you dont want CTE, you can go for derived table.

SELECT accountID, Designation
FROM
(
SELECT accountID
  ,a.Designation
  , ROW_NUMBER() OVER(PARTITION BY a.AccountID ORDER BY CASE WHEN p.Priority IS NULL THEN 999 ELSE p.priority END) AS rnk
FROM
(SELECT accountId, TRIM(value) AS Designation
FROM AgentDetails
CROSS APPLY STRING_SPLIT(designation,'/')
WHERE LEN(value) > 0)AS a

LEFT OUTER JOIN 

(SELECT Priority, TRIM(value) AS Designation
FROM DesignationPriority
CROSS APPLY STRING_SPLIT(Designation,'/')) AS p
ON p.Designation = a.Designation

) AS t 
WHERE rnk = 1;

Resultset

+-----------+------------------------+
| accountID |      Designation       |
+-----------+------------------------+
|      1233 | It                     |
|      1453 | Hrd                    |
|      1455 | Ceo                    |
|      2035 | Application Consultant |
+-----------+------------------------+

Upvotes: 1

Related Questions