CJ

The circle of life – ** .Net – Training – MOSS **

  • Past Post

  • Vistor Locations

Matrix problem solving 101

Posted by Clayton James on September 11, 2006

I was working at one of our client sites the other day and saw how a collegue (Mark Daunt) provided a simple solution to a complex problem. I was so impressed and knew that this type of problem solving can be applied to a variety of problems, so I thought that I would blog about it.

Situation:

A request was made to provide a matrix that will dispaly all investment fund combinations and provide a total count for each investor in each combination. They can also only be counted once, so if they belonged in the combination Fund 1 + Fund 2 + Fund 3 then they can’t be counted for the combination Fund 1 + Fund 2. Ouch….I can feel my brain starting to hurt.

Here is an example of what is trying to be acheived:
Funds                                                               Total number of investors
Fund 1           Fund 2             Fund 3
X                                                                       10
X                    X                                                 7
X                                            X                         4
X                    X                      X                         2

So as you see, quite a complex problem until you break it down.

Solution:

This first needs to be broken down so each investor and all of the funds are displayed in a single row. A flag is displayed in each fund column that determines if the investor has invested in the fund or not. e.g

Investors                         Fund 1             Fund 2            Fund 3, etc…
Investor 1                       1                       0                    0
Investor 2                       1                       0                    0
Investor 3                       1                       1                    0

Would you believe that getting the data into this format is the tricky part. Mark mentioned that he used a cursor here as he was using SQL Server 2000, but if he using SQL Server 2005 then he would look at pivot tables.

After this, all you have to do is the following to produced the matrix output…

SELECT Fund1, Fund2, Fund3, count(investors) as TotalInvestors
FROM Funds
GROUP BY Fund1, Fund2, Fund3

Understanding that putting this data into into a single row and then performing a group by is the tricky part. This understanding can be applied to a variety of problems and will hopefully make someone’s life a little simpler when they come across this situation.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: