UPDATE W 	
	SET PageRequested=PageRequested + '?tid=' + CONVERT(varchar(250), LEFT(NEWID(), 7))
	FROM WebLog W INNER JOIN 
		(SELECT IP, Min(ID) AS MinID FROM WebLog 
		WHERE  [Date]=(SELECT max([Date]) FROM WebLog) 
		GROUP BY IP)
			
	as Y 
	ON W.ID=Y.MinID 

	UPDATE W 
	SET PageRequested= 
	CASE 
	WHEN 
		PageRequested LIKE '%crmresourcesthankyou.asp%' THEN 
			PageRequested + '?tid=' + CONVERT(varchar(250), LEFT(NEWID(), 7))
	ELSE 
		PageRequested 
	END 
	FROM WebLog W INNER JOIN 
		(SELECT IP, (ID) FROM WebLog 
			WHERE  [Date]=(SELECT max([Date]) FROM WebLog) 
			GROUP BY IP, (ID))
	as Y 
	ON W.ID=Y.ID

quote:

---

Could you explain the conditions under which the second update executes within the first?

If you mean when the first update occurs on a row, the second update also occurs on a row, this query will do that, but I don't think it's what you want?

UPDATE W

SET PageRequested=

CASE WHEN

PageRequested + '?tid=' + CONVERT(varchar(250), LEFT(NEWID(), 7))LIKE '%crmresourcesthankyou.asp%' THEN

PageRequested + '?tid=' + CONVERT(varchar(250), LEFT(NEWID(), 7))+ '?tid=' + CONVERT(varchar(250), LEFT(NEWID(), 7))

ELSE

PageRequested + '?tid=' + CONVERT(varchar(250), LEFT(NEWID(), 7))

END

FROM WebLog W INNER JOIN

(SELECT IP, (ID) FROM WebLog

WHERE [Date]=(SELECT max([Date]) FROM WebLog)

GROUP BY IP, (ID))as Y

ON W.ID=Y.ID

---

Thanks but no. This is what I am after...

Take a look at this pic and this is what I am trying to do. The code I have so far is below it.



Code:

	UPDATE W 
	SET PageRequested = PageRequested + '?tid=' + PR
	FROM WebLog W INNER JOIN 
		(SELECT IP, (ID), SUBSTRING(PageRequested, (CHARINDEX('?tid=', PageRequested) + 5), 50) as PR FROM WebLog 
		WHERE  [Date]=(SELECT max([Date]) FROM WebLog)
		GROUP BY IP, (ID), PageRequested) as Y 
	ON W.ID=Y.ID
	WHERE PageRequested LIKE '%crmresourcesthankyou.asp%' AND PageRequested LIKE '%' + PR + '%'

Take a look at what the code does:
This is another set of sample data:



Edited by - jesus4u on 01/29/2003 14:41:57

Edited by - jesus4u on 01/29/2003 14:45:47

quote:

---

I might be off base here, but from what I can tell, wouldn't it be easier to modify the ASP pages to pass the TID from page to page? That way you'd have that TID on every record and you could read it or skip it as needed in your queries.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

---

quote:

---

I noticed your reply posted in another thread.

[url]http://sqlteam.com/Forums/topic.asp?TOPIC_ID=23418[/url]

You could delete it and repost it here to keep the thread clean.
------------------------------------------------------

Here we go (I haven't tried this)

UPDATE WebLog
SET PageRequested =
REPLACE( (SELECT PageRequested FROM WebLog W2 WHERE W2.IP = IP AND W2.[Date] = [Date] AND LEFT(W2.PageRequested, 13) ='/hr/login.asp' ),
'/hr/login.asp', PageRequested)

FROM WebLog
WHERE [Date] = (SELECT MAX([Date]) FROM WebLog) -- Today's records only
AND PageRequested = '/crmresourcesthankyou.asp' -- Update only this PageRequested String

Risks with the above query include the destructive effect of the update if no record is found with /hr/login.asp . A CASE statement could be introduced to handle this easily.

Another way to do it would be to find the paired rows in the From WebLog, then do the update. This is a little messier at first:

Update Weblog
SET PageRequested = REPLACE(W2.PageRequested, '/hr/login.asp', PageRequested)
FROM WebLog W1
INNER JOIN WebLog W2
ON -- See below for the ON
WHERE [Date]=(SELECT max([Date]) FROM WebLog)

It reads easier if the ON is omitted.

ON W1.IP = W2.IP AND W1.PageRequested = '/crmresourcesthankyou.asp'
AND W2.PageRequested = '/hr/login.asp'


I am sure there are syntax and maybe context issues with this query, but they should be debug(able).

HTH,

Sam

---

quote:

---

This is gonna sound dumb, but WHY exactly are you going through all of this anyway?

Yeah, I know that this is data being imported from a web log, but why are you modifying it in this way? What is this whole process in service of? Why do you have the tables defined the way they are here?

http://www.sqlteam.com/forums/topic.asp?ARCHIVE=&whichpage=2&TOPIC_ID=23300

I know you're trying to match these to user ID's, but WHY? Why are the tables set up the way they are, and can they be changed? Why import a ton of rows from the web log and only change certain ones? Why keep the rows that aren't changed? And if you DO need all of the rows, why would changing certain ones not contradict the purpose of the table? Changing these rows makes them invalid, in a way, when compared to the other rows.

I can't help thinking that one of the reasons it seems to be so difficult to get what you want is that we either don't understand the idea behind it, or that maybe the way your data is structured simply works against trying to accomplish the goal (WHAT is the end result desired anyway?) I'm not saying what you have is wrong or bad, but more background on how the whole system is set up might help to illuminate something in such a way that makes the answer easier. Something that describes what each table does, why it's designed that way, etc. I have to say I'm at a complete loss as to the role of the User and UserDetails tables. I know you explained them somewhat, but I don't know what they're supposed to do exactly.

---

quote:

---

This is gonna sound dumb, but WHY exactly are you going through all of this anyway?

Yeah, I know that this is data being imported from a web log, but why are you modifying it in this way? What is this whole process in service of? Why do you have the tables defined the way they are here?

http://www.sqlteam.com/forums/topic.asp?ARCHIVE=&whichpage=2&TOPIC_ID=23300

I know you're trying to match these to user ID's, but WHY? Why are the tables set up the way they are, and can they be changed? Why import a ton of rows from the web log and only change certain ones? Why keep the rows that aren't changed? And if you DO need all of the rows, why would changing certain ones not contradict the purpose of the table? Changing these rows makes them invalid, in a way, when compared to the other rows.

I can't help thinking that one of the reasons it seems to be so difficult to get what you want is that we either don't understand the idea behind it, or that maybe the way your data is structured simply works against trying to accomplish the goal (WHAT is the end result desired anyway?) I'm not saying what you have is wrong or bad, but more background on how the whole system is set up might help to illuminate something in such a way that makes the answer easier. Something that describes what each table does, why it's designed that way, etc. I have to say I'm at a complete loss as to the role of the User and UserDetails tables. I know you explained them somewhat, but I don't know what they're supposed to do exactly.

---

quote:

---

You haven't mentioned the problem I pointed out in my last post in this thread. The IP address does not track an individual user session. More than 1 user may use the same IP or the same user may have more than 1 session a day. If the rows don't have session identity, then the rows can't be correlated at the session level.

Sam



Edited by - SamC on 01/30/2003 09:55:31

---

UPDATE W 
	SET PageRequested = PageRequested + Y.PR
	FROM WebLog W 
	INNER JOIN 
	 (SELECT
	   IP,
	   SUBSTRING(PageRequested,CHARINDEX('?tid=',PageRequested),50) as PR 
	  FROM WebLog l
	  WHERE PageRequested LIKE '%?tid=%'
	    AND [Date] =
	     (SELECT Max([Date])
	      FROM WebLog
	      WHERE IP=l.IP)) as Y 
	  ON W.IP=Y.IP
	WHERE W.PageRequested LIKE '%crmresourcesthankyou.asp%'

quote:

---

Also we are not that concerned with more than one user coming in from the same IP in a given day. We will lump them together.

---

quote:

---

Good luck with that query. It doesn't meet several of the criteria you mentioned above, and it does break things.

1 - It performs the update on the entire table, not just the most recent day.

Alex responds: There will be only one day at a time anyway. My sql job will delete after it deals with the day because I will then move the data off to another table.

2 - It will append another "?tidxxxx" string to the thank you row every time it is run.

Alex responds: This is dealt with the answer above.

3 - From your prior post, you seem to think the IDENTIY column is going to help you separate sessions from IP address?

Alex responds: No I don't. It is just that we don't mind treating the same IP in a day as the same person.

4 -

quote:

---

Also we are not that concerned with more than one user coming in from the same IP in a given day. We will lump them together.

---

We've shown earlier in this post that the same IP address does login more than once a day (same user or different user doesn't matter). Each Login will have a different tid (you haven't contested this point). When you say you are not concerned, it doesn't tell us what action to take. I can't tell SQL to "not be concerned".

Alex responds: If you read our specs the same user needs to have different TID's to find out if they are sending us traffic from a user generated link or from just regular browsing of the sites.

As things stand, you have multiple rows, same day, same IP addresse, but different TIDs, just grab any one of the TIDs for an IP that day (this is what you state you have no concern about) and place it on all the thank you rows for that IP address?

Hope you find the right solution.

Sam

---