I have a side problem I've been fighting with at work, and I need to
just get it finished, so I decided to try Google Answers. :)

I have a table containing processesed statistics for usage of some
websites.  The entries in the table are uniquely keyed by
(UserID,Site,Date).  Thus, if a user visits two sites one day and one
site the next day, the user will have a total of three entries in the
table.

My problems came from trying to find the best way to easily roll up
and report totals on this data.  Given just the data in the example
above, the total number of users for day one would be 1 and the total
users for day 2 would be 1, but you cannot simply sum the days to get
a weekly total, you need a sum of distinct users, and SQL Server 2000
does not allow distinct aggregates in a rollup or cube.  Below is a
rough query for getting the numbers I'm interested in for a day, I'd
like a single query (can make any use of temp tables, views, functions
and sprocs) that can extend this reporting to provide the daily
numbers with a rollup (cube might be preferable) by week and month for
each site and all sites, keeping distinctness of users in mind.


Here are links to a sample table and data:
http://www.yipyip.com/test/sample_table.sql
http://www.yipyip.com/test/sample_data.csv

Daily totals example query:

select Site, Date,
       count(UserID) as NumUsers,
       sum(cast(IsSpecial as tinyint)) as 'NumSpecialUsers',
       sum(PageViewsA) as TypeAPageViews,
       sum(case when IsSpecial = 1 then PageViewsA else 0 end)
         as 'SpecialUserTypeAPageViews',
       sum(case when ClicksA > 0 then 1 else 0 end)
         as 'NumUsersWithTypeAClicks'
from Sample
group by Site, Date