More queries...

send answers to shaunw1@shaw.ca

CREATE PROCEDURE GetSiteLog2 ( [@PortalId] int, [@PortalAlias] Text(50), [@StartDate] datetime, [@EndDate] datetime )
AS
SELECT SiteLog.DateTime,
case when SiteLog.UserId is null then null else Users.FirstName + ' ' + Users.LastName end AS [Name],
case when SiteLog.Referrer like '%' + [@PortalAlias] + '%' then null else SiteLog.Referrer end AS Referrer,
case
when SiteLog.UserAgent like '%MSIE 1%' then 'Internet Explorer 1'
when SiteLog.UserAgent like '%MSIE 2%' then 'Internet Explorer 2'
when SiteLog.UserAgent like '%MSIE 3%' then 'Internet Explorer 3'
when SiteLog.UserAgent like '%MSIE 4%' then 'Internet Explorer 4'
when SiteLog.UserAgent like '%MSIE 5%' then 'Internet Explorer 5'
when SiteLog.UserAgent like '%MSIE 6%' then 'Internet Explorer 6'
when SiteLog.UserAgent like '%MSIE%' then 'Internet Explorer'
when SiteLog.UserAgent like '%Mozilla/1%' then 'Netscape Navigator 1'
when SiteLog.UserAgent like '%Mozilla/2%' then 'Netscape Navigator 2'
when SiteLog.UserAgent like '%Mozilla/3%' then 'Netscape Navigator 3'
when SiteLog.UserAgent like '%Mozilla/4%' then 'Netscape Navigator 4'
when SiteLog.UserAgent like '%Mozilla/5%' then 'Netscape Navigator 6+'
else SiteLog.UserAgent
end AS UserAgent,
SiteLog.UserHostAddress,
Tabs.TabName
FROM SiteLog
LEFT JOIN Users ON SiteLog.UserId = Users.UserId
LEFT JOIN Tabs ON SiteLog.TabId = Tabs.TabId
WHERE SiteLog.PortalId = [@PortalId]
AND SiteLog.DateTime BETWEEN [@StartDate] AND [@EndDate]
ORDER BY SiteLog.DateTime desc;
GO

CREATE PROCEDURE GetSiteLog6 ( [@PortalId] int, [@PortalAlias] Text(50), [@StartDate] datetime, [@EndDate] datetime )
AS
SELECT datepart(hour,DateTime) AS Hour,
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 datepart(hour,DateTime)
ORDER BY Hour;
GO