TonyEF
asked on
UpDate Field
I have a table named "InspectionsInfo" in this table there are two Fields:
"Inspection Number" and " Inspection Type Code"
I like to have a script that I can run to update the Inspection number is the following criteria is found:
If the "Inspection Number" and "Inspection Type Code" are the same then update any subsquent records where these two fields are the same by adding an "A","B","C",etc, to the end of the "Inspection Number" see example below.
Can someone please assist me with this script???
CURRENT DATA AFTER UPDATE
Inspection Number Inspection Type Code Inspection Number
1998-00000130 PL05 1998-00000130
1998-00000130 PL01 1998-00000130
1998-00000130 PL01 1998-00000130A
1998-00000130 PL04 1998-00000130
1998-00000130 PL04 1998-00000130A
1998-00000130 PL04 1998-00000130B
1998-00000130 PL04 1998-00000130C
1998-00000130 PL04 1998-00000130D
1998-00000130 BD03 1998-00000130
1998-00000130 BD03 1998-00000130A
"Inspection Number" and " Inspection Type Code"
I like to have a script that I can run to update the Inspection number is the following criteria is found:
If the "Inspection Number" and "Inspection Type Code" are the same then update any subsquent records where these two fields are the same by adding an "A","B","C",etc, to the end of the "Inspection Number" see example below.
Can someone please assist me with this script???
CURRENT DATA AFTER UPDATE
Inspection Number Inspection Type Code Inspection Number
1998-00000130 PL05 1998-00000130
1998-00000130 PL01 1998-00000130
1998-00000130 PL01 1998-00000130A
1998-00000130 PL04 1998-00000130
1998-00000130 PL04 1998-00000130A
1998-00000130 PL04 1998-00000130B
1998-00000130 PL04 1998-00000130C
1998-00000130 PL04 1998-00000130D
1998-00000130 BD03 1998-00000130
1998-00000130 BD03 1998-00000130A
Brian Crowe
The first thing you need to do is define "subsequent". What orders the records?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
why don't you add a sequence number column instead?
where does the inspection number come from in the first place ? (is the column a foreign key to another table?)
why do you have duplicates?
what distingusihes the rows in the first place...?
what is the correct order for the rows...?
what happens when you run out of letters ...? (A..Z or miss out vowels , or O, Q, I..)
(you have more than 23... 26 duplicates)
where does the inspection number come from in the first place ? (is the column a foreign key to another table?)
why do you have duplicates?
what distingusihes the rows in the first place...?
what is the correct order for the rows...?
what happens when you run out of letters ...? (A..Z or miss out vowels , or O, Q, I..)
(you have more than 23... 26 duplicates)
ASKER
BriCrowe
It is what I need but noticed that some of the records have more than 26 duplicates, in this case once it reaches the letter Z start with AA and then AB and so on, otherwise it is exactly what I need and if you can help me make that change as well Creating a new field with the new Inspection Number I would greatly appreciate it..
It is what I need but noticed that some of the records have more than 26 duplicates, in this case once it reaches the letter Z start with AA and then AB and so on, otherwise it is exactly what I need and if you can help me make that change as well Creating a new field with the new Inspection Number I would greatly appreciate it..
ASKER
Lowfatsprea
The data is stored like this from a very old database, the reason I need this done is because the new system that I am converting the data too does not except duplicates. The new system considers duplicates where the Inspection Number and the Inspection Type Code are the same. These records are not actually duplicates it is just the way the data is being stored by this old system.
Once after the Z it needs to start with AA, AB, AC and so on....
The data is stored like this from a very old database, the reason I need this done is because the new system that I am converting the data too does not except duplicates. The new system considers duplicates where the Inspection Number and the Inspection Type Code are the same. These records are not actually duplicates it is just the way the data is being stored by this old system.
Once after the Z it needs to start with AA, AB, AC and so on....
try this...
WITH cteInfo (Number, TypeCode, RowNumber)
AS
(
SELECT [Inspection Number],
[Inspection Type Code],
ROW_NUMBER() OVER(PARTITION BY [Inspection Number], [Inspection Type Code] ORDER BY [Inspection Number]) AS RowNumber
FROM InspectionsInfo
)
SELECT Number, TypeCode,
Number + CASE WHEN RowNumber / 26 > 0 THEN CHAR(((RowNumber) / 26) + 64) ELSE '' END
+ CASE WHEN RowNumber > 1 THEN CHAR(CASE (RowNumber - 1) % 26 WHEN 0 THEN 26 ELSE (RowNumber - 1) % 26 END + 64) ELSE '' END
FROM cteInfo
WITH cteInfo (Number, TypeCode, RowNumber)
AS
(
SELECT [Inspection Number],
[Inspection Type Code],
ROW_NUMBER() OVER(PARTITION BY [Inspection Number], [Inspection Type Code] ORDER BY [Inspection Number]) AS RowNumber
FROM InspectionsInfo
)
SELECT Number, TypeCode,
Number + CASE WHEN RowNumber / 26 > 0 THEN CHAR(((RowNumber) / 26) + 64) ELSE '' END
+ CASE WHEN RowNumber > 1 THEN CHAR(CASE (RowNumber - 1) % 26 WHEN 0 THEN 26 ELSE (RowNumber - 1) % 26 END + 64) ELSE '' END
FROM cteInfo