Maybe this will make more sense?

Question

Post reply

Maybe this will make more sense?

jesus4u

Grasshopper

Points: 23

January 29, 2003 at 10:23 am

#101366

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=' + CONVERT(varchar(250), LEFT(NEWID(), 7))

FROM WebLog W INNER JOIN 

(SELECT IP, (ID), SUBSTRING(PageRequested, CHARINDEX('?tid=', PageRequested), 50) as PR FROM WebLog 

WHERE  [Date]=(SELECT max([Date]) FROM WebLog) AND PageRequested LIKE '%?tid=%'

GROUP BY IP, (ID), PageRequested)

as Y 

ON W.ID=Y.ID

WHERE PageRequested LIKE '%crmresourcesthankyou.asp%'

id=code>

id=code>

Alex Polajenko

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

jpipes

SSChampion

Points: 12230

January 29, 2003 at 11:20 am

#447505

what is the "(ID)" for in the derived table???

jesus4u Grasshopper Points: 23 More actions · Answer 2

quote:
what is the "(ID)" for in the derived table???

Sorry, it was left over from a previous form of the query. Not needed. 🙂

Alex Polajenko

Antares686 SSC Guru Points: 125444 More actions · Answer 3

I would think it better to grab from within the app code and add a column to store the tid istead of trying to do an after the fact type of deal.

Is there a specific reason for doing it this way and is this just so you can tie common records?

jesus4u Grasshopper Points: 23 More actions · Answer 4

It is just so I can tie common records..

What do you mean by your previous comment?

Alex Polajenko

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Ok I make some assumptions here if you have the source code for the app that inserts this.

I have an app where I used to log all activity with the connections due to early stages of life and we wanted to track where and when errors occurred.

The key was some machines were being used by multiple people so I needed to be able to see who it was uniquely. And to be able to ID the connection even if made on same day. I only see the date and IP in yours so I would think potential to mismap.

For me I decided to store something unique for every connection on my log inserts, this would allow me to uniquely ID all related connections. What I found was the session ID. So I would use the following

Right(Request.ServerVariables("HTTP_COOKIE"), 33)

Which gives me the unique session connection. The value is of course longer than 33 but the first portion is actuall the servers value.

By adding a CHAR(33) column and inserting the piece I got from ASP, which stays in effect the duration of the session, I am guaranteed to be able to tell what items occurred within the same session.

Hope that makes sense.