variable column names in sql table valued function

Question

Suppose that I am using a sql server to keep track of all my personal expenses and that I am tagging everything with a date and a category.

This allows me to do things like aggregate monthly expenses per category and look at the last several months of expenses with each category as a row and the most recent months as columns.

What I am stuck on is the fact that I am having to name the columns things like "most recent month", "previous month", and "2 months ago". I would really prefer to be able to name them something like "Jan10", "Feb10", or "Mar09" or something like that and have them update automatically every month.

Calculating the names is simple enough, but I'm not sure how to get sql server to interpret a formula or join or anything like that as the alias for a column.

Any insights on this one?

Answer 1

What I am stuck on is the fact that I am having to name the columns things like "most recent month", "previous month", and "2 months ago". I would really prefer to be able to name them something like "Jan10", "Feb10", or "Mar09" or something like that and have them update automatically every month.

That approach means creating multiple columns, which will eventually hit the limit. In SQL Server, that's 1,024 for a non-wide and 30,000 for a wide table ...

A better approach is to store the date, and section/partition the data in the query as needed. Because Jan 10th is going to have a different value for 2009, 2010, 2011, etc...

Answer 2

It looks to me like you're trying to store data the same way you want to view it. That is not a very good way of going about it. I would suggest the following:

Your table should have the essentials, say:

Store (This could become its own table, but we can leave it as a string)
Category
Amount Spent
Date

Then, once you have this data, you can report on it. This report will do the heavy lifting, and will be dynamic in the sense that you don't need to hardcode values. The following is just an example, and I can't promise the syntax is even correct.

SELECT Store, SUM(CASE WHEN Date > GETDATE() - 14 THEN AmountSpent ELSE 0 END)
FROM YourTable
GROUP BY Store

The above will give you all money spent at each store in the last 14 days. This window will be "sliding", every time you run it, it will look back two weeks; no hardcoding.

Answer 3

You may wish to acquaint with Entity-attribute-value model and approaches.