Link to home
Start Free TrialLog in
Avatar of sirbounty
sirbountyFlag for United States of America

asked on

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...
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Can you use Linq?
Avatar of sirbounty

ASKER

I'll give the dictionary a shot - thanks
No to the Linq question
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?
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oic.  Ok - I'll try it out on Monday and let you know.  Thanks!
Thanks much.