RE: Quick SQL assistance
- From: D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
- To: "ChrisDavid.Taylor@xxxxxxxxxxxxxxx" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>, 'Oracle L' <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 14 Sep 2009 22:55:53 +0200
Is this what you are looking for?
(note the nls_territory setting, as 'DAY' in trunc depends on the nls settings)
sys@GUNNAR> create table test (host_name varchar2(20), snap_date date, tbs_size
number(10,0));
Table created.
sys@GUNNAR> insert into test values ('HostA', to_date('1/09/2009',
'DD/MM/YYYY'), 99);
1 row created.
sys@GUNNAR> insert into test values ('HostA', to_date('2/09/2009',
'DD/MM/YYYY'), 80);
1 row created.
sys@GUNNAR> insert into test values ('HostB', to_date('1/09/2009',
'DD/MM/YYYY'), 100);
1 row created.
sys@GUNNAR> insert into test values ('HostB', to_date('3/09/2009',
'DD/MM/YYYY'), 110);
1 row created.
sys@GUNNAR> insert into test values ('HostA', to_date('7/09/2009',
'DD/MM/YYYY'), 500);
1 row created.
sys@GUNNAR> insert into test values ('HostA', to_date('10/09/2009',
'DD/MM/YYYY'), 600);
1 row created.
sys@GUNNAR> insert into test values ('HostB', to_date('7/09/2009',
'DD/MM/YYYY'), 210);
1 row created.
sys@GUNNAR> insert into test values ('HostB', to_date('8/09/2009',
'DD/MM/YYYY'), 410);
1 row created.
sys@GUNNAR> insert into test values ('HostC', to_date('8/09/2009',
'DD/MM/YYYY'), 410);
1 row created.
sys@GUNNAR> commit;
Commit complete.
sys@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@GUNNAR> alter session set NLS_TERRITORY='BELGIUM';
Session altered.
sys@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@xxxxxxxxx
tel. +32 (0)3 451 23 82
http://www.uptime.be
disclaimer
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Taylor, Chris David [ChrisDavid.Taylor@xxxxxxxxxxxxxxx]
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@xxxxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
