select statement - please help

From: roshan <aiyoo2_at_aol.com>
Date: 8 Feb 2004 05:48:30 -0800
Message-ID: <431763b2.0402080548.2f019637@posting.google.com>

Hi,
I am working with 2 tables that have to do with unix filesystems and the free space in them for a given a date.

Table A                              Table B
--------                             -------
file_sys_name                        intvl
interval_index                       date

free_space
total_space

The join from A to B is done by A.interval_index=B.intvl

Given a start and end date, I am trying to calculate the percent growth
for each file_sys_name.

End Result Needed :

file_sys_name      %free_at_start_date   %free_at_end_date   %change
-------------      -------------------   ---------------     -------


/usr               50                    40                   -10

/var               27                    23                   -4

I am using oracle 8.05, can anybody suggest a single query that will work?
I tried this(below), but I am getting an error. (I am a newbie in SQL)

SELECT A.file_sys_name,
  (SELECT C.free_space/C.total_space*100 FROM A C, B

     WHERE C.interval_index=B.intvl
     AND B.date='start_date'
     AND A.file_sys_name=C.file_sys_name) AS %free_at_start_date,

  (SELECT C.free_space/C.total_space*100 FROM A C, B
     WHERE C.interval_index=B.intvl
     AND B.date='end_date'
     AND A.file_sys_name=C.file_sys_name) AS %free_at_end_date

FROM A

• note : I haven't done the growth column which will be the 2nd indented select minus the 1st indented select. (Is there an easy way to do that)

thanks in advance for any help,
Roshan Received on Sun Feb 08 2004 - 07:48:30 CST