RE: Quick SQL assistance

From: Bellows, Bambi (Comsys) <"Bellows,>
Date: Mon, 14 Sep 2009 15:20:12 -0500
Message-ID: <AD0CB572A820AB4E8E52ABD38950FD3606AEADD7_at_a0001-xpo0150-s.hodc.ad.allstate.com>

If it were me, I'd pick a day of the week, and use to_char(<date>,'D') = x (e.g., 1 for Sunday)  

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Taylor, Chris David Sent: Monday, September 14, 2009 3:09 PM To: 'Oracle L'
Subject: Quick SQL assistance  

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.  

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

Received on Mon Sep 14 2009 - 15:20:12 CDT