I have a program that has a list of traders who have executed trades through a list of brokers. I have to sum the trade quantities only if the stock symbol is the same and there is a negative position and positive position executed from different brokers.
So, for instance,
The list could look like this:
Trader Broker Symbol Quantity
Bob MS IBM -100
Bob GS IBM 200
Bob MS AAPL 50
Bob GS AAPL 75
Jane MS IBM 100
Jane GS IBM 200
So, in this scenario - I would sum up only Bob's quantities for IBM because there is a negative position in one broker and a positive position with another broker only for that symbol. His Apple position would be ignored and since Jane has no negative positions she will also be ignored.
Is there a LINQ query that could return the traders who would need have their quantities summed or would I have to resort to looping constructs and some way of keeping track of whether the symbols are the same and the brokers are different, etc.?
Since this list is in a CSV file - I was even thinking of accessing the file via OleDbConnection and running a SQL query that would get me the data I need. But I wasn't able to construct a proper query.
Anyone have any good ideas on how to approach this problem?