question

Hawfinch avatar image
Hawfinch asked

Want to create logic in SQL Server to create a derived column based on comparing two comma-separated columns and one other column which have a single record for each row ( For reference refer to the attached screenshot).

15112021-175130-rec.pngScenario: 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?

sqlserver2012
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.