CODEWITHSUNDEEP
t-sql
Hexxxer
Hexxxer

Reputation: 67

Select Different Table Based on Year

I am attempting to build a view to be used in crystal reports that allows us to look up GL codes. Unfortunately, our ERP creates a new SQL table each year and appends it the last 2 digits onto the table name.

Unless I can find a way to change which table it looks at based off the date I will need to manually change the view every year for each of the views I am creating. Any advice?

This Year: select * from GL000016
Next Year: select * from GL000017

Upvotes: 2

Views: 85

Answers (4)

Pierre-Alain Vigeant
Pierre-Alain Vigeant

Reputation: 23093

Create yourself a temporary table that match the common structure of your GL0000XX table.

You then have to use dynamic SQL to query your tables.

CREATE TABLE #GL ....;

DECLARE @year char(2) = YEAR(GETDATE()) % 100;

INSERT INTO #GL
EXEC('SELECT * FROM GL0000' + @year);

Upvotes: 0

Henry Lu
Henry Lu

Reputation: 381

Not exactly possible to switch tables dynamically for Views

If you want to switch the table you are selecting from, you'll need the to use IF statements or do Dynamic sql. Considering that you want to do this in a view, both of those are not available to you. So from my perspective, your options are:

  1. Switch to use a stored procedure and use dynamic sql or if statements
  2. Switch to use a function that returns a table (again, dynamic sql or if statements)
  3. A Sql job that periodically runs a stored procedure that uses dynamic sql to re-create the view with the correct GL Account table name.

If you have to use a view, then 3 is probably your option, but it comes with a maintenance and handover overhead. Next person working on this project might be wondering why their view changes keeps getting overwritten.

Upvotes: 0

dcreight
dcreight

Reputation: 641

Here is the MSSQL version: 

DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @TableName AS NVARCHAR(100)
SET @TableName = 'GL0000' + RIGHT(CONVERT(CHAR(4), GETDATE(), 120),2)
SET @SQLQuery = 'SELECT * FROM ' + @TableName
EXECUTE sp_executesql @SQLQuery

You could also use a stored procedure depending on the environment. @Tablename will hold the table name if that is all you need (i.e. SELECT @Tablename).

Upvotes: 2

William Xifaras
William Xifaras

Reputation: 5312

You can use the T-SQL Year function.

Returns an integer that represents the year of the specified date.

https://msdn.microsoft.com/en-us/library/ms186313.aspx

So for this year, the following will return 17.

select (YEAR(GETDATE()) % 100) + 1

Upvotes: 0

Related Questions