Maintaining a chain with multiple inserts

Hello,

I know that this has probably been discussed, but I couldn't find
anything with Google. I have the following problem... I receive a set
of text files that describe a chain of insurance claims. Each claim may
be copied from an older claim and it may be copied to a newer claim. I
get files that give information in both directions (i.e., One file says
Claim #1 copied TO Claim #2 and another file says Claim #2 copied FROM
Claim #1). I already have something in place to validate the files
against each other.

What I need to end up with is a row for each claim that gives the
original claim number in the chain (NULL if it is an original claim),
the claim from which it was copied (NULL if none), and the claim to
which it was copied (NULL if none). The problem is that I may need to
insert claims into the middle of a chain (ok, that's not hard), and I
may also receive records in the file that have the same source claim
but multiple destination claims. These records must be changed to
create a single chain. For example, if I receive records that show 1->2
and 1->3 then this should be converted to show 1->2 and 2->3. The
business doesn't have any strong rules on how to reconcile these, so
I've decided to just go with ordering by the claim IDs when determining
order if it's otherwise unclear. Using this, I've solved the second
situation as well, but then I started to consider the situation of
receiving 1->2, 1->3, and 2->4. This should end up as 1->2, 2->3, 3->4
but I can't figure out how to do that without cursors or multiple
passes.

Below is some SQL to work with. Any suggestions are greatly
appreciated. Also, if you can think of any other situations that I
might need to handle then that would be useful as well.

Thanks,
-Tom.

CREATE TABLE Copy_To (
My_ID INT NOT NULL,
Copy_To INT NOT NULL )
GO
ALTER TABLE Copy_To ADD CONSTRAINT PK_Copy_To PRIMARY KEY (My_ID,
Copy_To)
GO

CREATE TABLE Copy_From (
My_ID INT NOT NULL,
Copy_From INT NOT NULL )
GO
ALTER TABLE Copy_From ADD CONSTRAINT PK_Copy_From PRIMARY KEY (My_ID,
Copy_From)
GO

CREATE TABLE Chain (
My_ID INT NOT NULL,
Orig_ID INT NULL,
Copy_To INT NULL,
Copy_From INT NULL )
GO
ALTER TABLE Chain ADD CONSTRAINT PK_Chain PRIMARY KEY (My_ID)
GO

INSERT INTO Copy_To VALUES (2, 3)
INSERT INTO Copy_To VALUES (2, 4)
INSERT INTO Copy_To VALUES (3, 5)
GO
INSERT INTO Copy_From VALUES (3, 2)
INSERT INTO Copy_From VALUES (4, 2)
INSERT INTO Copy_From VALUES (5, 3)
GO
-- The chain table may already have some existing rows
INSERT INTO Chain VALUES (1, NULL, 6, NULL)
INSERT INTO Chain VALUES (6, 1, NULL, 1)
GO
/*
The expected results would be rows in Chain as follows:

My_ID Orig_ID Copy_To Copy_From
1 NULL 2 NULL
2 1 3 1
3 1 4 2
4 1 5 3
5 1 6 4
6 1 NULL 5

In this case the numbers are all sequential, but that will not always
be the case. A chain could be 1->4->8 or possibly even 4->8->1->7.
*/Hi

Your copy_to/from tables seems to be containing a hierarchy that you are
disregarding therefore they may be redundant!!!

If you had a master claimparts table then:

CREATE TABLE CLAIMPARTS ( claim char(1), part int )

INSERT INTO CLAIMPARTS ( claim , part )
SELECT 'A', 1
UNION ALL SELECT 'A', 2
UNION ALL SELECT 'A', 3
UNION ALL SELECT 'A', 4
UNION ALL SELECT 'B', 1
UNION ALL SELECT 'B', 8
UNION ALL SELECT 'B', 7
UNION ALL SELECT 'B', 3

SELECT C.CLAIM, C.PART,
(SELECT MIN(PART) FROM CLAIMPARTS P
WHERE C.PART < P.PART
AND C.CLAIM = P.CLAIM ) AS NEXTPART
FROM CLAIMPARTS C
ORDER BY C.CLAIM, C.PART

Would give you the hierarchy regardless of the previous copyto/from
information.

John

"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote in message
news:1128105505.180521.155350@.f14g2000cwb.googlegr oups.com...
> Hello,
> I know that this has probably been discussed, but I couldn't find
> anything with Google. I have the following problem... I receive a set
> of text files that describe a chain of insurance claims. Each claim may
> be copied from an older claim and it may be copied to a newer claim. I
> get files that give information in both directions (i.e., One file says
> Claim #1 copied TO Claim #2 and another file says Claim #2 copied FROM
> Claim #1). I already have something in place to validate the files
> against each other.
> What I need to end up with is a row for each claim that gives the
> original claim number in the chain (NULL if it is an original claim),
> the claim from which it was copied (NULL if none), and the claim to
> which it was copied (NULL if none). The problem is that I may need to
> insert claims into the middle of a chain (ok, that's not hard), and I
> may also receive records in the file that have the same source claim
> but multiple destination claims. These records must be changed to
> create a single chain. For example, if I receive records that show 1->2
> and 1->3 then this should be converted to show 1->2 and 2->3. The
> business doesn't have any strong rules on how to reconcile these, so
> I've decided to just go with ordering by the claim IDs when determining
> order if it's otherwise unclear. Using this, I've solved the second
> situation as well, but then I started to consider the situation of
> receiving 1->2, 1->3, and 2->4. This should end up as 1->2, 2->3, 3->4
> but I can't figure out how to do that without cursors or multiple
> passes.
> Below is some SQL to work with. Any suggestions are greatly
> appreciated. Also, if you can think of any other situations that I
> might need to handle then that would be useful as well.
> Thanks,
> -Tom.
> CREATE TABLE Copy_To (
> My_ID INT NOT NULL,
> Copy_To INT NOT NULL )
> GO
> ALTER TABLE Copy_To ADD CONSTRAINT PK_Copy_To PRIMARY KEY (My_ID,
> Copy_To)
> GO
> CREATE TABLE Copy_From (
> My_ID INT NOT NULL,
> Copy_From INT NOT NULL )
> GO
> ALTER TABLE Copy_From ADD CONSTRAINT PK_Copy_From PRIMARY KEY (My_ID,
> Copy_From)
> GO
> CREATE TABLE Chain (
> My_ID INT NOT NULL,
> Orig_ID INT NULL,
> Copy_To INT NULL,
> Copy_From INT NULL )
> GO
> ALTER TABLE Chain ADD CONSTRAINT PK_Chain PRIMARY KEY (My_ID)
> GO
> INSERT INTO Copy_To VALUES (2, 3)
> INSERT INTO Copy_To VALUES (2, 4)
> INSERT INTO Copy_To VALUES (3, 5)
> GO
> INSERT INTO Copy_From VALUES (3, 2)
> INSERT INTO Copy_From VALUES (4, 2)
> INSERT INTO Copy_From VALUES (5, 3)
> GO
> -- The chain table may already have some existing rows
> INSERT INTO Chain VALUES (1, NULL, 6, NULL)
> INSERT INTO Chain VALUES (6, 1, NULL, 1)
> GO
> /*
> The expected results would be rows in Chain as follows:
> My_ID Orig_ID Copy_To Copy_From
> 1 NULL 2 NULL
> 2 1 3 1
> 3 1 4 2
> 4 1 5 3
> 5 1 6 4
> 6 1 NULL 5
> In this case the numbers are all sequential, but that will not always
> be the case. A chain could be 1->4->8 or possibly even 4->8->1->7.
> */|||Hi John,

I think that I may have been unclear about the problem. The Copy_To and
Copy_From columns are IDs. I should have probably put foreign keys to
My_ID in my example code to make that clear. Also, the Copy_To and
Copy_From tables are direct imports of files that I receive, so I have
no control over how that information is presented. If you had a way
that I could convert those rows into something like what you have
(instead of Claim and Part I would basically have the Orig_ID and
My_ID) then that would probably solve my problem.

Thanks,
-Tom.|||Hi Thomas

This gives you the results you wanted, but if you have more than one chain
you will need a means of identifying them.

CREATE VIEW AllIds AS
SELECT My_Id FROM Chain
UNION SELECT My_Id FROM Copy_To
UNION SELECT My_Id FROM Copy_From

SELECT I.My_Id,
CASE WHEN I.My_Id = C.My_Id THEN C.Orig_ID ELSE C.My_Id END AS Orig_ID,
CASE WHEN I.My_Id = C.Copy_to THEN E.Copy_to ELSE (SELECT MIN(My_Id) FROM
AllIds P WHERE I.My_Id < P.My_Id) END AS Copy_To,
CASE WHEN I.My_Id = C.My_Id THEN C.Copy_From ELSE (SELECT MAX(My_Id) FROM
AllIds P WHERE I.My_Id > P.My_Id) END AS Copy_from
FROM AllIds I
JOIN Chain C ON C.My_Id <= I.My_Id AND C.Copy_to >= I.My_Id
JOIN Chain E ON C.Copy_to = E.My_Id AND E.Orig_id = C.My_id
ORDER BY I.My_Id

John

"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote in message
news:1128138055.333646.242360@.g14g2000cwa.googlegr oups.com...
> Hi John,
> I think that I may have been unclear about the problem. The Copy_To and
> Copy_From columns are IDs. I should have probably put foreign keys to
> My_ID in my example code to make that clear. Also, the Copy_To and
> Copy_From tables are direct imports of files that I receive, so I have
> no control over how that information is presented. If you had a way
> that I could convert those rows into something like what you have
> (instead of Claim and Part I would basically have the Orig_ID and
> My_ID) then that would probably solve my problem.
> Thanks,
> -Tom.|||John Bell wrote:
> Hi Thomas
> This gives you the results you wanted, but if you have more than one chain
> you will need a means of identifying them.

Unfortunately, that's perhaps the biggest problem that I have. The only
way to determine that a row in Copy_To or Copy_From is in a chain with
another row is to follow the links. Once the rows are in the Chain
table the Orig_ID can be used to distinguish all chains.

I'll play around with the code that you included and see if I can get
something worked out. Unfortunately, I think that I'm going to be stuck
recursing through the chains to link them together.

Thanks for all of your help!

-Tom.|||Hi

If your ranges do not overlap then you should be able to cope with the query
I posted use c.my_id as the chains identifier as your orig_id was NULL or
the first entry.

John

"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote in message
news:1128177507.802681.287910@.f14g2000cwb.googlegr oups.com...
> John Bell wrote:
>> Hi Thomas
>>
>> This gives you the results you wanted, but if you have more than one
>> chain
>> you will need a means of identifying them.
> Unfortunately, that's perhaps the biggest problem that I have. The only
> way to determine that a row in Copy_To or Copy_From is in a chain with
> another row is to follow the links. Once the rows are in the Chain
> table the Orig_ID can be used to distinguish all chains.
> I'll play around with the code that you included and see if I can get
> something worked out. Unfortunately, I think that I'm going to be stuck
> recursing through the chains to link them together.
> Thanks for all of your help!
> -Tom.|||I've come up with a solution to the problem which I thought might be
useful for others. My problem was that I kept trying to find different
ways to force the data into the same structure. Finally it occurred to
me to look for a better model that is easier to work with.

I came up with the following:

CREATE TABLE Chain (
My_ID INT NOT NULL,
Orig_ID INT NOT NULL,
Level INT NOT NULL)
GO
ALTER TABLE Chain ADD CONSTRAINT PK_Chain PRIMARY KEY (My_ID)
GO

Now, converting the data is fairly quick and simple. First I add in all
of the "base" claims. Those are any claims that aren't copied from
somewhere else:

INSERT INTO Chain (My_ID, Orig_ID, Level)
SELECT CT1.My_ID, CT1.My_ID, 0
FROM Copy_To CT1
LEFT OUTER JOIN Copy_To CT2 ON CT2.Copy_To = CT1.My_ID
WHERE CT2.My_ID IS NULL

Once the base claims are added I add each new level in a loop until
there are no levels left:

DECLARE @.ins_count INT
SET @.ins_count = 1
WHILE (@.ins_count > 0)
BEGIN
INSERT INTO Chain (My_ID, Orig_ID, Level)
SELECT
MIN(CF.My_ID),
C1.Orig_ID,
SQ.Max_Level + 1
FROM Copy_From CF
INNER JOIN Chain C1 ON C1.Orig_ID = CF.Copy_From
INNER JOIN (SELECT Orig_ID, MAX(Level) AS Max_Level
FROM Chain
GROUP BY Orig_ID) SQ ON SQ.Orig_ID = C1.Orig_ID
LEFT OUTER JOIN Chain C2 ON C2.My_ID = CF.My_ID
WHERE C2.My_ID IS NULL
GROUP BY C1.Orig_ID, SQ.Max_Level

SELECT @.ins_count = @.@.ROWCOUNT
END

This logic handles all of the various problems with the data such as a
single claim being copied to multiple destination claims. The one
problem that it doesn't handle is a claim which is copied FROM multiple
claims, but this is an unresolvable error condition, so I find those
rows and move them to an error table ahead of time.

To improve performance I made a unique clustered index for Chain on the
Orig_ID and Level.

Getting the data that I need is fast and easy from this table as well:

SELECT C.My_ID, C.Orig_ID, CF.My_ID AS Copy_From, CT.My_ID AS Copy_To
FROM Chain C
LEFT OUTER JOIN Chain CF ON CF.Orig_ID = C.Orig_ID AND CF.Level =
C.Level - 1
LEFT OUTER JOIN Chain CT ON CT.Orig_ID = C.Orig_ID AND CT.Level =
C.Level + 1

-Tom.

Maintain Transaction even if need to loop through multiple data

I have a ADP app that executes several SPs with a click of a button
Now I want to combine these SP into 1 transaction so that it can
rollback even if one SP fails.
Problem is several SP need to process recordsets in vba so I have made
while loops in vba for them. I do this to avoid cursors as they no good
performers. My choice of avoiding cursors has broken the 'transaction
boundary' therefore I cannot do a roll back for these. How can I still
use store procedures but still maintain performance and be able to roll
back all the SPs if any of them fails?Have you considered rewriting the procs in set based fashion such that
you don't need to call them in a loop? A for each loop is really just a
cursor by another name. Without seeing what you are doing it's
difficult to advise otherwise.
David Portas
SQL Server MVP
--|||Thanks for the hint -> set based technique.
I've found what I've been looking for:
http://groups.google.com/group/micr...8924a2b73888ca4|||I got another problem.
I got this dynamic sp:
create procedure spFindData()
as
declare @.statement varchar(255),@.intYear int,intCount int
set @.intyear = 2010
while @.intyear >=2004
begin
select @.statement = "select count(*) from sales' + @.intYear
-- I want to assign the result of @.statement to @.intCount, how?
if (@.intCount>0)
begin
return @.intYear
end
@.intYear = @.intYear -1
end

Maintain Transaction even if need to loop through multiple data

I have a ADP app that executes several SPs with a click of a button
Now I want to combine these SP into 1 transaction so that it can
rollback even if one SP fails.
Problem is several SP need to process recordsets in vba so I have made
while loops in vba for them. I do this to avoid cursors as they no good
performers. My choice of avoiding cursors has broken the 'transaction
boundary' therefore I cannot do a roll back for these. How can I still
use store procedures but still maintain performance and be able to roll
back all the SPs if any of them fails?Have you considered rewriting the procs in set based fashion such that
you don't need to call them in a loop? A for each loop is really just a
cursor by another name. Without seeing what you are doing it's
difficult to advise otherwise.
--
David Portas
SQL Server MVP
--|||Thanks for the hint -> set based technique.
I've found what I've been looking for:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/eb09130dcb79c392/d8924a2b73888ca4?lnk=st&q=stored+procedure+set+based&rnum=3&hl=en#d8924a2b73888ca4|||I got another problem.
I got this dynamic sp:
create procedure spFindData()
as
declare @.statement varchar(255),@.intYear int,intCount int
set @.intyear = 2010
while @.intyear >=2004
begin
select @.statement = "select count(*) from sales' + @.intYear
-- I want to assign the result of @.statement to @.intCount, how?
if (@.intCount>0)
begin
return @.intYear
end
@.intYear = @.intYear -1
end

Maintain Transaction even if need to loop through multiple data

I have a ADP app that executes several SPs with a click of a button
Now I want to combine these SP into 1 transaction so that it can
rollback even if one SP fails.
Problem is several SP need to process recordsets in vba so I have made
while loops in vba for them. I do this to avoid cursors as they no good
performers. My choice of avoiding cursors has broken the 'transaction
boundary' therefore I cannot do a roll back for these. How can I still
use store procedures but still maintain performance and be able to roll
back all the SPs if any of them fails?
Have you considered rewriting the procs in set based fashion such that
you don't need to call them in a loop? A for each loop is really just a
cursor by another name. Without seeing what you are doing it's
difficult to advise otherwise.
David Portas
SQL Server MVP
|||Thanks for the hint -> set based technique.
I've found what I've been looking for:
http://groups.google.com/group/micro...924a2b73888ca4
|||I got another problem.
I got this dynamic sp:
create procedure spFindData()
as
declare @.statement varchar(255),@.intYear int,intCount int
set @.intyear = 2010
while @.intyear >=2004
begin
select @.statement = "select count(*) from sales' + @.intYear
-- I want to assign the result of @.statement to @.intCount, how?
if (@.intCount>0)
begin
return @.intYear
end
@.intYear = @.intYear -1
end

Main Report Page with multiple visible/nonvisible sub reports

Hi,
I have mainly the problem, that when I use a lot of subreports and
I get error messages by the report server.
Even I think it will not a got solution in matter of performance.
But how can I create a report, which will be configured by a
a field record ?
Up to now, I only use the sub reports with the function "hide " sub report.
The main page of the report will have the parameter selection box, to select
the datasets.
Depending on a record field information, I show only the depending reports,
which are shown different datas from different tables. Even different chart
data.
But I get the impression, that internally all query will be execute, even,
when the
visible flag is set to "hidden".
Need help for a better solution.
Best regards
RichardOn Nov 26, 8:59 am, Ritchie <Ritc...@.discussions.microsoft.com> wrote:
> Hi,
> I have mainly the problem, that when I use a lot of subreports and
> I get error messages by the report server.
> Even I think it will not a got solution in matter of performance.
> But how can I create a report, which will be configured by a
> a field record ?
> Up to now, I only use the sub reports with the function "hide " sub report.
> The main page of the report will have the parameter selection box, to select
> the datasets.
> Depending on a record field information, I show only the depending reports,
> which are shown different datas from different tables. Even different chart
> data.
> But I get the impression, that internally all query will be execute, even,
> when the
> visible flag is set to "hidden".
> Need help for a better solution.
> Best regards
> Richard
In terms of the auto-execution of datasets, I'm not sure; however, you
might want to include all the subreports in the main report as table
controls (where applicable). Then toggle the visibility of the
individual table controls (via: right-clicking the top-left corner of
the table control >> select Properties >> select Visibility >> select
'Visibility can be toggled by another report item' >> then select a
control from the list below 'Report item:'). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

mailto: with multiple email addresses = Invalid URI

I'm trying to create a mailto link with muliple email addresses like
"mailto:name@.email1.com;name2@.email2.com;name3@.email3.com" and cannot
get it working. any ideas on how to get this working?
Thanksgot it working...as an expression w/in the navigation tab of Advanced
Textbox Properties
=IIf( IsNothing(Fields!EmailGroupList.Value),
Nothing,("javascript:void(window.open('mailto:" &
Fields!EmailGroupList.Value & "','_blank'))"))
this opens up the mail client and populates the To: field with multiple
addresses.

Mailing labels for dummies

Ok, I'm an idiot. Does anyone out there have some step by step instructions for creating a report that will do mailing labels. I've got multiple columns on my report but I end up with the contents of each column being the same across each grouping/row. In other words for each record read, since I have three columns, I get three occurances of each customer and address until all the data selected for that row is presented, then I go to the next customer which again is repeated across all three columns.

I'm sorry I'm so stupid, but I've looked at other entries on the forum, and I just can't figure it out. We are trying to get out from underneith Business objects thumb and any help would be greatly appreciated. Thanks! - Eric -

Carpet King,

I think what you are looking for is the Multiple Column type report. you will set this up by using going into the report properties and setting the number of colums to 3.

Hope this helps

|||

Thanks Bret for the input. Your posting helped me to figure out where to look and change the properties. It through me for a while 'cause the pre-view doesn't show the multiple columns. As a result, after reading your input and making the change, it took a while to figure out that what you told me to do really did fix the problem.

The SQL reporting services is still Marginal (at best!) in creating labels. I'm having a heck of a time getting things evened out so that they will print on my labels correctly. I'm still having a lot of problems getting it set up just right so that information from one label doesn't carry over to another.

None the less, thank you very much for your help! I don't want you to feel like I am shooting the messanger, just venting. Sorry. :B - Eric -

|||

Hello,

I am working on mailing labels and I have the 3 columns, but now I am trying to get them to print across the page,rather than down the page. (Trying to prevent wasting labels by going down the page.) Below is an example of what I need:

1 2 3 4

NOT

1

2

3

4

Any ideas would be greatly appreciated!

mbsqlnewbie

Mailing labels for dummies

Ok, I'm an idiot. Does anyone out there have some step by step instructions for creating a report that will do mailing labels. I've got multiple columns on my report but I end up with the contents of each column being the same across each grouping/row. In other words for each record read, since I have three columns, I get three occurances of each customer and address until all the data selected for that row is presented, then I go to the next customer which again is repeated across all three columns.

I'm sorry I'm so stupid, but I've looked at other entries on the forum, and I just can't figure it out. We are trying to get out from underneith Business objects thumb and any help would be greatly appreciated. Thanks! - Eric -

Carpet King,

I think what you are looking for is the Multiple Column type report. you will set this up by using going into the report properties and setting the number of colums to 3.

Hope this helps

|||

Thanks Bret for the input. Your posting helped me to figure out where to look and change the properties. It through me for a while 'cause the pre-view doesn't show the multiple columns. As a result, after reading your input and making the change, it took a while to figure out that what you told me to do really did fix the problem.

The SQL reporting services is still Marginal (at best!) in creating labels. I'm having a heck of a time getting things evened out so that they will print on my labels correctly. I'm still having a lot of problems getting it set up just right so that information from one label doesn't carry over to another.

None the less, thank you very much for your help! I don't want you to feel like I am shooting the messanger, just venting. Sorry. :B - Eric -

|||

Hello,

I am working on mailing labels and I have the 3 columns, but now I am trying to get them to print across the page,rather than down the page. (Trying to prevent wasting labels by going down the page.) Below is an example of what I need:

1 2 3 4

NOT

1

2

3

4

Any ideas would be greatly appreciated!

mbsqlnewbie

LTRIM in grouping

Hello All,

I am trying to ltrim a portion of multiple fields in a grouping. I am able to do it for one of them, but unfortunately there are several I have to do it for. If I use the following expression, it works for that one.

Code Snippet

=iif(Fields!BankNumber.Value="083"and Fields!TestName.Value="Inquiry Menu - Bank 083",LTRIM("Inquiry Menu"),Fields!TestName.Value)

However, if I try and do it for more than one it errors out. For example...

Code Snippet

=iif(Fields!BankNumber.Value="083"and Fields!TestName.Value="Inquiry Menu - Bank 083",LTRIM("Inquiry Menu"),Fields!TestName.Value)

OR iif(Fields!BankNumber.Value="083"and Fields!TestName.Value="Search Menu - Bank 083",LTRIM("Search Menu"),Fields!TestName.Value)

OR iif(Fields!BankNumber.Value="083"and Fields!TestName.Value="SEAX - Bank 083",LTRIM("SEAX"),Fields!TestName.Value)

Is there another way to arrange this so I can LTRIM each field group seperately?

Thanks,

Clint

It is not clear what exactly you are trying to do, but I'll take a crack at it. If I miss the mark, point me in the right direction.

I think that what you want is to remove the " - Bank 083" string from the end of your testname when the banknumber =083

The expression that you wrote does not exactly do that, and anyway there's a simpler way:

=Replace(Fields!TestName.Value, " - Bank 083", "")

What this does is return the testname with any instance of " - Bank 083" replaced with nothing.

What you wrote (in the first case) says "If the banknumber is 083 and the testname is "Inquiry Menu - Bank 083" then use the string "Inquiry Menu" with no leading spaces, otherwise use the testname.

So the value of that expression is either a literal string, or testname, which is also a string. You got a syntax error because there is no meaning to the expression "string1" OR "string2"

I took a few liberties in assuming characteristics of your data with the solution I offered. Specifically, I assume that only banknumber 083 has " - Bank 083" at the end of the testname.

You only mention the case of this one bank... Are all the other values for testname formatted correctly? If not, you'll probably want to make a more general solution using InStr and SubStr