-- Daily traffic stats from SMTPTracker's logs
-- All versions
-- www.smtptracker.com

declare 
@mescount int,
@spamcount int,
@logdate varchar(10)

IF EXISTS (SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name = 'Stats')
begin
DROP TABLE Stats
end

CREATE TABLE Stats
(
LogDate		smalldatetime,
MessCount	int,
SpamCount	int
)


declare m_cursor cursor for
select convert(varchar,logdate,110) as ld , count(*)
from mlog
where logdate is not null
group by convert(varchar,logdate,110)
order by ld 

set @logdate = ''
open m_cursor
fetch next from m_cursor into @logdate, @mescount
while @@fetch_status = 0
begin
set @spamcount = 0
select @spamcount = count(*) from slog where convert(varchar,logdate,110) = @logdate group by convert(varchar,logdate,110)
insert into Stats select convert(smalldatetime,@logdate,110),@mescount,@spamcount
fetch next from m_cursor into @logdate, @mescount
end

close m_cursor
deallocate m_cursor

select *,cast(((cast(spamcount as float)/cast(messcount as float))*100) as decimal(10,2)) as SpamRatio from stats
order by logdate desc