CODEWITHSUNDEEP
mysqlsqlsql-server
Venk Krish
Venk Krish

Reputation: 85

Introduce 4 digits based on three columns

I have an interesting problem, I would like to generate an 8 digit unique ID for a table. I have the first 4 digits in a column and the task is to attach another four digits to the existing 4 digits. The following are the conditions and sample data.

The first four digits are found in this column "AFFECTSID_NEW" from the bellow sample data

Sample Data:

AFFECTSID_NEW | activityname​                | actionname
---------------------------------------------------------------
2301          | Default Proposed Activity   | sample 2
2301          | Communicating welcome pack  | sample 1
1206          | Execute events (7 Events)   | International Trade Seminar 
1206          | Execute events (8 Events)   | Workshop with one law firm
1206          | Execute events (8 Events)   | Workshop with International Speaker
1206          | Execute events (8 Events)   | Seminar with Government agency
1206          | Execute events (8 Events)   | Execute events (8 Events)
1205          | Resolve commercial disputes | Resolve commercial disputes

Cases:

For generating the 5th and 6th digit

  1. ​​for the same "Affectsid_new" and unique "activityname" it should be 01,02,03...
  2. for non unique "activityname" and "activityname"="actionname" it should either be the continuation digits from the above point 1 if the "Affectsid_new" is also non unique or it should be "01" if "Affectsid_new" is unique

​ Once 5th and 6th digits are generated these are the conditions for generating the 7th and 8th digit

  1. ​​for the same "Affectsid_new" and unique "activityname" it should be "01"
  2. for non unique "activityname" and "activityname"="actionname" it should be "01"
  3. for non unique "activityname" and "activityname"≠"actionname" (not equal) it should either be the continuation digits from the above point 2 (02,03,04..) if the "Affectsid_new" is also non unique or it should be "01" if "Affectsid_new" is unique

The Expected result is as follows

AFFECTSID_NEW | activityname​                   | actionname                           | Expected
---------------------------------------------------------------------------------------------------
2301          | Default Proposed Activity      | sample 2                             | 23010101 
2301          | Communicating welcome pack     | sample 1                             | ​23010201
1206          | Execute events (7 Events)      | International Trade Seminar          | 12060101
1206          | Execute events (8 Events)      | Workshop with one law firm           | ​12060202
1206          | Execute events (8 Events)      | Workshop with International Speaker  | ​12060203
1206          | Execute events (8 Events)      | Seminar with Government agency       | ​12060204
1206          | Execute events (8 Events)      | Execute events (8 Events)            | ​12060201
1205          | Resolve commercial disputes    | Resolve commercial disputes          | ​12050101

Upvotes: 2

Views: 73

Answers (2)

Benison Sam
Benison Sam

Reputation: 2825

Please try the following uncommented query. I have tried to implement what you need but the result would not be exactly the same as what you have given here as an example. The result depends upon which column you choose to order:

SELECT *,
(AFFECTSID_NEW +
 RIGHT('00' + CONVERT(NVARCHAR, RANK() OVER (PARTITION BY AFFECTSID_NEW ORDER BY activityname)), 2) +
 RIGHT('00' + CONVERT(NVARCHAR, RANK() OVER (PARTITION BY activityname ORDER BY actionname)), 2)) AS Expected
FROM #TempTable

Following is the table creation scripts (Just in case).

--CREATE TABLE #TempTable (AFFECTSID_NEW NVARCHAR(8), activityname NVARCHAR(256), actionname NVARCHAR(256))

--INSERT INTO #TempTable
--VALUES
--('2301', 'Default Proposed Activity', 'sample 2'),
--('2301', 'Communicating welcome pack', 'sample 1'),
--('1206', 'Execute events (7 Events)', 'International Trade Seminar'),
--('1206', 'Execute events (8 Events)', 'Workshop with one law firm'),
--('1206', 'Execute events (8 Events)', 'Workshop with International Speaker'),
--('1206', 'Execute events (8 Events)', 'Seminar with Government agency'),
--('1206', 'Execute events (8 Events)', 'Execute events (8 Events)'),
--('1205', 'Resolve commercial disputes', 'Resolve commercial disputes')

Hope this helps...

Upvotes: 0

Anon
Anon

Reputation: 10908

SELECT
  *
 ,AFFECTSID_NEW
  + REPLACE(STR(DENSE_RANK() OVER(PARTITION BY AFFECTSID_NEW ORDER BY activityname​),2),' ','0')
  + REPLACE(STR(ROW_NUMBER() OVER(PARTITION BY AFFECTSID_NEW,activityname ORDER BY actionname​),2),' ','0')
FROM MyTable

Upvotes: 2

Related Questions