Good call, a filter like this would do the trick: (days(f.backup_end)<(days(current_date)-30) OR f.backup_end IS NULL)
On Thu, Jan 28, 2016 at 04:46:17PM +0100, Erwann SIMON wrote: > Hi Eric, > > You maybe should add a condition if backup_end is null > > -- > Best regards / Cordialement / ???? ???????????? > Erwann SIMON > > ----- Mail original ----- > De: "EJ van Loon (ITOPT3) - KLM" <eric-van.l...@klm.com> > ?: ADSM-L@VM.MARIST.EDU > Envoy?: Jeudi 28 Janvier 2016 16:20:57 > Objet: Re: [ADSM-L] SQL statement > > Hi Skylar! > This is exactly what I was looking for. Thank you very much for your help!!! > Kind regards, > Eric van Loon > AF/KLM Storage Engineering > > -----Original Message----- > From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of > Skylar Thompson > Sent: donderdag 28 januari 2016 15:59 > To: ADSM-L@VM.MARIST.EDU > Subject: Re: SQL statement > > 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 > ******************************************************** > 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