Identifying correct column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Identifying correct column

Author

Topic

pizzo36
Starting Member

5 Posts

Posted - 2007-08-10 : 16:10:51

This may be an easy one to solve but I can't figure it out.

I have a table showing demand over a rolling period, lets say three months. Once a new month is started a new column is created and the oldest column is deleted.
Ex:
|2007_06|2007_07|2007_08| --those breaks are supposed to show columns

In my select statement I need to make sure that I am selecting the column with the most up to date information, hence, '2007_08'. As this will be changing on a month by month basis, how can I make sure that this is automatic and will not have to be manually updated.

Thanks
Pizza Man

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-10 : 16:22:41

only with dynamic sql.
but i have to say that this is a bit weird db design. not normalized at all

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pizzo36
Starting Member

5 Posts

Posted - 2007-08-10 : 17:02:43

Thanks for directing me onto dynamic SQL. I followed those techniques and came up with this.

begin
declare @DSQL varchar(1000)
declare @Column1 varchar(15)
declare @Column2 varchar(15)
set @column1 = 'ohqty'+left(convert(varchar(8),getdate(),112),4)+'_'+substring(convert(varchar(8),getdate(),112),5,2)
set @column2 = 'ssicc'+left(convert(varchar(8),getdate(),112),4)+'_'+substring(convert(varchar(8),getdate(),112),5,2)
select @DSQL = 'Select count(*), sum('+ @column1 +')'
select @DSQL = @DSQL + 'from itemmastmonthly'
exec(@DSQL)
end

This appears to work.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-13 : 02:30:10

http://www.datamodel.org/NormalizationRules.html
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

marat
Yak Posting Veteran

85 Posts

Posted - 2007-08-13 : 04:22:56

declare @Column varchar(80)
declare @DSQL varchar(1000)
select @Column = max(Column_name) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'YourTable' and Column_name like '200%'

select @DSQL = 'Select count(['+ @Column + ']), sum(['+ @Column + ']) from YourTable'

exec(@DSQL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources