Select from a column using a variable

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)

Select from a column using a variable

Author

Topic

timlisten
Starting Member

26 Posts

Posted - 2012-06-14 : 21:41:22

Hi, I have a few columns like this

Day1, Day2, Day3, Day4, Day5.

and a store procedure
CREATE PROCEDURE GetDays(@DayValue1 INT, @DayValue2 INT)

I need to do some calculations between two of the columns depends on what variables I pass in the store procedure.

For example, if I do
EXEC GetDays(2, 5), that means I need to do some calculation for the columns Day2 and Day5. Is there a way for me to do it without using dynamic query?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-14 : 21:44:45

yes. Normalize your table

KH
[spoiler]Time is always against us[/spoiler]

timlisten
Starting Member

26 Posts

Posted - 2012-06-14 : 21:57:46

How to do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-14 : 22:01:39

instead of having column like day1, day2 etc
you have
- DayNo
- DayValue

so when you need like SUM of Day2 to Day5,


select SUM(DayValue)
from   yourtable
where  DayNo between 2 and 5

KH
[spoiler]Time is always against us[/spoiler]

timlisten
Starting Member

26 Posts

Posted - 2012-06-14 : 22:12:17

Hi, the table was designed this way by one of my colleague who no longer work here and there are a lot of queries rely on this table. Is there any other way around this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-14 : 22:18:10

How many Dayxx column will you have ? is it fixed ?

KH
[spoiler]Time is always against us[/spoiler]

timlisten
Starting Member

26 Posts

Posted - 2012-06-14 : 22:33:30

Yah, they are fixed. There are a total of 7 columns.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-14 : 22:59:52