Home » SQL & PL/SQL » SQL & PL/SQL » Help needed in a procedure.
Help needed in a procedure. [message #200080] Fri, 27 October 2006 15:33 Go to next message
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.
Re: Help needed in a procedure. [message #200266 is a reply to message #200080] Mon, 30 October 2006 02:59 Go to previous message
Maaher
Messages: 7062
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
Previous Topic: Callling oracle func with ADO
Next Topic: ORA-00600:internal......arguments:[6807].....
Goto Forum:
  


Current Time: Sun Dec 04 08:28:10 CST 2016

Total time taken to generate the page: 0.07361 seconds