RE: Quick SQL assistance

From: D'Hooge Freek <Freek.DHooge_at_uptime.be>
Date: Mon, 14 Sep 2009 22:55:53 +0200
Message-ID: <4814386347E41145AAE79139EAA398980C71FCDA5C_at_ws03-exch07.iconos.be>

Is this what you are looking for?
(note the nls_territory setting, as 'DAY' in trunc depends on the nls settings)

sys_at_GUNNAR> create table test (host_name varchar2(20), snap_date date, tbs_size number(10,0)); Table created.
sys_at_GUNNAR> insert into test values ('HostA', to_date('1/09/2009', 'DD/MM/YYYY'), 99); 1 row created.
sys_at_GUNNAR> insert into test values ('HostA', to_date('2/09/2009', 'DD/MM/YYYY'), 80); 1 row created.
sys_at_GUNNAR> insert into test values ('HostB', to_date('1/09/2009', 'DD/MM/YYYY'), 100); 1 row created.
sys_at_GUNNAR> insert into test values ('HostB', to_date('3/09/2009', 'DD/MM/YYYY'), 110); 1 row created.
sys_at_GUNNAR> insert into test values ('HostA', to_date('7/09/2009', 'DD/MM/YYYY'), 500); 1 row created.
sys_at_GUNNAR> insert into test values ('HostA', to_date('10/09/2009', 'DD/MM/YYYY'), 600); 1 row created.
sys_at_GUNNAR> insert into test values ('HostB', to_date('7/09/2009', 'DD/MM/YYYY'), 210); 1 row created.
sys_at_GUNNAR> insert into test values ('HostB', to_date('8/09/2009', 'DD/MM/YYYY'), 410); 1 row created.
sys_at_GUNNAR> insert into test values ('HostC', to_date('8/09/2009', 'DD/MM/YYYY'), 410); 1 row created.
sys_at_GUNNAR> commit;
Commit complete.

sys_at_GUNNAR> select * from test order by host_name, snap_date;

HOST_NAME                      SNAP_DAT   TBS_SIZE
------------------------------ -------- ----------
HostA                          01/09/09         99
HostA                          02/09/09         80
HostA                          07/09/09        500
HostA                          10/09/09        600
HostB                          01/09/09        100
HostB                          03/09/09        110
HostB                          07/09/09        210
HostB                          08/09/09        410
HostC                          08/09/09        410

9 rows selected.

sys_at_GUNNAR> alter session set NLS_TERRITORY='BELGIUM'; Session altered.
sys_at_GUNNAR> select *
  2 from ( select host_name,

  3                first_value(snap_date)
  4                  over ( partition by host_name, trunc(snap_date, 'DAY')
  5                         order by host_name, snap_date
  6                       ) snap_date,
  7                first_value(tbs_size)
  8                  over ( partition by host_name, trunc(snap_date, 'DAY')
  9                         order by host_name, snap_date
 10                       ) tbs_size
 11         from test
 12       )

 13 group by host_name, snap_date, tbs_size  14 order by host_name, snap_date;

HOST_NAME                      SNAP_DAT   TBS_SIZE
------------------------------ -------- ----------
HostA                          01/09/09         99
HostA                          07/09/09        500
HostB                          01/09/09        100
HostB                          07/09/09        210
HostC                          08/09/09        410

Freek D'Hooge
Uptime
Oracle Database Administrator
e-mail: freek.dhooge_at_uptime.be
tel. +32 (0)3 451 23 82
http://www.uptime.be
disclaimer

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Taylor, Chris David [ChrisDavid.Taylor_at_ingrambarge.com] Sent: 14 September 2009 22:09
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:55:53 CDT