By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,256 Members | 1,700 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,256 IT Pros & Developers. It's quick & easy.

help

P: n/a
Hi

I got a table with 2 columns as follows

col1 col2

10 193.51
10 194.5
10 202.71

20 192.79
20 197.6
20 192.9

30 192.76
30 191.91
30 187.9
Now i need to add a column dynamically thru sql statement to the table
so that my output should be as follows
here

0.511601468=(194.5/193.51-1)*100
4.221079692=(202.71/194.5-1)*100
and so on
col1 col2 col3

10 193.51 0.511601468
10 194.5 4.221079692
10 202.71 null

20 192.79 2.494942684
20 197.6 -2.37854251
20 192.9 null

30 192.76 -0.440962855
30 191.91 -2.08952113
30 187.9 Null

Nov 3 '05 #1
Share this Question
Share on Google+
22 Replies


P: n/a
Hi Kali,

create table #Tempone
(
SomeID INT,
SomeValue REAL
)
INSERT INTO #Tempone
SELECT 10 , 193.51

INSERT INTO #Tempone
SELECT 10 , 194.5

INSERT INTO #Tempone
SELECT 10 , 202.71
INSERT INTO #Tempone
SELECT 20 , 192.79

INSERT INTO #Tempone
SELECT 20 , 197.6

INSERT INTO #Tempone
SELECT 20 , 192.9
INSERT INTO #Tempone
SELECT 30 , 192.76

INSERT INTO #Tempone
SELECT 30 , 191.91

INSERT INTO #Tempone
SELECT 30 , 187.9
--Query
SELECT T1.SomeId, T1.SomeValue, ((SELECT TOP 1 SomeValue FROM #Tempone
T2 WHERE T1.SomeId = T2.SomeID AND T2.SomeValue > T1.SomeValue ORDER BY
SomeValue ) / SomeValue-1)*100
From #Tempone T1

order by T1.SomeValue
but you should consider the order of the inserted rows, I saw that you
didnīt have an order of the value, so in this scenario they will be
fetched as they will come in to the query processor.
HTH, jens Suessmeyer.

Nov 3 '05 #2

P: n/a
Hi Suessmeyer

thanks for u r reply.
U r solution is good
can i get my result as column to the existing table(#Tempone ) that is
an update statement instead of select
ofcourse we can add column thru alter syntax

Hope U understand

Thanks & Regards
kalyan

Nov 3 '05 #3

P: n/a
What is the primary key here? Please post DDL for your table(s) so that
we don't have to guess.

Your example calculations seem to imply a sequence to the data. i.e.
193.51 comes "first", followed by 194.5, followed by 202.71. Have I got
that right? If so, what defines the sequence? Is it just that the Col2
values are taken lowest first? Please explain a bit more.

--
David Portas
SQL Server MVP
--

Nov 3 '05 #4

P: n/a
Hi Suessmeyer

Also how should we handle division by zero errors in u r case

Thanks & Regards
kalyan

Nov 3 '05 #5

P: n/a
Hi David Portas

here col1 is the companyids
col2 is the prices of those ids for a period of 3 days
and col3 is the return calculation

as for each day return of a
companyid=(price(previousday)/price(currentday)-1)*100

here the period may vary

Hope U understand


Thanks & Regards
kalyan

Nov 3 '05 #6

P: n/a
What do you want to be the result when a zero divide occurs? If you
want nulls you can use NULLIF:

(x / NULLIF(z,0))

--
David Portas
SQL Server MVP
--

Nov 3 '05 #7

P: n/a
You didn't answer any of the questions I asked.
companyid=(price(previousday)/price(currentday)-1)*100


Yes, but what is current and previous here? You cannot know which row
represents the previous day unless you have a date column or similar.

--
David Portas
SQL Server MVP
--

Nov 3 '05 #8

P: n/a
--Create the other column

ALTER TABLE #tempone ADD SomeCalcValue REAL NULL

UPDATE #Tempone SET
col3 =
((SELECT TOP 1 SomeValue
FROM #Tempone T2
WHERE T1.SomeId = T2.SomeID
AND T2.SomeValue > T1.SomeValue
ORDER BY SomeValue )
/ (CASE SomeValue WHEN 0 THEN NULL ELSE SomeValue
END)-1)*100
From #Tempone T1


But what I already mentioned and David mentioned is the sequence in
which the calculations is done in place, because in your sample data
there was no structure to find any logic of the sequence in there.

BTW, Call me Jens :-D

HTH, jens Suessmeyer.

Nov 3 '05 #9

P: n/a
Hi David Portas

here col2 values are arranged in the descending order of date

this is the query is used

select companyid,latestclosingprice into #Tempone from
backscreeningdata3 where dailydate
between '12/3/04' and '01/05/05' order by companyid,dailydate desc
Hope U understand
Thanks & Regards
kalyan

Nov 3 '05 #10

P: n/a

UPDATE #Tempone SET
differenceValue =
((SELECT TOP 1 latestclosingprice
FROM #Tempone T2
WHERE T1.companyid = T2.companyid
AND T2.dailydate > T1.dailydate
ORDER BY dailydate ASC )
/ (CASE SomeValue WHEN 0 THEN NULL ELSE SomeValue END)-1)*100
FROM #Tempone T1

That assumes that only one entry per date exists, because otherwise
there would be a unpredicted way to sort the results.

Nov 3 '05 #11

P: n/a
Hi jens

I got a problem by using u r query

i generated a temp table using the following query as
select companyid,latestclosingprice,dailydate into #Tempone from
backscreeningdata3 where dailydate
between '12/3/04' and '01/05/05' order by companyid,dailydate desc

if i took records for 2 companies these r the results

3 2.0500 2005-01-05 00:00:00.000
3 2.1800 2005-01-04 00:00:00.000
3 2.2600 2005-01-03 00:00:00.000
3 2.1500 2004-12-31 00:00:00.000
3 2.1400 2004-12-30 00:00:00.000
3 2.1400 2004-12-29 00:00:00.000
3 2.1800 2004-12-28 00:00:00.000
3 2.1900 2004-12-27 00:00:00.000
3 2.1700 2004-12-23 00:00:00.000
3 2.0900 2004-12-22 00:00:00.000
3 2.0700 2004-12-21 00:00:00.000
3 2.0700 2004-12-20 00:00:00.000
3 2.1000 2004-12-17 00:00:00.000
3 2.3400 2004-12-16 00:00:00.000
3 1.7300 2004-12-15 00:00:00.000
3 1.7300 2004-12-14 00:00:00.000
3 1.8200 2004-12-13 00:00:00.000
3 1.7800 2004-12-10 00:00:00.000
3 1.7000 2004-12-09 00:00:00.000
3 1.6300 2004-12-08 00:00:00.000
3 1.6500 2004-12-07 00:00:00.000
3 1.6700 2004-12-06 00:00:00.000
3 1.7000 2004-12-03 00:00:00.000

16 112.1800 2005-01-05 00:00:00.000
16 112.8900 2005-01-04 00:00:00.000
16 114.5500 2005-01-03 00:00:00.000
16 115.1200 2004-12-31 00:00:00.000
16 115.5000 2004-12-30 00:00:00.000
16 115.0600 2004-12-29 00:00:00.000
16 114.8000 2004-12-28 00:00:00.000
16 114.7100 2004-12-27 00:00:00.000
16 114.2200 2004-12-23 00:00:00.000
16 113.1400 2004-12-22 00:00:00.000
16 111.6700 2004-12-21 00:00:00.000
16 112.3900 2004-12-20 00:00:00.000
16 109.4700 2004-12-17 00:00:00.000
16 108.4900 2004-12-16 00:00:00.000
16 108.9600 2004-12-15 00:00:00.000
16 108.1900 2004-12-14 00:00:00.000
16 107.0900 2004-12-13 00:00:00.000
16 106.1200 2004-12-10 00:00:00.000
16 106.0200 2004-12-09 00:00:00.000
16 105.4100 2004-12-08 00:00:00.000
16 107.8800 2004-12-07 00:00:00.000
16 106.7700 2004-12-06 00:00:00.000
16 107.6800 2004-12-03 00:00:00.000
so by using u r query

i got the following

3 2.0500 .9756097560975610 2005-01-05 00:00:00.000
3 2.1800 .4587155963302752 2005-01-04 00:00:00.000
3 2.2600 3.5398230088495575 2005-01-03 00:00:00.000
3 2.1500 .9302325581395349 2004-12-31 00:00:00.000
3 2.1400 .4672897196261682 2004-12-30 00:00:00.000
3 2.1400 .4672897196261682 2004-12-29 00:00:00.000
3 2.1800 .4587155963302752 2004-12-28 00:00:00.000
3 2.1900 3.1963470319634703 2004-12-27 00:00:00.000
3 2.1700 .4608294930875576 2004-12-23 00:00:00.000
3 2.0900 .4784688995215311 2004-12-22 00:00:00.000
3 2.0700 .9661835748792271 2004-12-21 00:00:00.000
3 2.0700 .9661835748792271 2004-12-20 00:00:00.000
3 2.1000 1.9047619047619048 2004-12-17 00:00:00.000
3 2.3400 NULL 2004-12-16 00:00:00.000
3 1.7300 2.8901734104046243 2004-12-15 00:00:00.000
3 1.7300 2.8901734104046243 2004-12-14 00:00:00.000
3 1.8200 12.6373626373626374 2004-12-13 00:00:00.000
3 1.7800 2.2471910112359551 2004-12-10 00:00:00.000
3 1.7000 1.7647058823529412 2004-12-09 00:00:00.000
3 1.6300 1.2269938650306748 2004-12-08 00:00:00.000
3 1.6500 1.2121212121212121 2004-12-07 00:00:00.000
3 1.6700 1.7964071856287425 2004-12-06 00:00:00.000

here i should not get a value for col3 as there is no value for the
next date of this id..same for the next id also

3 1.7000 1.7647058823529412 2004-12-03 00:00:00.000

16 112.1800 .1871991442324835 2005-01-05 00:00:00.000
16 112.8900 .2214545132429799 2005-01-04 00:00:00.000
16 114.5500 .1396769969445657 2005-01-03 00:00:00.000
16 115.1200 .3300903405142460 2004-12-31 00:00:00.000
16 115.5000 NULL 2004-12-30 00:00:00.000
16 115.0600 .0521467060664001 2004-12-29 00:00:00.000
16 114.8000 .2264808362369338 2004-12-28 00:00:00.000
16 114.7100 .0784587219945951 2004-12-27 00:00:00.000
16 114.2200 .2889161267728944 2004-12-23 00:00:00.000
16 113.1400 .9545695598373696 2004-12-22 00:00:00.000
16 111.6700 .4567027849914928 2004-12-21 00:00:00.000
16 112.3900 .4448794376723908 2004-12-20 00:00:00.000
16 109.4700 2.0096830181784964 2004-12-17 00:00:00.000
16 108.4900 .4332196515807909 2004-12-16 00:00:00.000
16 108.9600 .4680616740088106 2004-12-15 00:00:00.000
16 108.1900 .2772899528607080 2004-12-14 00:00:00.000
16 107.0900 .5509384629750677 2004-12-13 00:00:00.000
16 106.1200 .6125141349415756 2004-12-10 00:00:00.000
16 106.0200 .0943218260705527 2004-12-09 00:00:00.000
16 105.4100 .5786927236505075 2004-12-08 00:00:00.000
16 107.8800 .2873563218390805 2004-12-07 00:00:00.000
16 106.7700 .2997096562704880 2004-12-06 00:00:00.000
16 107.6800 .1857355126300149 2004-12-03 00:00:00.000

please help

thanks & regards
kalyan

Nov 3 '05 #12

P: n/a
Wrong. ORDER BY on SELECT INTO does NOT order the values because tables
are always unordered. Don't use ORDER BY with SELECT INTO, it serves no
purpose except maybe to slow things down. See the UPDATE that Jens
posted. Note that you need the date in there too.

--
David Portas
SQL Server MVP
--

Nov 3 '05 #13

P: n/a
PLEASE post DDL. PLEASE include the KEYS and CONSTRAINTS with the DDL.
Without this information any answers you get will just be guesswork.
Read the following article to understand how to do this and how to
include sample data as INSERT statements to make it easier for others
to test out solutions:

http://www.aspfaq.com/etiquette.asp?id=5006

You SELECT INTO is more or less irrelevent here. What we really need to
know is the KEYS for the base table, or the table on which you want to
perform the UPDATE or SELECT.

--
David Portas
SQL Server MVP
--

Nov 3 '05 #14

P: n/a
Hi David Portas

If order by doesn't work then how can i frame my temp table so that i
can work on Jen's query

Hope u got my requirement

thanks & Regards
kalyan

Nov 3 '05 #15

P: n/a
Hi OP,
Select SomeID,SomeValue,(SELECT TOP 1 Somevalue from #tempone t2 Where
t1.SomeId = t2.Someid AND t2.lastestDate<t1.lastestDate Order by
lastestDate DESC),
((SELECT TOP 1 Somevalue from #tempone t2 Where t1.SomeId = t2.Someid
AND t2.lastestDate<t1.lastestDate Order by lastestDate DESC)/
(CASE SomeValue WHEN 0 THEN NULL ELSE SomeValue END)-1)*100 as
DifferenceValue,lastestDate
FROM (SELECT TOP 100 PERCENT * from #TempOne order by lastestdate DESC)
T1
next time you are asking please provide directly the sample and DDL
data, thatīll help us to help you father rather than just guessing.

Jens.

Nov 3 '05 #16

P: n/a
Hi OP,
Select SomeID,SomeValue,(SELECT TOP 1 Somevalue from #tempone t2 Where
t1.SomeId = t2.Someid AND t2.lastestDate<t1.lastestDate Order by
lastestDate DESC),
((SELECT TOP 1 Somevalue from #tempone t2 Where t1.SomeId = t2.Someid
AND t2.lastestDate<t1.lastestDate Order by lastestDate DESC)/
(CASE SomeValue WHEN 0 THEN NULL ELSE SomeValue END)-1)*100 as
DifferenceValue,lastestDate
FROM (SELECT TOP 100 PERCENT * from #TempOne order by lastestdate DESC)
T1
next time you are asking please provide directly the sample and DDL
data, thatīll help us to help you father rather than just guessing.

Jens.

Nov 3 '05 #17

P: n/a
Hi Jens

Sorry for testing u r patience.
Actually i'm a new bie to sql

i must frame a temp table from a history table

select companyid,latestclosingprice,dailydate into #Tempone from
backscreeningdata3 where dailydate
between '12/3/04' and '01/05/05' order by companyid,dailydate desc

so i get the records for each companyid for a period of 23 days
from these i've calculate returns for each companyid for the entire
period
as

for each companyid for each day
return=(price(previousday)/price(currentday)-1)*100

hope u understand

i'm extremely sorry if i'm troubling u alot

hope u got me

BTW what do u mean by OP

Thanks & Regards
kalyan

here backscreeningdata3 is the history table which is updated daily
since 5 years

so for each day we have 4000-4500 records will be updated each
identified by companyid

Nov 3 '05 #18

P: n/a
ka*****@gmail.com wrote:
Hi David Portas

If order by doesn't work then how can i frame my temp table so that i
can work on Jen's query

Hope u got my requirement

thanks & Regards
kalyan


Lacking a date column, you could add an identity (or timestamp?) column
that would track the sequence in which rows are inserted. If the
business
process causes rows to be inserted in chronological sequence, this
column would allow you to identify rows from consecutive days for a
given ID and determine their place in the arithmetic.

Nov 3 '05 #19

P: n/a
(ka*****@gmail.com) writes:
select companyid,latestclosingprice,dailydate into #Tempone from
backscreeningdata3 where dailydate
between '12/3/04' and '01/05/05' order by companyid,dailydate desc
The ORDER BY is still meaningless.
so i get the records for each companyid for a period of 23 days
from these i've calculate returns for each companyid for the entire
period
as

for each companyid for each day
return=(price(previousday)/price(currentday)-1)*100
UPDATE a
SET col3 = (b.price/a.price - 1) * 100
FROM tbl a
JOIN tbl b ON a.companyid = b.companyid
AND a.date = daetadd(DAY, 1, b.date)

This is untested. Had you included the following:

o CREATE TABLE statement for the table.
o Sample data *as INSERT statements*
o The desired result given the sample.

You would have gotten a tested solution.

If this does not give exacatly the right result, play around with
1 and -1, and moving around the alias.
BTW what do u mean by OP


Some people use "the OP" to refer to someone who have posted. I don't know
if they read it out as "the other person" or "the original poster", but
personally I find it impolite to talk about someone as OP when this
person is present - and adressing someone as OP?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 3 '05 #20

P: n/a
On Thu, 3 Nov 2005 22:56:11 +0000 (UTC), Erland Sommarskog wrote:
BTW what do u mean by OP


Some people use "the OP" to refer to someone who have posted. I don't know
if they read it out as "the other person" or "the original poster", but
personally I find it impolite to talk about someone as OP when this
person is present - and adressing someone as OP?


Hi Erland,

The normal meaning for OP is indeed Original Poster. Not Other person.

http://slang.acronymfinder.com/af-qu...act&acronym=OP
I sometimes refer to the OP of a thread as OP. Never when writing a
response to the OP, but sometimes after lots of messages between others.
In that case, "but is that what the OP asked" will be easier to
understand than "but is that what Frank asked" (Frank? Who is Frank??).

Also, I'm more inclined to use the abbreviation OP when the OP uses a
nickname with unprintable characters or foul words.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Nov 3 '05 #21

P: n/a
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
The normal meaning for OP is indeed Original Poster. Not Other person.

http://slang.acronymfinder.com/af-qu...act&acronym=OP
I sometimes refer to the OP of a thread as OP. Never when writing a


And just like DDL, BOL and other funny abbreviations, this is something
that is to be avoided. Not everyone who is posting here knows what they
mean, and it's only confusing to them.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 3 '05 #22

P: n/a
On Thu, 3 Nov 2005 23:50:44 +0000 (UTC), Erland Sommarskog wrote:
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
The normal meaning for OP is indeed Original Poster. Not Other person.

http://slang.acronymfinder.com/af-qu...act&acronym=OP
I sometimes refer to the OP of a thread as OP. Never when writing a


And just like DDL, BOL and other funny abbreviations, this is something
that is to be avoided. Not everyone who is posting here knows what they
mean, and it's only confusing to them.


Hi Erland,

You're right - thanks for the reminder!

(I must admit that I was tempted to write YR-TFTR, though :-)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Nov 4 '05 #23

This discussion thread is closed

Replies have been disabled for this discussion.