sirbounty
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...
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...
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?
ASKER
I'll give the dictionary a shot - thanks
No to the Linq question
No to the Linq question
ASKER
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oic. Ok - I'll try it out on Monday and let you know. Thanks!
ASKER
Thanks much.