help with analytic in oracle 9i, please

From: <epipko_at_gmail.com>
Date: 31 Aug 2005 09:07:33 -0700
Message-ID: <1125504453.028080.223430@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 Received on Wed Aug 31 2005 - 11:07:33 CDT