Re: Quick SQL assistance

From: chet justice <chet.justice_at_gmail.com>
Date: Mon, 14 Sep 2009 16:22:44 -0400
Message-ID: <8311a5b60909141322k188a14a1p1fdf1f641941ad7d_at_mail.gmail.com>

What about TO_CHAR( snap_date, 'WW' )?

SELECT
  TO_CHAR( SYSDATE, 'WW' ) ww,
  TRUNC( SYSDATE, 'WW' ) dd
FROM dual;

WW DD
---------- ---------
37 10-SEP-09 On Mon, Sep 14, 2009 at 4:09 PM, Taylor, Chris David < ChrisDavid.Taylor_at_ingrambarge.com> wrote:

> Ok, I'm drawing a blank here as my brain seems to hvae taken the
> afternoon off.
>
> I have a dataset:
>
> HOST_NAME TARGET_NAME SNAP_DATE TABLESPACE_SIZE
> ------------------- ------------------------ -------------------
> -----------------------------
> HostA Dev1 9/1/2009 99,999
> HostA Dev1 9/2/2009 99,999
> HostA Dev1 9/3/2009 99,999
> ...
> ...
> HostA Dev2 9/1/2009 89,999
> HostA Dev2 9/2/2009 89,999
> HostA Dev2 9/3/2009 89,999
>
> What I need to do is come up with a query that takes the above data and
> ONLY shows dates of weekly intervals. I need to get the following dataset:
>
> (Weekly Snapshot)
> HostA Dev1 9/1/2009 99,999
> HostA Dev1 9/8/2009 101,999
> HostA Dev2 9/1/2009 89,999
> HostA Dev2 9/8/2009 91,999
>
> I think I need to use an analytic function of some sort, but I cannot seem
> to come up with the syntax.
>
> Anyone have any suggestions/thoughts? (Obviously I could create another
> date table as a driver table and join the dates to it, but I know there's a
> simpler way)
>
>
>
> *Chris Taylor*
> *Sr. Oracle DBA*
> Ingram Barge Company
> Nashville, TN 37205
> Office: 615-517-3355
> Cell: 615-354-4799
> Email: chris.taylor_at_ingrambarge.com
>
>
> *CONFIDENTIALITY NOTICE**: This e-mail and any attachments are
> confidential and may also be privileged. If you are not the named recipient,
> please notify the sender immediately and delete the contents of this message
> without disclosing the contents to anyone, using them for any purpose, or
> storing or copying the information on any medium.*
>
>

-- 
chet justice
www.oraclenerd.com

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 14 2009 - 15:22:44 CDT