We help IT Professionals succeed at work.

Gathering summary data based on two groupings

I am attempting to pull some logging information into a summary format.
The main fields I'm reading are the entry date, and the IP address.  I'm attempting to collect totals for the connect time.
Unfortunately, the data isn't exactly in order, so that presents my challenge.

I want to be able to insert a record in my database for each date that each IP occurs.
Example:

8/1/2011  1.2.3.4  60
8/1/2011  1.2.3.5 180
8/1/2011  1.2.3.4  60
8/2/2011  1.2.3.8  240
8/2/2011  1.2.3.5  100
8/2/2011  1.2.3.4  75

So based on the above data, I would want to have only 2 rows inserted for 8/1/2011:
  8/1/2011  1.2.3.4  120 (total of each entry of value 60)
  8/1/2011  1.2.3.5  180
but 3 rows entered for 8/2/11:
  8/2/2011  1.2.3.8  240
  8/2/2011  1.2.3.5  100
  8/2/2011  1.2.3.4  75

I've tried looping through the records but I'm having trouble catching when the date changes and/or the ip changes.  Granted, there may be times when both change as well.

I'd love sample code, but I think my biggest obstacle is the logic required...
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
One option is to use a dictionary object with date and ip combined (separated with space may be) as the key and the connect time as the value. Then if the key already exists, add to the connect time, else insert the item to dictionary.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
Can you use Linq?
CERTIFIED EXPERT
Top Expert 2007

Author

Commented:
I'll give the dictionary a shot - thanks
No to the Linq question
CERTIFIED EXPERT
Top Expert 2007

Author

Commented:
So, if I use a dictionary...
I see it working this way:

dictionary.add ("8/1/2011  1.2.3.4", 60)
dictionary.add ("8/1/2011  1.2.3.5", 180
dictionary.add ("8/1/2011  1.2.3.4",  60) '<<<fails here, but I need to sum the total connect time to be 120

Or am I not following your suggestion correctly?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
You would do

If exists Then
   dictionary(item) = dictionary(item) + newvalue
else
   dictionary.add()
end if
CERTIFIED EXPERT
Top Expert 2007

Author

Commented:
Oic.  Ok - I'll try it out on Monday and let you know.  Thanks!
CERTIFIED EXPERT
Top Expert 2007

Author

Commented:
Thanks much.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.