4 replies to this topic

[DoktorD1313]

When using MS SQL, is it possible to reference a column by storing the column name in a variable?

The reason for asking is that I need to use different columns out of a table depending on the current month.

I.E.

Current month = 1 (Jan)

Columns to read: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
-----------------------------------------------------------------

Current month = 2 (Feb)

Columns to read: 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13

And so on and so forth..


If there were a way to reference column names via variables, I could set the variable from the current month and go through the columns I need with a WHILE statement.

Otherwise, I'm looking at having to write a load of IF statements to do the same work.

[WingedPanther]

The questions suggests your database should be structured differently. What is the purpose of doing this?

[DoktorD1313]

Well I can't think of any other way of going about it, but the reason for the layout is as follows:

The item history data that is imported into the database contains the sales history of each item by month for the past three years. I've structured it so that it is contained in one record starting from the oldest to the most recent month.

Once a new year has been reached, the server (that the data is exported from, not my database) drops the oldest year and creates a new year (even if there is only a single month of history in it, the rest of the 12 are shown as "0").

Because the reorder program looks at the 12 months prior to the current month, I was hoping that I could use a variable to act as a column name so I could name my columns as numbers to avoid lots of "IF/ELSE" coding.

The other big problem is that MS SQL doesnt support functions across rows like Excel (such as max, min, and count). Therefore, it looks like I would have to do an IF statement in a counted loop with something to the order of this pseudo code:

SET @COUNT = [VARIABLE COLUMN]


WHILE @COUNT > 1


IF [VARIABLE COLUMN] > [HIGH VALUE COLUMN] 

THEN UPDATE [HIGH VALUE COLUMN] = [VARIABLE COLUMN]


SET @COUNT = @COUNT - 1

SET [VARIABLE COLUMN] = [VARIABLE COLUMN] - 1

Figured I might be able to create a procedure that would accept the beginning column number and store it into a variable, then create a string for the SQL query, which would be able to incorperate the variable and just execute the string.

Are there any flaws/drawbacks that you can see with this plan?

[WingedPanther]

It would probably be easier to import the history as a series of records, with a date of the first of the month, and the sales number. That way you could select the first 12 records that are on or after a specified month.

[DoktorD1313]

I might just have to try that. I like the way that sounds.

Thanks!