Re: Help needed to find minimum in a series

From: Carlos <miotromailcarlos_at_netscape.net>
Date: 18 Apr 2007 07:52:06 -0700
Message-ID: <1176907926.146247.224990@q75g2000hsh.googlegroups.com>

> Help please!!!

Cavalry to thr rescue:

carlos_at_db01.xxxxxxxx> CREATE TABLE TABLE_1 (USERID NUMBER, STATUS VARCHAR2(4))
  2 /

Tabla creada.

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> CREATE TABLE TABLE_2 (USERID NUMBER, STATUS VARCHAR2(4), START_DATE DATE)
  2 /

Tabla creada.

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> INSERT INTO TABLE_1 (USERID, STATUS) VALUES

(1234, 'A001')
  2 /

1 fila creada.

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> INSERT INTO TABLE_1 (USERID, STATUS) VALUES (3456, 'C001')
  2 /

1 fila creada.

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> INSERT INTO TABLE_2 (USERID, STATUS, START_DATE)   2 VALUES (1234,'A001',TO_DATE('01/07/2007','DD/MM/ YYYY'))
  3 /

1 fila creada.

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> INSERT INTO TABLE_2 (USERID, STATUS, START_DATE)   2 VALUES (1234,'A001',TO_DATE('01/01/2007','DD/MM/ YYYY'))
  3 /

1 fila creada.

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> INSERT INTO TABLE_2 (USERID, STATUS, START_DATE)
  2               VALUES (1234,'B001',TO_DATE('01/10/2006','DD/MM/

YYYY'))
  3 /

1 fila creada.

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> INSERT INTO TABLE_2 (USERID, STATUS, START_DATE)   2 VALUES (1234,'A001',TO_DATE('01/07/2006','DD/MM/ YYYY'))
  3 /

1 fila creada.

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> INSERT INTO TABLE_2 (USERID, STATUS, START_DATE)   2 VALUES (1234,'A001',TO_DATE('01/01/2006','DD/MM/ YYYY'))
  3 /

1 fila creada.

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> INSERT INTO TABLE_2 (USERID, STATUS, START_DATE)   2 VALUES (1234,'B001',TO_DATE('01/01/2005','DD/MM/ YYYY'))
  3 /

1 fila creada.

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> INSERT INTO TABLE_2 (USERID, STATUS, START_DATE)   2 VALUES (1234,'A001',TO_DATE('01/01/2004','DD/MM/ YYYY')); 1 fila creada.

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> COMMIT
  2 /

Confirmación terminada.

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> SELECT * FROM TABLE_1   2 /

    USERID STAT
---------- ----

      1234 A001
      3456 C001

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> SELECT * FROM TABLE_2 ORDER BY USERID, START_DATE
  2 /

    USERID STAT START_DATE

---------- ---- -------------------
      1234 A001 01/01/2004 00:00:00
      1234 B001 01/01/2005 00:00:00
      1234 A001 01/01/2006 00:00:00
      1234 A001 01/07/2006 00:00:00
      1234 B001 01/10/2006 00:00:00
      1234 A001 01/01/2007 00:00:00
      1234 A001 01/07/2007 00:00:00

7 filas seleccionadas.

carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx>
carlos_at_db01.xxxxxxxx> SELECT a.USERID, a.STATUS, c.START_DATE
  2    FROM TABLE_1 a,
  3         ( SELECT USERID, STATUS, START_DATE, RANK() OVER
(PARTITION BY USERID ORDER BY START_DATE DESC) ORDEN
  4             FROM ( SELECT USERID, STATUS, START_DATE, LAG(STATUS)
OVER (PARTITION BY USERID ORDER BY START_DATE) ANT_STATUS
  5                      FROM TABLE_2
  6                   ) b
  7            WHERE b.STATUS != b.ANT_STATUS
  8               ) c
  9   WHERE a.USERID = c.USERID
 10     AND a.STATUS = c.STATUS
 11     AND c.ORDEN = 1

 12 /

    USERID STAT START_DATE

---------- ---- -------------------
      1234 A001 01/01/2007 00:00:00

carlos_at_db01.xxxxxxxx>

HTH Cheers.

Carlos. Received on Wed Apr 18 2007 - 09:52:06 CDT