Calculation on same table with same column

[vinothrao84]

hi,

i have the 2 following table in my database.

tablename : tblMutFunds

code.......shortname......number......display_web
G1..........Growth1......... 1...............1
G2..........Growth2......... 3...............1
G3..........Growth3......... 5...............1
G4..........Growth4......... 2...............0
G5..........Growth5......... 4...............1
G6..........Growth6......... 8...............1
G7..........Growth7......... 7...............0
G8..........Growth8......... 6...............1
G9..........Growth9......... 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......Fund_Name.......NAV.........Change.... ....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]

if exists (select * from sysobjects where [name] = 'temp1')   drop temp1 
Select <tablename1>.<column1> as  <name1>, <tablename2>.<column2> as <name2>
inner join <table2> 
on <table1>.<columnName> = <table2>.<columnName>
into temp1
from tbl1 as <tablename1>, tbl2 as <tablename2>
order by <column>
select * from temp1
 

something along this, syntax is not correct