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@VM.MARIST.EDU on behalf of wtsef...@buffalo.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 (skyl...@u.washington.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
