Hi EJ,

I think this will do the trick:

SELECT -
   f.node_name, -
   f.filespace_name, -
   f.backup_end, -
   CAST(ROUND(o.physical_mb/1024) as int) as "GB Stored" -
FROM -
   filespaces f, -
   occupancy o -
WHERE -
   o.node_name=f.node_name -
   AND o.filespace_name=f.filespace_name -
   AND days(f.backup_end)<(days(current_date)-30) -
ORDER BY o.physical_mb DESC

On Thu, Jan 28, 2016 at 02:43:54PM +0000, Loon, EJ van (ITOPT3) - KLM wrote:
> Hi guys!
> I'm trying to join the filespaces and occupancy tables in one SQL statement, 
> but I get stuck on the point where I want to calculate with the amount of 
> days...
> I would like to create a list of filespaces (node_name, 
> filespace_name,backup_end) which are not backed up for more than 30 days, 
> along with the amount of data stored for them in GB 
> (cast(round(physical_mb/1024) as int) as "GB Stored"), sorted on size, the 
> largest first.
> Can anybody help me out here? Thank you very much for your help in advance!
> Kind regards,
> Eric van Loon
> AF/KLM Storage Engineering
> ********************************************************
> For information, services and offers, please visit our web site: 
> http://www.klm.com. This e-mail and any attachment may contain confidential 
> and privileged material intended for the addressee only. If you are not the 
> addressee, you are notified that no part of the e-mail or any attachment may 
> be disclosed, copied or distributed, and that any other action related to 
> this e-mail or attachment is strictly prohibited, and may be unlawful. If you 
> have received this e-mail by error, please notify the sender immediately by 
> return e-mail, and delete this message.
>
> Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
> employees shall not be liable for the incorrect or incomplete transmission of 
> this e-mail or any attachments, nor responsible for any delay in receipt.
> Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
> Airlines) is registered in Amstelveen, The Netherlands, with registered 
> number 33014286
> ********************************************************

--
-- Skylar Thompson (skylar2 AT u.washington DOT edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine