need help with one more outer join sql query in coldfusion
Hello all ,
This may sound familiar as I asked something related earlier today. That problem was solved thx to you all.
But now I'm stuck again. Could you please help.
I have a table with 300,000 rows containing IP addresses and page names. One row for every page request. This keeps track of certain traffic on a web site.
I want to identify any IPs that have unusually high traffic i.e. more than 500 rows worth. Query 1 (below) does that OK:
Query 1:
<cfquery name="FindHighTrafficIP" datasource="#Dynamic_AdRot
SELECT COUNT(UserIP) AS Expr1, UserIP, Count(PageName) As PageNameQty
FROM dbo.UserTracking
Where (VisitDateTime >= #StartDateTime#) AND (VisitDateTime <= #EndDateTime#)
GROUP BY UserIP
HAVING (COUNT(UserIP) > #Cutoff#) note "cutoff" = 500
ORDER BY Expr1 DESC
</cfquery>
The result is 58 IP addresses that all have very high traffic. Things like Google bot etc.
Now I want to bounce that off of a table of 6,000 previously identified IPs. I would like the output of that query to be the subset of the 58 new IPs that are not yet in the 6,000 IPs.
How do I do that?
Thx
This may sound familiar as I asked something related earlier today. That problem was solved thx to you all.
But now I'm stuck again. Could you please help.
I have a table with 300,000 rows containing IP addresses and page names. One row for every page request. This keeps track of certain traffic on a web site.
I want to identify any IPs that have unusually high traffic i.e. more than 500 rows worth. Query 1 (below) does that OK:
Query 1:
<cfquery name="FindHighTrafficIP" datasource="#Dynamic_AdRot
SELECT COUNT(UserIP) AS Expr1, UserIP, Count(PageName) As PageNameQty
FROM dbo.UserTracking
Where (VisitDateTime >= #StartDateTime#) AND (VisitDateTime <= #EndDateTime#)
GROUP BY UserIP
HAVING (COUNT(UserIP) > #Cutoff#) note "cutoff" = 500
ORDER BY Expr1 DESC
</cfquery>
The result is 58 IP addresses that all have very high traffic. Things like Google bot etc.
Now I want to bounce that off of a table of 6,000 previously identified IPs. I would like the output of that query to be the subset of the 58 new IPs that are not yet in the 6,000 IPs.
How do I do that?
Thx
ASKER
ok, here is the code I am trying to get to work
<cfquery name="FindNewHighTrafficIP
SELECT COUNT(t1.UserIP) AS Expr1, t1.UserIP, Count(t1.PageName) As PageNameQty, t2.IP
FROM CP_AdRotator.dbo.UserTrack
CP_VisitorsDB.dbo.Excluded
Where (t1.VisitDateTime >= #StartDateTime#) AND (t1.VisitDateTime <= #EndDateTime#)AND t2.IP IS NULL
GROUP BY t1.UserIP
HAVING (COUNT(t1.UserIP) > 500)
ORDER BY Expr1 DESC
</cfquery>
<cfquery name="FindNewHighTrafficIP
SELECT COUNT(t1.UserIP) AS Expr1, t1.UserIP, Count(t1.PageName) As PageNameQty, t2.IP
FROM CP_AdRotator.dbo.UserTrack
CP_VisitorsDB.dbo.Excluded
Where (t1.VisitDateTime >= #StartDateTime#) AND (t1.VisitDateTime <= #EndDateTime#)AND t2.IP IS NULL
GROUP BY t1.UserIP
HAVING (COUNT(t1.UserIP) > 500)
ORDER BY Expr1 DESC
</cfquery>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you
its working now
its working now
Open in new window
I don't have both your queries so I can't construct the exact query but I hope you get what I mean. Oh, and you can't have the ORDER BY inside one of the inner queries, move things like that to the outer query if needed.