Re: Using Analytics......

From: Alien <stijf_at_stijf.com>
Date: 24 Feb 2006 04:54:49 -0800
Message-ID: <1140785689.678904.305410@t39g2000cwt.googlegroups.com>

Hi,

This should work. Concatenate date and text together. Then pick the high and low, and separate the values again.

SQL> create table test (text varchar2(50),month date);

Table created.

SQL> insert into test values ('A',to_date('1-jan-2000','DD-MON-YYYY'));

1 row created.

SQL> insert into test values ('B',to_date('5-may-2000','DD-MON-YYYY'));

1 row created.

SQL> insert into test values
('C',to_date('20-feb-2006','DD-MON-YYYY'));

1 row created.

SQL> select substr(sd,9,1) low

  2  ,      to_date(substr(sd,1,8),'YYYYMMDD') start_date
  3  ,      substr(ed,9,1) high
  4  ,      to_date(substr(ed,1,8),'YYYYMMDD') end_date
  5  ,      result

  6 from (
  7 select min(to_char(month,'YYYYMMDD')||text) over () sd

  8  ,      max(to_char(month,'YYYYMMDD')||text) over () ed
  9  ,      count(*) over () result

 10 from test)
 11 where rownum=1;

L START_DA H END_DATE RESULT
- -------- - -------- ----------
A 01-01-00 C 20-02-06 3

SQL> regards,

Arian

Miggins wrote:
> Hi,
>
> Wanting to run a query which will do the following.
>
> Extract information from the first and last record in the query and
> also the number of records processed.
>
> So a table containing the following.....
>
> A 01-JAN-2000
> B 05-MAY-2003
> C 20-FEB-2006
>
> Would return.....
>
> A 01-JAN-2000 C 20-FEB-2006 3
>
> I know you can do this using analytics.....any ideas....also any
> resources explaining more about them.
>
> Cheers
Received on Fri Feb 24 2006 - 06:54:49 CST