b177dda7   
   From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   On Tue, 28 Sep 2010 08:58:03 -0700 (PDT), dontinou wrote:   
      
   >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!!   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)