Look-UP

Can someone help with the follow --
I have a table in SQL and I would like to write a procedure to the following
Date 2001 2003 2006 2012
20060131 0.0455 0.0455 0.0446 0.0422
20060130 0.0566 0.0566 0.0758 0.0436
20060129 0.783 0.5642 0.3548 0.2165
in the pocedure you enter the Date (20060130) and then the Index (2006) the
result should be 0.0758
Please feel free to call me with if you have any questions
847-323-7731
ThanksChrismkr wrote:
> Can someone help with the follow --
> I have a table in SQL and I would like to write a procedure to the
> following
>
> Date 2001 2003 2006 2012
> 20060131 0.0455 0.0455 0.0446 0.0422
> 20060130 0.0566 0.0566 0.0758 0.0436
> 20060129 0.783 0.5642 0.3548 0.2165
> in the pocedure you enter the Date (20060130) and then the Index
> (2006) the result should be 0.0758
>
A better table design would be:
Date Index Value
20060131 2001 .0455
20060131 2003 .0455
20060131 2006 .0446
20060131 2012 .0422
20060130 2001 .0566
20060130 2003 .0566
20060130 2006 .0758
etc.
That makes the procedure easy:
create procedure GetIndexValue (
@.date datetime, --it IS a datetime column, right?
@.index int) as
select value from tablename
where date= @.date and Index= @.index
My recommendation would be to change the database design to what I suggested
above. if you cannot do that for some reason, then you can "fold" your
existing table using a union query in a view, like this:
create view folded_data as
select date, 2001 as Index, 2001 from tablename
union all
select date, 2003 as Index, 2003 from tablename
union all
select date, 2006 as Index, 2006 from tablename
union all
select date, 2012 as Index, 2012 from tablename
Then use the folded_data view in your procedure instead of the base table:
create procedure GetIndexValue (
@.date datetime, --it IS a datetime column, right?
@.index int) as
select value from folded_data
where date= @.date and Index= @.index
However, this may not perform well. You may get better performance via
dynamic sql. See Erland's dynamic sql articles here:
http://www.sommarskog.se/index.html
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Bob Barrows [MVP] wrote:
> create view folded_data as
> select date, 2001 as Index, 2001 from tablename
> union all
> select date, 2003 as Index, 2003 from tablename
> union all
> select date, 2006 as Index, 2006 from tablename
> union all
> select date, 2012 as Index, 2012 from tablename
>
Sorry, this should read:
create view folded_data as
select date, 2001 as ValueIndex, [2001] as Value from tablename
union all
select date, 2003, [2003] from tablename
union all
select date, 2006, [2006] from tablename
union all
select date, 2012, [2012] from tablename
Also acceptable would be:
create view folded_data (
Date, ValueIndex, Value
) as
select date, 2001 , [2001] from tablename
union all
select date, 2003, [2003] from tablename
union all
select date, 2006, [2006] from tablename
union all
select date, 2012, [2012] from tablename
Why "ValueIndex"? "Index" is a reserved keyword that should be avoided when
naming database objects.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Bob,
Can I call you or can you call me about this,
We are willing to pay the right price to the right person to help us with
this
Thanks
Chris
"Bob Barrows [MVP]" wrote:

> Chrismkr wrote:
> A better table design would be:
> Date Index Value
> 20060131 2001 .0455
> 20060131 2003 .0455
> 20060131 2006 .0446
> 20060131 2012 .0422
> 20060130 2001 .0566
> 20060130 2003 .0566
> 20060130 2006 .0758
> etc.
> That makes the procedure easy:
> create procedure GetIndexValue (
> @.date datetime, --it IS a datetime column, right?
> @.index int) as
> select value from tablename
> where date= @.date and Index= @.index
> My recommendation would be to change the database design to what I suggest
ed
> above. if you cannot do that for some reason, then you can "fold" your
> existing table using a union query in a view, like this:
> create view folded_data as
> select date, 2001 as Index, 2001 from tablename
> union all
> select date, 2003 as Index, 2003 from tablename
> union all
> select date, 2006 as Index, 2006 from tablename
> union all
> select date, 2012 as Index, 2012 from tablename
> Then use the folded_data view in your procedure instead of the base table:
> create procedure GetIndexValue (
> @.date datetime, --it IS a datetime column, right?
> @.index int) as
> select value from folded_data
> where date= @.date and Index= @.index
> However, this may not perform well. You may get better performance via
> dynamic sql. See Erland's dynamic sql articles here:
> http://www.sommarskog.se/index.html
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||To claim your prize, just call them up and give them your credit card
number. ;-)
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:upBlcg7OGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Bob Barrows [MVP] wrote:
> Sorry, this should read:
> create view folded_data as
> select date, 2001 as ValueIndex, [2001] as Value from tablename
> union all
> select date, 2003, [2003] from tablename
> union all
> select date, 2006, [2006] from tablename
> union all
> select date, 2012, [2012] from tablename
> Also acceptable would be:
> create view folded_data (
> Date, ValueIndex, Value
> ) as
> select date, 2001 , [2001] from tablename
> union all
> select date, 2003, [2003] from tablename
> union all
> select date, 2006, [2006] from tablename
> union all
> select date, 2012, [2012] from tablename
> Why "ValueIndex"? "Index" is a reserved keyword that should be avoided
> when
> naming database objects.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Ah! This explains JT's cryptic message.
Sorry but no. I only work through the newsgroups. I've already got a
full-time job :-)
Bob Barrows
Chrismkr wrote:
> Bob,
> Can I call you or can you call me about this,
> We are willing to pay the right price to the right person to help us
> with this
>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.