JET SQL Reporting

Well... my requests for assistance on JET SQL to the community have been very successful thus far. My last request on information relating to multiple joins generated a lot of excellent feedback. I would like to recognize the following individuals for their assistance:

Leigh Pointer, Chris Burtt-Jones, Stefan Nilsson, Jon Henning, Fletcher Klap, Bruce Onder, Armin Woworsky, Loretta Johnson, Andy Baron

Now I have an even larger task...

The GetSiteLog stored procedure contains a variety of SELECT queries for manipulating the Site Log in different ways. As part of the DAL abstraction it needs to be broken into independent queries for each report type. This task is fairly straight forward. The tough part is getting the JET SQL syntax converted for the complicated COUNT, DISTINCT, GROUP BY, and CASE logic. I will post some of the queries below. I do not expect one person will be able to crack them all but who knows... we have some very talented developers in our midst...

CREATE PROCEDURE GetSiteLog1 ( [@PortalId] int, [@PortalAlias] Text(50), [@StartDate] datetime, [@EndDate] datetime )
AS
SELECT DateTime AS Date,
count(*) AS Views,
count(distinct SiteLog.UserHostAddress) AS Visitors,
count(distinct SiteLog.UserId) As Users
FROM SiteLog
WHERE PortalId = [@PortalId]
AND SiteLog.DateTime BETWEEN [@StartDate] AND [@EndDate]
GROUP BY DateTime
ORDER BY Date desc;
GO

... sorry the Blog module has size limitations... I will post some more in a seperate item...