459,256 Members | 1,700 Online
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
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)*100From #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)*100From #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

 P: n/a Hi OP, Select SomeID,SomeValue,(SELECT TOP 1 Somevalue from #tempone t2 Where t1.SomeId = t2.Someid AND t2.lastestDate

 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 OPSome people use "the OP" to refer to someone who have posted. I don't knowif they read it out as "the other person" or "the original poster", butpersonally I find it impolite to talk about someone as OP when thisperson 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 aAnd just like DDL, BOL and other funny abbreviations, this is somethingthat is to be avoided. Not everyone who is posting here knows what theymean, 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.