Re: help with analytic in oracle 9i, please

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 31 Aug 2005 18:45:38 +0200
Message-ID: <4315deaf$0$3781$636a15ce@news.free.fr>

<epipko_at_gmail.com> a écrit dans le message de news: 1125504453.028080.223430_at_o13g2000cwo.googlegroups.com...
| Hi all,
| I have procedure that calculates values and pupulates them in a table
| nightly, once a day. I was asked to come up with a sql statement that
| will compaire entries from today's run and previous night run. I can
| write a plsql proc, but thinking about LAG() and need your help.
|
| I need to compaire today's net_booked_qty and net_booked_dllrs with
| yesterday's for same customer, division.
|
| Here is the sample data:
| ------------------------
| CREATE TABLE T1
| (
| TODAY VARCHAR2(10 BYTE),
| CUSTOMER_ID VARCHAR2(10 BYTE) NOT NULL,
| DIVISION_ID VARCHAR2(5 BYTE) NOT NULL,
| TODAY_MONTH VARCHAR2(6 BYTE),
| NET_BOOKED_QTY NUMBER,
| NET_BOOKED_DLLRS NUMBER
| );
|
|
| Insert into T1
| (TODAY, CUSTOMER_ID, DIVISION_ID, TODAY_MONTH, NET_BOOKED_QTY,
| NET_BOOKED_DLLRS)
| Values
| ('08/30/2005', 'KOHLS', 'BOY', '200508', 41424, 407573);
| Insert into T1
| (TODAY, CUSTOMER_ID, DIVISION_ID, TODAY_MONTH, NET_BOOKED_QTY,
| NET_BOOKED_DLLRS)
| Values
| ('08/30/2005', 'KOHLS', 'GRL', '200508', 74496, 875328);
| Insert into T1
| (TODAY, CUSTOMER_ID, DIVISION_ID, TODAY_MONTH, NET_BOOKED_QTY,
| NET_BOOKED_DLLRS)
| Values
| ('08/30/2005', 'KOHLS', 'JRS', '200508', 25926, 342291);
| Insert into T1
| (TODAY, CUSTOMER_ID, DIVISION_ID, TODAY_MONTH, NET_BOOKED_QTY,
| NET_BOOKED_DLLRS)
| Values
| ('08/30/2005', 'KOHLS', 'REU', '200508', 42954, 560532.72);
| Insert into T1
| (TODAY, CUSTOMER_ID, DIVISION_ID, TODAY_MONTH, NET_BOOKED_QTY,
| NET_BOOKED_DLLRS)
| Values
| ('08/30/2005', 'KOHLS', 'YMS', '200508', 118704, 1481204);
| Insert into T1
| (TODAY, CUSTOMER_ID, DIVISION_ID, TODAY_MONTH, NET_BOOKED_QTY,
| NET_BOOKED_DLLRS)
| Values
| ('08/31/2005', 'KOHLS', 'BOY', '200508', 41420, 407571);
| Insert into T1
| (TODAY, CUSTOMER_ID, DIVISION_ID, TODAY_MONTH, NET_BOOKED_QTY,
| NET_BOOKED_DLLRS)
| Values
| ('08/31/2005', 'KOHLS', 'GRL', '200508', 74490, 875329);
| Insert into T1
| (TODAY, CUSTOMER_ID, DIVISION_ID, TODAY_MONTH, NET_BOOKED_QTY,
| NET_BOOKED_DLLRS)
| Values
| ('08/31/2005', 'KOHLS', 'JRS', '200508', 25920, 342292);
| Insert into T1
| (TODAY, CUSTOMER_ID, DIVISION_ID, TODAY_MONTH, NET_BOOKED_QTY,
| NET_BOOKED_DLLRS)
| Values
| ('08/31/2005', 'KOHLS', 'REU', '200508', 42953, 560530.1);
| Insert into T1
| (TODAY, CUSTOMER_ID, DIVISION_ID, TODAY_MONTH, NET_BOOKED_QTY,
| NET_BOOKED_DLLRS)
| Values
| ('08/31/2005', 'KOHLS', 'YMS', '200508', 118703, 1481205);
| COMMIT;
|
| Thanks,
| Eugene
|

SQL> select customer_id, division_id, today, net_booked_qty, net_booked_dllrs,
  2  lag (net_booked_qty) over (partition by customer_id, division_id order by today) prev_net_booked_qty,
  3  lag (net_booked_dllrs) over (partition by customer_id, division_id order by today) prev_net_booked_dllrs

  4 from t1
  5 order by customer_id, today;
CUSTOMER_I DIVIS TODAY NET_BOOKED_QTY NET_BOOKED_DLLRS PREV_NET_BOOKED_QTY PREV_NET_BOOKED_DLLRS

---------- ----- ---------- -------------- ---------------- ------------------- ---------------------
KOHLS      BOY   08/30/2005          41424           407573
KOHLS      GRL   08/30/2005          74496           875328
KOHLS      REU   08/30/2005          42954        560532.72
KOHLS      YMS   08/30/2005         118704          1481204
KOHLS      JRS   08/30/2005          25926           342291
KOHLS      BOY   08/31/2005          41420           407571               41424                407573
KOHLS      GRL   08/31/2005          74490           875329               74496                875328
KOHLS      REU   08/31/2005          42953         560530.1               42954             560532.72
KOHLS      YMS   08/31/2005         118703          1481205              118704               1481204
KOHLS      JRS   08/31/2005          25920           342292               25926                342291

10 rows selected.

Regards
Michel Cadot Received on Wed Aug 31 2005 - 11:45:38 CDT