Calculation on same table with same column

[vinothrao84]

hi,

i have the 2 following table in my database.

tablename : tblMutFunds

code.......shor tname......numb er......display _web
G1..........Gro wth1......... 1.............. .1
G2..........Gro wth2......... 3.............. .1
G3..........Gro wth3......... 5.............. .1
G4..........Gro wth4......... 2.............. .0
G5..........Gro wth5......... 4.............. .1
G6..........Gro wth6......... 8.............. .1
G7..........Gro wth7......... 7.............. .0
G8..........Gro wth8......... 6.............. .1
G9..........Gro wth9......... 9.............. .0

tablename : tblMutFundPrice

fund_code...... .fund_nav...... .fund_date
G1............. ......0.2896... ....01/12/2007
G2............. ......0.4246... ....01/12/2007
G3............. ......0.3794... ....01/12/2007
G4............. ......0.8001... ....01/12/2007
G5............. ......0.4246... ....01/12/2007
G6............. ......0.9347... ....01/12/2007
G7............. ......0.3794... ....01/12/2007
G8............. ......0.2896... ....01/12/2007
G9............. ......0.3971... ....01/12/2007
G1............. ......0.1122... ....01/13/2007
G2............. ......0.4220... ....01/13/2007
G3............. ......0.3770... ....01/13/2007
G4............. ......0.8090... ....01/13/2007
G5............. ......0.4225... ....01/13/2007
G6............. ......0.9471... ....01/13/2007
G7............. ......0.3841... ....01/13/2007
G8............. ......0.2159... ....01/13/2007
G9............. ......0.3111... ....01/13/2007

The RESULT i want is....

Number......Fun d_Name.......NA V.........Chang e........Change %......Date.... ........Display _Web
1.............. ......Growth1.. .......0.1122.. ...-0.0050.........-1.26........... .01/13/2007........... ......1
3.............. ......Growth2.. .......0.4246.. ...+0.0004..... ...+0.04....... ....01/13/2007........... ......1
4.............. ......Growth5.. .......0.4225.. ...-0.0059.........-1.36........... .01/13/2007........... ......1
5.............. ......Growth3.. .......0.3794.. ...+0.0004..... ....+0.04...... ....01/13/2007........... ......1
6.............. ......Growth8.. .......0.2159.. ...-0.0080.........-1.04........... .01/13/2007........... ......1
8.............. ......Growth6.. .......0.9471.. ...-0.0037.........-0.96........... .01/13/2007........... ......1

The CHANGE is calculate based on NAV of previous date (01/12/2007).
The CHANGE% is calculate % based on NAV of previous date (01/12/2007).

For the CHANGE and CHANGE% column data are just sample, incorrect data.

Another thing, ONLY records with display_web column value = "1" is display the result. Records value with "0" are not displayed.

Well i hope u guys pls pls help me.....THX.....

[JamieHowarth0]

Hi vinothrao,

Have you tried coding any of this yourself so far?

The idea of the forum is to provide help and guidance with code-specific problems, not to write your code for you. All experts contribute their time and expertise freely (which is of considerable value) and without expectation of recompense - but in return, we expect you to have at least made attempts at what you are trying to do.
If in any doubt then please read the Posting Guidelines.

Best regards,

medicineworker

[jamesd0142]

[code=sql]
if exists (select * from sysobjects where [name] = 'temp1') drop temp1
Select <tablename1>.<c olumn1> as <name1>, <tablename2>.<c olumn2> as <name2>
inner join <table2>
on <table1>.<colum nName> = <table2>.<colum nName>
into temp1
from tbl1 as <tablename1>, tbl2 as <tablename2>
order by <column>
select * from temp1
[/code]

something along this, syntax is not correct