Bill where are you putting the “DAYS” part of the query? I’m a bit confused and 
can’t reproduce your statement.

-- 
Hal 





On 1/28/16, 10:11 AM, "ADSM: Dist Stor Manager on behalf of William Sefranek" 
<ADSM-L AT VM.MARIST DOT EDU on behalf of wtsefran AT BUFFALO DOT EDU> wrote:

>Eric,
>
>One other line you can add to the select section is:
>DAYS(current_timestamp)-DAYS(f.BACKUP_END) AS DAYS_SINCE_BACKUP -
>
>Which will list the number of days since this filespace completed a
>backup. I use that in our query to list old filespaces due for cleanup.
>
>Bill
>
>On 1/28/2016 9:58 AM, Skylar Thompson wrote:
>> 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
>>
>>
>
>--
>William Sefranek
>University of Buffalo
>Enterprise Infrastructure Services
>(716)645-5116