Updating a table

[Guest_imported]

My goal is to update a table. Here is what I'm working with.

This stored procedure is the one called to pull the data requested.I'll explain what it does.

select strservername,strqpname, "0" from
(
SELECT Max(convert(varchar(10),dtexists,101)) as date ,strservername,strqpname FROM tblQPCounts WHERE strservername = @servername group by strservername,strqpname
) qp
where qp.strqpname not in
(
select strquickplace from
( SELECT strServer, convert(varchar(10),dtmhitday,101) as date, Sum(lngNoQPHits) AS HitSum,strquickplace FROM tblRollQPnameHits WHERE (dtmhitday between @startdate AND @enddate) AND strServer = @servername GROUP BY strServer, convert(varchar(10),dtmhitday,101),strquickplace
) hits
)
and strqpname not like "%/main.nsf%" group by qp.strservername, qp.strqpname order by qp.strservername, qp.strqpname

The red one is the first "temporary" table that is created the green is the second table. The blue is the code that pulls the data from the comparison of the two temporary tables. So it's saying make TABLE1 and make TABLE2 then compare them and display the "strqpname" that is not in both tables.

Now the problem I'm running into is there are entries that are in TABLE1 and that are in TABLE2 but these entries are represented differently in TABLE1 so the "blue" code assumes it's data that is supposed to be displayed. THe reason for this is becuase on the Domino side an extra /main.nsf is appended to some of these entires . I have figured out code that , strips out those characters. See below:

if(select count(left(strqpname,patindex("%/main.nsf%",strqpname)- 1))
from tblqpcounts
where strqpname like "%/main.nsf%" and strservername = "crdquickplace01&quot > 0
select (left(strqpname,patindex("%/main.nsf%",strqpname)- 1))
from tblqpcounts
where strqpname like "%/main.nsf%" and strservername = "crdquickplace01"
else
SELECT Max(convert(varchar(10),dtexists,101)) as date ,strservername,strqpname FROM tblQPCounts WHERE strservername = "crdquickplace02" group by strservername,strqpname
To explain in detail, this says if the pattern "/main.nsf" is in the strqpname column then select the entries depending on the where clause and strip out the bad characters if not then just do the else part. What I want to do is incorporate this select statement to strip out the bad characters in the "RED" table mentioned above. I have something like that working now but it isn't working exactly the way I need it to. This is the code that I am talking about:[and strqpname not like "%/main.nsf%"], but again that code is not accurate enough. Below is what I need.

When I replace the reference to the strqpname in the TABLE1 with (left(strqpname,patindex("%/main.nsf%",strqpname)- 1)) I get an error that says invalid column name, and it's referring to the "%/main.nsf%" as the column name that is bad. . See example below, and compare to multicolor code that was first pasted:

select strservername,strqpname, "0" from
(
SELECT Max(convert(varchar(10),dtexists,101)) as date ,strservername,(left(strqpname,patindex("%/main.nsf%",strqpname)- 1)) FROM tblQPCounts WHERE strservername = @servername group by strservername,strqpname
) qp
where qp.strqpname not in

To give you a better understanding of the whole picture this is the process. A Domino Agent parses a log to a text file, the text file is then moved to the SQL side and it's imported into a table via a scheduled job calling a stored procedure. So when the user selects that report the stored procedure above calls that data to be displayed on the web. So, I know it's a bit messy but if you would have any ideas it would greatly help. If the above you find is impossible, then I have a few more options to run by you, all at your very earliest convienience of course.