Scenario: I have two comma-separated columns (Column 2 and 3) and another Column which is having a single record in each row.
- I need to calculate values for only "Closed" values.
- In Second row- I have a Closed value in columns 1 and column 2. In column 3, for Recruiter it is closed so in the output column – it will be Recruiter.
- In the Third row – I have a Closed value in columns 1 and column 2 and In column 3 for Internal It is closed , in the output column – it will be Internal.
- in the Fourth row - I have a Closed value for both Internal and recruiter in column 2 so in the output Recruiter – because Recruiter Preference is more than Internal. Recruiter >Internal> Contractor
- In the Fifth row – I don't have any closed role so it will be Null.
- And so on…
The position of
Closed value is not fixed in column 2. It can be anywhere such as - either at 1st place/position or 2nd place or in 3rd place.
Max comma-separated values allowed for Column 2 and Column 3 are Three.
And for any other values in column 1 other than Closed, it will NULL.
****This Table is loaded every day and it used to process 6 million + records daily. and containing data of total 13 dates ****
Question: What will be the best approach/logic in SQL Server? Or what should be the query for retrieving the value?