Hi, I have one table with records of internet activity, one hit per
row. I need to generate a report of the top 10 websites of the top 10
users on any given day.

Currently I'm manually running two separate queries:

Query 1: Return top users for a given time period

Use ISAFWLOG
SELECT
clientusername, count(*) as total
From WebProxyLog
Where
logTime between '20100927 10:00:00.000' AND '20100927 23:59:59.999'
AND
ClientUserName <> 'anonymous'
group by clientusername
order by total desc

Query 2: Return top websites for a particular user

Use ISAFWLOG
SELECT
desthost, count(*) as total
From WebProxyLog
Where
logTime between '20100926 10:00:00.000' AND '20100927 23:59:59.999'
AND
ClientUserName = 'username'
group by desthost
order by total desc

What's the easiest way to do this in one query or stored procedure? I
am not very good with SQL so any help would be great.

Thanks!!

On Tue, 28 Sep 2010 08:58:03 -0700 (PDT), dontinou wrote:

Hi dontinou,

First: If the data type of logTime is datetime (and I guess it is), then
the filter will not work as you expect. Since datetime has a precision
of 3/1000 seconds, 23:59:59.999 will round to midnight, and hits on
exactly midnight will be included. For date and time ranges, it is best
to work with a half-open interval:
WHERE logTime >= '2010-09-27T10:00:00.000'
AND logTime < '2010-09-28T00:00:00.000'
(I also changed the format to a format that is guaranteed to be
interpreted the same in every possible locale setting)

For your query, you will probablyt need something like this:

WITH HitsInTimeFrame AS
(SELECT clientusername, desthost, logTime
FROM WebProxyLog
WHERE logTime >= '2010-09-27T10:00:00.000'
AND logTime < '2010-09-28T00:00:00.000')
SELECT TOP (10) desthost, COUNT(*) AS total
FROM HitsInTimeFrame
WHERE ClientUserName IN (SELECT TOP(10) clientusername
FROM HitsInTimeFrame
GROUP BY clientusername
ORDER BY COUNT(*) DESC)
GROUP BY desthost
ORDER BY COUNT(*) DESC;

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Please learn to follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

I would assume that you are using 2008 and know about the DATE date
type. It will save you some work and make the code easier -- you can
use BETWEEN! Hoorah!

WITH Aggregated_Hits
AS
(SELECT client_user_name, dest_host,
COUNT(*) OVER (PARTITION BY client_user_name)
AS client_landing_cnt,
COUNT(*) OVER (PARTITION BY client_user_name, dest_host)
AS client_dest_landing_cnt
FROM WebProxyLog
WHERE log_time BETWEEN '2010-09-27' AND '2010-09-28'
AND client_user_name <> 'anonymous'),

Ranked_Hits
AS
(SELECT client_user_name, dest_host,
client_landing_cnt, client_dest_landing_cnt,
DENSE_RANK() OVER (PARTITION BY client_user_name
ORDER BY client_landing_cnt DESC)
AS client_rank,
DENSE_RANK() OVER (PARTITION BY client_user_name, dest_host
ORDER BY client_dest_landing_cnt DESC)
AS dest_within_client_rank
FROM Aggregated_Hits)

SELECT client_user_name, dest_host,client_rank,
dest_within_client_rank
FROM Ranked_Hits
WHERE client_rank <= 10
AND dest_within_client_rank <= 10;

Untested.