Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
adweigert
Starting Member
22 Posts |
 Posted - 2003-10-09 : 11:32:50
|
| so what do you do when your spam logger keeps track of each email and who they go to so that at a particular date/time and from address it could be one email but with multiple recipients?well you need the total number of real emails sent ... not the total of recipients that were spared from spam ...*warning* this is really ugly but i'm proud of it :)create view [SPAM_ActualSuspectedTotals] as select top 100 percent coalesce(t1.[date], t2.[date]) AS date, isnull(t1.total, 0) + isnull(t2.total, 0) as total from (select convert(varchar, [datetime], 101) as [date], count(*) as total from (select [datetime], fromaddress, count(*) as toaddresses from spam_log where type = 'Suspected' group by [datetime], fromaddress) As s1 group by convert(varchar, [datetime], 101)) as t1 left outer join (select convert(varchar, [datetime], 101) as [date], sum(s2.total) as total from (select sl.[datetime], sl.fromaddress, sl.toaddress, count(*) as total from spam_log as sl inner join (select [datetime], fromaddress, count(*) as toaddresses from spam_log where type = 'Suspected' group by [datetime], fromaddress having count(*) > 1) as sc on sl.[datetime] = sc.[datetime] and sl.fromaddress = sc.fromaddress group by sl.[datetime], sl.fromaddress, sl.toaddress having count(*) > 1) as s2 group by convert(varchar, [datetime], 101)) As t2 on t1.[date] = t2.[date] order by coalesce(t1.[date], t2.[date]) desc |
|
|
|
|
|
|
|
this is a thing of beauty