It would actually be in the table expression at the top: SELECT ... DAYS(current_timestamp)-DAYS(f.BACKUP_END) AS DAYS_SINCE_BACKUP, - FROM - ...
On Thu, Jan 28, 2016 at 03:46:43PM +0000, King, Harold Clyde (Hal) wrote: > 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 -- -- Skylar Thompson (skyl...@u.washington.edu) -- Genome Sciences Department, System Administrator -- Foege Building S046, (206)-685-7354 -- University of Washington School of Medicine
