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.Loon AT KLM DOT COM>
> ?: ADSM-L AT VM.MARIST DOT 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 AT VM.MARIST DOT EDU] On Behalf
> Of Skylar Thompson
> Sent: donderdag 28 januari 2016 15:59
> To: ADSM-L AT VM.MARIST DOT 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 (skylar2 AT u.washington DOT 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 (skylar2 AT u.washington DOT edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine
|
