help with analytic in oracle 9i, please

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

epipko@gmail.com - 31 Aug 2005 17:07 GMT

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

Reply to this Message

Michel Cadot - 31 Aug 2005 17:45 GMT

| Hi all,
| I have procedure that calculates values and pupulates them in a table
[quoted text clipped - 71 lines]
| 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

Reply to this Message

epipko@gmail.com - 31 Aug 2005 18:21 GMT

Thank you, Michel

Reply to this Message