Can't you transform the values into that format in your code? i.e. store them in variables as they come out of the recordset and then manipulate them however you please? If not, the following SQL should do what you need, although it will perform slower over a large number of records than doing it in code:- SELECT [fondi].[ID], [fondi].[nome_fondo], (SELECT TOP 1 [valore_quota] FROM [valori] WHERE [id_fondo] = [fondi].[ID] ORDER BY [data_quota] DESC) AS [CurrentValue], (SELECT TOP 1 [valore_quota] FROM [valori] WHERE [id_fondo] = [fondi].[ID] AND [data_quota] < (SELECT TOP 1 [data_quota] FROM [valori] WHERE [id_fondo] = [fondi].[ID] ORDER BY [data_quota] DESC) ORDER BY [data_quota] DESC) AS [LastValue] FROM [fondi]; Dan
________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Valter Sent: 26 October 2005 07:12 To: [email protected] Subject: [AspClassicAnyQuestionIsOk] Re: Quotation table Thank you very much for your help, probably I didn't get something but when I run the query as below "Select TOP 2 valore_quota, nome_fondo from valori, fondi where id = id_fondo order by data_quota desc" it returns me two rows like the following Fund1 value1 Fund1 value2 instead of Fund1 value1 value2 as I actually need. Later I might have to calculate the difference between the two values (value1 - value2). Thank you very much once again. Valter --- In [email protected], "Dan Powderhill" <[EMAIL PROTECTED]> wrote: > > A table for the fund names and a table for the values relating to the > funds. > > Dan > > ________________________________ > > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of Valter > Sent: 25 October 2005 16:26 > To: [email protected] > Subject: [AspClassicAnyQuestionIsOk] Re: Quotation table > > > Do you mean two tables for each fund? > Thank you > Valter > > > --- In [email protected], "Dan Powderhill" > <[EMAIL PROTECTED]> wrote: > > > > Normalise it into two tables:- > > > > tblFunds > > ------------ > > ID > > FundName > > > > tblFundValues > > -------------------- > > ID > > FundID > > Value > > Date > > > > Then you can have a history of values by date for every fund, and > using > > basic SQL will be able to bring back the current and current-but- one > > values using the TOP keyword (SELECT TOP 2 [Value] FROM > [tblFundValues] > > ORDER BY [Date] DESC) etc. > > > > Dan > > > > ________________________________ > > > > From: [email protected] > > [mailto:[EMAIL PROTECTED] On Behalf Of > Valter > > Sent: 25 October 2005 12:40 > > To: [email protected] > > Subject: [AspClassicAnyQuestionIsOk] Quotation table > > > > > > For my personal project I am trying to design a table in which > there > > will be a list of funds, their last month value and their previous > > month value. I do know how to make it with only one fund but with > many > > of them I've found some problems. Can anybody help me? > > I draft a sample of the table how it should look: > > Fund1 last_value prev_value > > Fund2 last_value prev_value > > ... > > ... > > I can have a table for each fund with all values order by date > > > > Thank you > > > > > > > > > > > > > > SPONSORED LINKS > > Basic programming language > > <http://groups.yahoo.com/gads? > t=ms&k=Basic+programming+language&w1=Basic > > > +programming+language&w2=Computer+programming+languages&w3=Programming > +l > > > anguages&w4=Java+programming+language&c=4&s=126&.sig=bnac3LCZpttb3c9Fv > bV > > U-A> Computer programming languages > > <http://groups.yahoo.com/gads? > t=ms&k=Computer+programming+languages&w1=B > > > asic+programming+language&w2=Computer+programming+languages&w3=Program > mi > > > ng+languages&w4=Java+programming+language&c=4&s=126&.sig=1Czd2hKCO9_u4 > KV > > ZQperFQ> Programming languages > > <http://groups.yahoo.com/gads? > t=ms&k=Programming+languages&w1=Basic+prog > > > ramming+language&w2=Computer+programming+languages&w3=Programming+lang > ua > > > ges&w4=Java+programming+language&c=4&s=126&.sig=TyHGCjod4YOKITrSq1xccQ > > > > > > Java programming language > > <http://groups.yahoo.com/gads? > t=ms&k=Java+programming+language&w1=Basic+ > > > programming+language&w2=Computer+programming+languages&w3=Programming+ > la > > > nguages&w4=Java+programming+language&c=4&s=126&.sig=PZAexF9LyXpKb3HDJS > lB > > 1g> > > > > ________________________________ > > > > YAHOO! GROUPS LINKS > > > > > > > > * Visit your group "AspClassicAnyQuestionIsOk > > <http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk> " on the > web. > > > > * To unsubscribe from this group, send an email to: > > [EMAIL PROTECTED] > > <mailto:[EMAIL PROTECTED] > subject=Un > > subscribe> > > > > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of > > Service <http://docs.yahoo.com/info/terms/> . > > > > > > ________________________________ > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > SPONSORED LINKS > Basic programming language > <http://groups.yahoo.com/gads? t=ms&k=Basic+programming+language&w1=Basic > +programming+language&w2=Computer+programming+languages&w3=Programming +l > anguages&w4=Java+programming+language&c=4&s=126&.sig=bnac3LCZpttb3c9Fv bV > U-A> Computer programming languages > <http://groups.yahoo.com/gads? t=ms&k=Computer+programming+languages&w1=B > asic+programming+language&w2=Computer+programming+languages&w3=Program mi > ng+languages&w4=Java+programming+language&c=4&s=126&.sig=1Czd2hKCO9_u4 KV > ZQperFQ> Programming languages > <http://groups.yahoo.com/gads? t=ms&k=Programming+languages&w1=Basic+prog > ramming+language&w2=Computer+programming+languages&w3=Programming+lang ua > ges&w4=Java+programming+language&c=4&s=126&.sig=TyHGCjod4YOKITrSq1xccQ > > > Java programming language > <http://groups.yahoo.com/gads? t=ms&k=Java+programming+language&w1=Basic+ > programming+language&w2=Computer+programming+languages&w3=Programming+ la > nguages&w4=Java+programming+language&c=4&s=126&.sig=PZAexF9LyXpKb3HDJS lB > 1g> > > ________________________________ > > YAHOO! GROUPS LINKS > > > > * Visit your group "AspClassicAnyQuestionIsOk > <http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk> " on the web. > > * To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED] subject=Un > subscribe> > > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of > Service <http://docs.yahoo.com/info/terms/> . > > > ________________________________ > > > > > [Non-text portions of this message have been removed] > ________________________________ YAHOO! GROUPS LINKS * Visit your group "AspClassicAnyQuestionIsOk <http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk> " on the web. * To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED] subscribe> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> . ________________________________ ------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/saFolB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
