OraFAQ Forum: SQL & PL/SQL » Help needed in a procedure.

Home » SQL & PL/SQL » SQL & PL/SQL » Help needed in a procedure.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help needed in a procedure. [message #200080]

Fri, 27 October 2006 15:33

sudkool
Messages: 12
Registered: May 2006

Junior Member

Hi,
I am trying to write a store procedure which can serve the following purpose

Member# Start Dt End Dt Transaction Dt Profit Share
1234 01/11/06 01/31/06 03/03/06 123.00
1234 02/01/06 02/28/06 03/03/06 222.00 **
1234 03/01/06 03/31/06 03/03/06 222.00
3233 01/11/06 01/31/06 03/13/06 123.00
3233 02/01/06 02/28/06 03/13/06 0.00 **
3233 03/01/06 03/31/06 03/13/06 222.00 **
3233 04/01/06 04/30/06 03/13/06 0.00 **

Here the example of the output I will have. I am trying to write a query which will check the records and ignore the first row of the member (always), and then if the profit share a user is getting is different from the last month then display else go to next record. I had the query where I was using Min function on all the dates and then putting the profit share on group by. It was working fine till I encounter the last row. For member 3233 profit share of 02/01 and 04/01 is same, so it was talking only one row, while I am trying to show the both rows, as the record is different from the immediate last record of the member. Could someome please help me with it. ** shows the records which should be displayed.

Thank you in advance.

Report message to a moderator

Re: Help needed in a procedure. [message #200266 is a reply to message #200080]

Mon, 30 October 2006 02:59

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Here's what I came up with:

SQL> CREATE TABLE mhe_foo(member_id number, seqno number, rate number)
  2  /

Table created.

SQL>
SQL> INSERT INTO mhe_foo VALUES (1234, 1, 123.00 );

1 row created.

SQL> INSERT INTO mhe_foo VALUES (1234, 2, 222.00 );

1 row created.

SQL> INSERT INTO mhe_foo VALUES (1234, 3, 222.00 );

1 row created.

SQL> INSERT INTO mhe_foo VALUES (3233, 1, 123.00 );

1 row created.

SQL> INSERT INTO mhe_foo VALUES (3233, 2, 0.00   );

1 row created.

SQL> INSERT INTO mhe_foo VALUES (3233, 3, 222.00 );

1 row created.

SQL> INSERT INTO mhe_foo VALUES (3233, 4, 0.00   );

1 row created.

SQL>
SQL> SELECT member_id
  2       , seqno
  3       , rate
  4  FROM   mhe_foo
  5  ORDER BY member_id, seqno
  6  /

 MEMBER_ID      SEQNO       RATE
---------- ---------- ----------
      1234          1        123
      1234          2        222
      1234          3        222
      3233          1        123
      3233          2          0
      3233          3        222
      3233          4          0

7 rows selected.

SQL>
SQL> SELECT member_id
  2       , rate
  3  FROM ( SELECT member_id
  4              , rate
  5              , LAG(rate) OVER ( PARTITION BY member_id
  6                                 ORDER BY seqno ) prev_rate
  7         FROM   mhe_foo
  8         ORDER  BY member_id, seqno
  9       )
 10  WHERE prev_rate != rate
 11  /

 MEMBER_ID       RATE
---------- ----------
      1234        222
      3233          0
      3233        222
      3233          0

SQL>
SQL>
SQL> DROP TABLE mhe_foo
  2  /

Table dropped.

It's like Tom Kyte said: analytic functions rock! I based my order on a sequence number, but you could as easily take one of your date columns.

Next time, provide your own create table script.

MHE

Report message to a moderator

Previous Topic:	Callling oracle func with ADO
Next Topic:	ORA-00600:internal......arguments:[6807].....

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 04:54:46 CST 2024