help

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 NullHi 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
didnt 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.|||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|||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
--|||Hi Suessmeyer

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

Thanks & Regards
kalyan|||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|||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
--|||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
--|||--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.|||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|||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.|||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

32.0500 2005-01-05 00:00:00.000
32.1800 2005-01-04 00:00:00.000
32.2600 2005-01-03 00:00:00.000
32.1500 2004-12-31 00:00:00.000
32.1400 2004-12-30 00:00:00.000
32.1400 2004-12-29 00:00:00.000
32.1800 2004-12-28 00:00:00.000
32.1900 2004-12-27 00:00:00.000
32.1700 2004-12-23 00:00:00.000
32.0900 2004-12-22 00:00:00.000
32.0700 2004-12-21 00:00:00.000
32.0700 2004-12-20 00:00:00.000
32.1000 2004-12-17 00:00:00.000
32.3400 2004-12-16 00:00:00.000
31.7300 2004-12-15 00:00:00.000
31.7300 2004-12-14 00:00:00.000
31.8200 2004-12-13 00:00:00.000
31.7800 2004-12-10 00:00:00.000
31.7000 2004-12-09 00:00:00.000
31.6300 2004-12-08 00:00:00.000
31.6500 2004-12-07 00:00:00.000
31.6700 2004-12-06 00:00:00.000
31.7000 2004-12-03 00:00:00.000

16112.18002005-01-05 00:00:00.000
16112.89002005-01-04 00:00:00.000
16114.55002005-01-03 00:00:00.000
16115.12002004-12-31 00:00:00.000
16115.50002004-12-30 00:00:00.000
16115.06002004-12-29 00:00:00.000
16114.80002004-12-28 00:00:00.000
16114.71002004-12-27 00:00:00.000
16114.22002004-12-23 00:00:00.000
16113.14002004-12-22 00:00:00.000
16111.67002004-12-21 00:00:00.000
16112.39002004-12-20 00:00:00.000
16109.47002004-12-17 00:00:00.000
16108.49002004-12-16 00:00:00.000
16108.96002004-12-15 00:00:00.000
16108.19002004-12-14 00:00:00.000
16107.09002004-12-13 00:00:00.000
16106.12002004-12-10 00:00:00.000
16106.02002004-12-09 00:00:00.000
16105.41002004-12-08 00:00:00.000
16107.88002004-12-07 00:00:00.000
16106.77002004-12-06 00:00:00.000
16107.68002004-12-03 00:00:00.000

so by using u r query

i got the following

32.0500.97560975609756102005-01-05 00:00:00.000
32.1800.45871559633027522005-01-04 00:00:00.000
32.26003.53982300884955752005-01-03 00:00:00.000
32.1500.93023255813953492004-12-31 00:00:00.000
32.1400.46728971962616822004-12-30 00:00:00.000
32.1400.46728971962616822004-12-29 00:00:00.000
32.1800.45871559633027522004-12-28 00:00:00.000
32.19003.19634703196347032004-12-27 00:00:00.000
32.1700.46082949308755762004-12-23 00:00:00.000
32.0900.47846889952153112004-12-22 00:00:00.000
32.0700.96618357487922712004-12-21 00:00:00.000
32.0700.96618357487922712004-12-20 00:00:00.000
32.10001.90476190476190482004-12-17 00:00:00.000
32.3400NULL2004-12-16 00:00:00.000
31.73002.89017341040462432004-12-15 00:00:00.000
31.73002.89017341040462432004-12-14 00:00:00.000
31.820012.63736263736263742004-12-13 00:00:00.000
31.78002.24719101123595512004-12-10 00:00:00.000
31.70001.76470588235294122004-12-09 00:00:00.000
31.63001.22699386503067482004-12-08 00:00:00.000
31.65001.21212121212121212004-12-07 00:00:00.000
31.67001.79640718562874252004-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

31.70001.76470588235294122004-12-03 00:00:00.000

16112.1800.18719914423248352005-01-05 00:00:00.000
16112.8900.22145451324297992005-01-04 00:00:00.000
16114.5500.13967699694456572005-01-03 00:00:00.000
16115.1200.33009034051424602004-12-31 00:00:00.000
16115.5000NULL2004-12-30 00:00:00.000
16115.0600.05214670606640012004-12-29 00:00:00.000
16114.8000.22648083623693382004-12-28 00:00:00.000
16114.7100.07845872199459512004-12-27 00:00:00.000
16114.2200.28891612677289442004-12-23 00:00:00.000
16113.1400.95456955983736962004-12-22 00:00:00.000
16111.6700.45670278499149282004-12-21 00:00:00.000
16112.3900.44487943767239082004-12-20 00:00:00.000
16109.47002.00968301817849642004-12-17 00:00:00.000
16108.4900.43321965158079092004-12-16 00:00:00.000
16108.9600.46806167400881062004-12-15 00:00:00.000
16108.1900.27728995286070802004-12-14 00:00:00.000
16107.0900.55093846297506772004-12-13 00:00:00.000
16106.1200.61251413494157562004-12-10 00:00:00.000
16106.0200.09432182607055272004-12-09 00:00:00.000
16105.4100.57869272365050752004-12-08 00:00:00.000
16107.8800.28735632183908052004-12-07 00:00:00.000
16106.7700.29970965627048802004-12-06 00:00:00.000
16107.6800.18573551263001492004-12-03 00:00:00.000

please help

thanks & regards
kalyan|||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
--|||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
--|||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|||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, thatll help us to help you father rather than just guessing.

Jens.|||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, thatll help us to help you father rather than just guessing.

Jens.|||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|||kalikoi@.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.|||(kalikoi@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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-q...xact&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)|||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-q...xact&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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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-q...xact&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)