SQL for Accountants

My objective here is not to teach you how to write SQL queries.  Instead I am going to draw some comparisons between every accountants favorite tool, Excel, and SQL commands.  My hope is that the analogies I make will help improve your understanding of SQL.

So to get started lets look at an Excel workbook as the database and the individual worksheets as files within the database.  For this exercise we will limit worksheets to information sets only or tables.  Think of the objective of SQL in terms of pulling relevant and related information into a new worksheet.

The first command I would like to cover is the From command.  In Excel this would be equivalent to referencing a worksheet in a formula.  It directs your formula to which worksheet you would like to pull information.

The next command is the Select statement.  This is going to reference the header information contained in each the worksheet.   Much like using an index function.

The where command is like using a filter.  You will specifically designate the information in the Select statements fields that you would like to retrieve.  This is your criteria.

Next is the Join statement this is where things get a little complicated.  I like to think of this statement as though it is a vlookup.  The only difference is that this information doesn't have to be to the far left or do you have to reference columns.  (If you are an advanced Excel use Index(range,match(field)) may be a better explanation).  Here what is important is that information matches from both tables and you can reference the information against each other.   Joins define the relationship between the tables (worksheets to use our Excel terminology).  Note that you would join using the field names.

Let's cover the OrderBy command next to take a break.  This is the same as using Sort.

Now let's cover the GroupBy command.  This is like using PivotTables.  The main thing to remember here is that this is going to summarize the data by the fields you chose to group.  Now the trick is that every field in the Select statement needs to be included in the group by statement unless it is a calculation or aggregate of the other data.

For now I am going to leave this post as is, I may add and revise some information later.  My head hurts.

Political Flexibility

For a long time we have wanted our political leaders to be rigid by viewing everything from a black and white perspective.  Barack Obama, George Bush and all the presidents during my life time have been rigid in their views.  To demonstrate let's look at the current gay marriage debate.

I am going to move past the gay by nature verse choice debate.  Even if being gay is a choice people should have the freedom to make that choice.  That type of thinking is stupid and a waste of time.

Some people discuss allowing civil unions and others don't want it allow gay marriage at all.  The counterpoint is that neither of these represent equality.  Allowing one group of people to get married and not another is clearly unequal.  Allowing one group civil unions and the other to be married is consider seperate but equal and is viewed as an unacceptable by advocates of gay marriage.  Clearly, the big issue is religion.  In this country religious freedom is very important and most religions are clear that homosexual relationships are sinful.  That is just the way it is and religious freedom is much more important in my opinion than sexual freedom.

Does this mean that both can't exist.  I personally would prefer a world where gay unions are allowed by the government.  Not allowing gay marriage is oppressive.  However, I don't think it right to force institutions to marry a couple they believe to be in a sinful relationship.  The simple solution is for the government to focus on civil unions and allow religious institutions to determine whether they will unite a couple in marriage.  This way one group is not oppressed for the sake of another.

The solution is simple once politics are placed aside and mediation between two competing interests are considered.  A win win solution presents itself in this scenario.  To close off remember that other do not have to accept your lifestyle and that is okay.  That closing comment is intended both ways.

Content and Compensation: Forums Driving Valuable Content

The internet definitely has a value system.  For now I am turning my attention towards forums. 
Contributors to forums used to be able to earn income by making valuable contributions to the forums and adding links to product and blogs.  A lot of contributors began making valueless posts to spam their links all over the place. Many forums turned to a system where they tried to manage cotnributors by developing systems to allowing benefits such as links.  This hasn't worked out really well either.  The recent trend is to not compensate for content.  I personally don't see this working out very well.  There will be a few knowledgeable idiots who will offer up services but this going to lead to many forums becoming very one note. 

It is definitely appropriate to compensate contributors and it is the right thing to do.  Expecting free content for subscribers is not a good way to get the content your subscribers are going to want to read.  The question is how to best get quality content?  The conundrum is that paying people for content attracts opporunists but not paying them doesn't attract content.  Another challenge is that subscribers don't normally don't pay a subscription fee and are often anonymous. 

Often forums treat contributors and subscribers equally but they really do not play the same role on a forum.  Managing the different relationships is something forums can do to improve the content being posted.  This relationship should be distinguisahble to all participants of the blog.  Most subscribers do not participate by posting.  Often the answer to a question has been previously provided if the forum has been able to build content.  The great thing about subscribers is that they want instant gratification.  They don't ask a question if they can get right to the answer right away.

By recognizing this we realize that the job of managing the forum is greatly reduced.  By placing the focus on managing content authors the task becomes plausible.  Content authors should be compensated through sharing ad revenues.  When implementing revenue sharing system tracking becomes very important.when sharing ad revenue with content authors.  Revnue from all advertising must be associated with the author that has attracted the traffic through posting responses to subscriber questions.  This way subscriber get value because their issues have been resolved.  Authors the content providers are compensated for providing valuable information.   Forum developers are compensated by providing the property and are best enabled to manage content.

All issues haven't been discussed so leave a comment to expand on the topic.

Excel GETPIVOTDATA Formula verse SUMIFS Formula

Alright, I am going to expose my immediate bias.  I definitely prefer the GETPIVOTDATA function to the SUMIFS function.

I will discuss the benefits of each formula briefly.  I think that the main benefit of the SUMIFS function is that they are easier to understand and little easier to right.  They are also less likely to return an error result.  For this reason the GETPIVOTDATA function should be nested in an IF function and an ISERROR function.

Example: IF(ISERROR(GETPIVOTDATA(X,X,X,X)),0,ISERROR(GETPIVOTDATA(X,X,X,X))

This basically returns a zero if the GETPIVOTDATA function result is an error or the result if it is not.

This makes writing these formulas a little more difficult.

Now for the reasons I prefer the GETPIVOTDATA function.  The most appealing reason is that it is easier to control the frequency of calculations when working with large tables in a worksheet.  The PivotTable needs to be refreshed before these formulas preced with calculations.  So if you have to modify data  or import new data frequently these don't bog the worksheet down with endless calculation  updates.  You control when the PivotTable is refreshed.  Working from PivotTable's also makes data easier to extract and analyze.  If you spot an issue in a group of data that is being pulled you can simply refer to the PivotTable and double click the number to pull the portion of the table making up your result.

Another advantage is that the GETPIVOTDATA are easier to test and use in templates.  When modifying templates it looks to the PivotTable not to the huge data set.  This makes it easier for these funtions to cacluclate a result.  Now for the secret you don't usually have to do a lot of work to write these  formulas .  Use = then click on a cell from the PivotTable.  Then make it dynamic by changing where the criteria being pulled is a cell referencce.

I think I have made my case.  If I think of any other benefits or contasts I will update this post.