473,320 Members | 1,695 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 Null

Nov 3 '05 #1
22 1959
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
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
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
Hi Suessmeyer

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

Thanks & Regards
kalyan

Nov 3 '05 #5
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
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
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
--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
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

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
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
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
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
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
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
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
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
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
(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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

21
by: Dave | last post by:
After following Microsofts admonition to reformat my system before doing a final compilation of my app I got many warnings/errors upon compiling an rtf file created in word. I used the Help...
9
by: Tom | last post by:
A question for gui application programmers. . . I 've got some GUI programs, written in Python/wxPython, and I've got a help button and a help menu item. Also, I've got a compiled file made with...
6
by: wukexin | last post by:
Help me, good men. I find mang books that introduce bit "mang header files",they talk too bit,in fact it is my too fool, I don't learn it, I have do a test program, but I have no correct doing...
3
by: Colin J. Williams | last post by:
Python advertises some basic service: C:\Python24>python Python 2.4.1 (#65, Mar 30 2005, 09:13:57) on win32 Type "help", "copyright", "credits" or "license" for more information. >>> With...
7
by: Corepaul | last post by:
Missing Help Files When I enter "recordset" as the keyword and search the Visual Basic Help index, I get many topics of interest in the resulting list. But there isn't any information available...
5
by: Steve | last post by:
I have written a help file (chm) for a DLL and referenced it using Help.ShowHelp My expectation is that a developer using my DLL would be able to access this help file during his development time...
8
by: Mark | last post by:
I have loaded Visual Studio .net on my home computer and my laptop, but my home computer has an abbreviated help screen not 2% of the help on my laptop. All the settings look the same on both...
10
by: JonathanOrlev | last post by:
Hello everybody, I wrote this comment in another message of mine, but decided to post it again as a standalone message. I think that Microsoft's Office 2003 help system is horrible, probably...
1
by: trunxnirvana007 | last post by:
'UPGRADE_WARNING: Array has a new behavior. Click for more: 'ms-help://MS.VSCC.v80/dv_commoner/local/redirect.htm?keyword="9B7D5ADD-D8FE-4819-A36C-6DEDAF088CC7"' 'UPGRADE_WARNING: Couldn't resolve...
0
by: hitencontractor | last post by:
I am working on .NET Version 2003 making an SDI application that calls MS Excel 2003. I added a menu item called "MyApp Help" in the end of the menu bar to show Help-> About. The application...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.