Click here to Skip to main content
15,171,873 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table Ex (ID,one,Two,three,Four,five,six,seven,eight,nine,ten) and values are in following format . .

SQL
ID one Two three Four five six seven eight nine ten

1 10 11 12 13 14 15 16 17 18 19

2 21 21 22 23 24 25 26 27 28 29

3 13 14 12 13 54 55 46 13 18 19

4 10 11 12 13 14 15 16 17 18 19

5 10 14 124 113 114 115 116 117 118 119


Here The Column Represents The Months and Id represents The Date. so I want a query where only three month data will be displayed on the basis of the month. suppose we have records of Jan,Feb,march now as soon as April comes (It will be compared or fetched through label displaying Current Date) Jan Data Automatically be removed. now the record will have only Feb,March,April data (current) To Kindly Help me How To achieve this.
Posted
Updated 13-Jan-15 3:20am
v2
Comments
   
What have you tried and where is the issue?

1 solution

Yes, you can do this. Little weird though. Have a look.

SQL
--Get current month
DECLARE @MONTH INT
SELECT @MONTH=DATEPART(MONTH,GETDATE())

--Get first column/month name 
DECLARE @MONTH1 VARCHAR(10)
SELECT @MONTH1 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MYTABLE'
AND ORDINAL_POSITION = @MONTH+1	--added 1 to exclude column 'ID'

--get second column/month name
DECLARE @MONTH2 VARCHAR(10)
IF(@MONTH<12) SET @MONTH=@MONTH+1 ELSE SET @MONTH=1
SELECT @MONTH2 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MYTABLE'
AND ORDINAL_POSITION = @MONTH+1

--get third column/month name
DECLARE @MONTH3 VARCHAR(10)
IF(@MONTH<12) SET @MONTH=@MONTH+1 ELSE SET @MONTH=1
SELECT @MONTH3 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MYTABLE'
AND ORDINAL_POSITION = @MONTH+1

DECLARE @SQL AS NVARCHAR(1000)
SET @SQL = 'SELECT ' + @MONTH1 +',' + @MONTH2 +','+@MONTH3 +' FROM MYTABLE' 

EXEC(@SQL)


I have tried this in my local and should do your job. In case this doesn't work or you want me to explain anything, please let me know :)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900