September 27, 2001 at 11:19 am
I am creating a a web based smtp log file reporting system. I want to create a procedure that takes a column (ip) and counts the number of times a particular ip appears, then adds a temporary column to the table for that particular ip to store the count. Ultimately there will be up to 30+ temporary columns.I'm not sure how to go about doing it or if that is the best way. Here is what I have that does not seem to work the way I want it too, but may give a better idea as to what I'm trying to accomplish.
CREATE PROCEDURE SP_SMTPSTATS AS
SELECT count(ip) mcd
FROM test
where ip = '10.0.0.5'
count(ip) mcdd
from test
where ip = '10.0.0.4'
GO
September 27, 2001 at 11:54 am
ok, you didn't give a whole heck of a lot of info, but here's a suggestion.
Rather than add a column to a table to store IP counts, use a separate table.
So, your SMTP log could be uploaded into a temp table, the statistics then calculated and stored into a permant SMTPStats table. So, in this example, I have a assumed that your smtp log file has already been uploaded/converted into a table called SMTP_LOG_TEMP, and that the table that contains all your smtp statistics is called SMTP_STATS. SMTP_STATS in this example has 2 fields: IP_ADDRESS, and IP_COUNT.
-- First, get the IPs, and their counts from the latest log file
INSERT INTO #SMTP_STATS_WORK
SELECT IP, Count(IP) as LATEST_COUNT
FROM SMTP_LOG_TEMP
GROUP BY IP
-- Second, update the SMTP_STATS table
UPDATE SMTP_STATS
SET a.IP_COUNT = a.IP_COUNT + b.LATEST_COUNT
FROM SMTP_STATS a, #SMTP_STATS_WORK b
WHERE a.IP_ADDRESS = b.IP
-- Lastly, insert any IPs from this run, that weren't in the SMTP_STATS table already
INSERT INTO SMTP_STATS (IP_ADDRESS, IP_COUNT)
SELECT IP, LATEST_COUNT
FROM #SMTP_STATS_WORK
WHERE IP not in (SELECT IP_ADDRESS FROM SMTP_STATS)
Good luck!
Matthew Mamet
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply