Hobby projects?

Rossj wrote:

Anybody working on any interesting* hobby projects at the moment? I am betting everybody has something going on outside of work hours ... come on spill the beans .. whatcha working on?

use maximum_verbosity

-- Statistics
declare @dt datetime
set @dt = convert(datetime, convert(varchar(12),getdate()))+' 00:00:00'
exec mv4_getstats @dt

-- Searchers
select top 50 time=substring(convert(varchar(20),datetime),13,8), ips.ip, searchresults.domain, engine, query
from searchresults inner join ips on searchresults.userid = ips.id
where query <> 't' and query<>'entries'
order by datetime desc

-- Non-searchers
select top 50 [datetime], activity.ip, request, referrer, activity.domain, username, ips.hits
from activity inner join ips on activity.ip = ips.ip
where activity.referrer <> '' and activity.isspider = 'false' and activity.islocal = 'false' and not (referrer like '%google%' or referrer like '%yahoo%' or referrer like '%msn%' or referrer like '%search%' or referrer like '%damnednice.com%' or referrer like '%zi255.com%') and ips.banlevel <1
order by datetime desc

-- Non-Referred
select top 50 activity.id, activity.datetime, activity.ip, activity.request, activity.domain, ips.username, ips.hits, ips.description
from activity inner join ips on activity.ip = ips.ip
where activity.referrer = '' and activity.isspider = 'false' and activity.islocal = 'false' and not activity.ip like '127%' and not activity.ip like '192%' and not activity.domain='ixdatul.com' and not activity.ip='166.34.132.74' and ips.isspider='false' and ips.banlevel < 1
order by datetime desc

-- Activity by known spammers
select top 50 activity.id, datetime, activity.ip, request, referrer, hits, ips.banlevel, activity.domain
from activity inner join ips on activity.ip = ips.ip
where ips.banlevel > 0
order by activity.datetime desc

-- Errors
select top 50 * from errors
where ex not like '%maximum%resolutions%'
order by datetime desc

Sproc MV4_GetStats looks a good deal different.  It executes other sprocs to pull a specific statistic, and aggregates them into one row for easy retrieval.  This is how I pull the data in the "Statistics" section of www.zi255.com.

CREATE proc [dbo].[MV4_GetStats](@dt DateTime) as begin
declare @t table(
[EntryCount] int,
[UserCount] int,
[BanCount] int,
[HitCount] int,
[SearchCount] int,
[TodaysHitCount] int,
[DistinctDailyIPCount] int,
[SearchesToday] int,
[CommentsMod] int,
[Comments] int,
[FirstEntryDate] datetime,
[LastSpiderTime] datetime);

declare @temp table (x int)
declare @temp2 table (x datetime)

-- Entry Count
insert @temp exec MV4_GetEntryCount
insert into @t  ([EntryCount])
select x from @temp
delete @temp

--User Count
insert into @temp exec MV4_GetUserCount
update @t set [UserCount] = (select x from @temp)
where EntryCount is not null
delete @temp

-- Ban Count
insert into @temp exec MV4_GetBanCount
update @t set [BanCount] = (select x from @temp)
where EntryCount is not null
delete @temp

-- Hit Count
insert into @temp exec MV4_GetHitCount
update @t set [HitCount] = (select x from @temp)
where EntryCount is not null
delete @temp

-- Search Count
insert into @temp exec MV4_GetSearchCount
update @t set [SearchCount] = (select x from @temp)
where EntryCount is not null
delete @temp

-- Last Spider Time
insert into @temp2 exec MV4_GetLastSpiderTime
update @t set [LastSpiderTime] = (select x from @temp2)
where EntryCount is not null
delete @temp2

-- Today's Hit Count
insert into @temp exec MV4_GetTodaysHitCount @dt
update @t set [TodaysHitCount] = (select x from @temp)
where EntryCount is not null
delete @temp

-- Distinct Daily IP Count
insert into @temp exec MV4_GetDistinctDailyIPCount @dt
update @t
set [DistinctDailyIPCount] = (select x from @temp)
where EntryCount is not null
delete @temp

-- Searches Today
insert into @temp exec MV4_GetSearchesToday @dt
update @t
set [SearchesToday] = (select x from @temp)
where EntryCount is not null
delete @temp

-- Comments Requiring Moderation
insert into @temp exec MV4_GetCommentsModCount
update @tset [CommentsMod] = (select x from @temp)
where EntryCount is not null
delete @temp

-- First Post datetime
insert into @temp2 exec MV4_GetFirstEntryDate
update @t set [FirstEntryDate] = (select x from @temp2)
where EntryCount is not null
delete @temp2

-- Total Comments
insert into @temp exec MV4_GetCommentCount
update @t
set [Comments] = (select x from @temp)
where EntryCount is not null
delete @temp

-- Retrieve stats row
select * from @t
end

The whole thing comes back looking like this (clicky).  What, you don't use SQL Server Management Studio to monitor your traffic?