Home » SQL & PL/SQL » SQL & PL/SQL » QUERY to get balances of running total (10g R2 , win 2003 SRV)
QUERY to get balances of running total [message #441382] |
Sun, 31 January 2010 02:37  |
micro_oracle
Messages: 41 Registered: December 2007 Location: UAE
|
Member |
 
|
|
Dear All,
I need to get the running total of differences between Received quantity and ordered quantity
lets say : if I ordered from ITEM 'X' a quantity of 300 Pcs, and I received the following quantity as the following schedule :
1- GRN 1 QTY = 20, I need the running total to be showing 280
2- GRN 2 QTY = 80, I need the running total to be showing 200
3- GRN 3 QTY = 120, I need the running total to be showing 80
thus please look into the following code :
create table local_purchase_order
(
po_txn_code varchar2(3),
po_no number(6,2),
po_date date,
po_item_code varchar2(8),
po_unique_reference_code varchar2(6),
po_quantity number(8,2));
ALTER TABLE local_purchase_order ADD CONSTRAINT UNQ_PO_REF_PK PRIMARY KEY (po_unique_reference_code);
create table good_receipt_note
(
grn_txn_code varchar2(3),
grn_no number(6,2),
grn_date date,
grn_item_code varchar2(8),
grn_unique_reference_code varchar2(6),
grn_quantity number(8,2),
PO_UNQ_REF_CODE varchar2(6))
ALTER TABLE good_receipt_note ADD CONSTRAINT UNQ_GRN_REF_PK PRIMARY KEY (grn_unique_reference_code);
ALTER TABLE good_receipt_note
add CONSTRAINT GRN_PO_FK
FOREIGN KEY (PO_UNQ_REF_CODE)
REFERENCES LOCAL_PURCHASE_ORDER(PO_UNIQUE_REFERENCE_CODE);
insert into local_purchase_order values ('LPO',1001,'01-JAN-2010','C1000101','REF201',550);
insert into local_purchase_order values ('LPO',1010,'10-JAN-2010','C1000120','REF202',200);
insert into good_receipt_note values ('GRN',1101,'03-JAN-2010','C1000101','GRN800',100,'REF201');
insert into good_receipt_note values ('GRN',1102,'05-JAN-2010','C1000101','GRN801',50,'REF201');
insert into good_receipt_note values ('GRN',1103,'09-JAN-2010','C1000101','GRN802',80,'REF201');
insert into good_receipt_note values ('GRN',1104,'22-JAN-2010','C1000101','GRN803',70,'REF201');
insert into good_receipt_note values ('GRN',1105,'03-FEB-2010','C1000101','GRN810',50,'REF201');
insert into good_receipt_note values ('GRN',1106,'15-JAN-2010','C1000120','GRN811',20,'REF202');
insert into good_receipt_note values ('GRN',1107,'31-JAN-2010','C1000120','GRN812',80,'REF202');
insert into good_receipt_note values ('GRN',1108,'18-MAR-2010','C1000120','GRN813',40,'REF202');
commit;
SELECT PO_TXN_CODE, PO_NO, PO_DATE, PO_ITEM_CODE, PO_UNIQUE_REFERENCE_CODE,
GRN_TXN_CODE, GRN_NO, GRN_DATE,PO_QUANTITY, GRN_QUANTITY
FROM local_purchase_order,good_receipt_note
WHERE PO_UNIQUE_REFERENCE_CODE = PO_UNQ_REF_CODE
PO_TXN_CODE PO_NO PO_DATE PO_ITEM_CODE PO_UNIQUE_REFERENCE_CODE GRN_TXN_CODE GRN_NO GRN_DATE PO_QUANTITY GRN_QUANTITY
LPO 1001 1/1/2010 C1000101 REF201 GRN 1101 1/3/2010 550 100
LPO 1001 1/1/2010 C1000101 REF201 GRN 1102 1/5/2010 550 50
LPO 1001 1/1/2010 C1000101 REF201 GRN 1103 1/9/2010 550 80
LPO 1001 1/1/2010 C1000101 REF201 GRN 1104 1/22/2010 550 70
LPO 1001 1/1/2010 C1000101 REF201 GRN 1105 2/3/2010 550 50
LPO 1010 1/10/2010 C1000120 REF202 GRN 1106 1/15/2010 200 20
LPO 1010 1/10/2010 C1000120 REF202 GRN 1107 1/31/2010 200 80
LPO 1010 1/10/2010 C1000120 REF202 GRN 1108 3/18/2010 200 40
then if it's possible to have last result (last row) for each item in each po
thus it would be like this :
Total PO Qty Total GRN Qty Balance
300 220 80
and now the most complix condition which it's difficult to acheive: that if I need to show only those requested ITEMS in PO's whose having a pending quantities to be delivered.
can any one help me, please
|
|
|
Re: QUERY to get balances of running total [message #441387 is a reply to message #441382] |
Sun, 31 January 2010 03:01   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
First question:
SQL> with
2 data as (
3 SELECT PO_TXN_CODE, PO_NO, PO_DATE, PO_ITEM_CODE, PO_UNIQUE_REFERENCE_CODE,
4 GRN_TXN_CODE, GRN_NO, GRN_DATE,PO_QUANTITY, GRN_QUANTITY
5 FROM local_purchase_order,good_receipt_note
6 WHERE PO_UNIQUE_REFERENCE_CODE = PO_UNQ_REF_CODE
7 )
8 select po_no, grn_no, po_quantity,
9 sum(GRN_QUANTITY) over (partition by po_no order by grn_no) granted,
10 po_quantity-sum(GRN_QUANTITY) over (partition by po_no order by grn_no) balance
11 from data
12 order by po_no, grn_no
13 /
PO_NO GRN_NO PO_QUANTITY GRANTED BALANCE
---------- ---------- ----------- ---------- ----------
1001 1102 550 50 500
1001 1103 550 130 420
1001 1104 550 200 350
1001 1105 550 250 300
1010 1106 200 20 180
1010 1107 200 100 100
1010 1108 200 140 60
7 rows selected.
Second question:
SQL> with
2 data as (
3 SELECT PO_TXN_CODE, PO_NO, PO_DATE, PO_ITEM_CODE, PO_UNIQUE_REFERENCE_CODE,
4 GRN_TXN_CODE, GRN_NO, GRN_DATE,PO_QUANTITY, GRN_QUANTITY
5 FROM local_purchase_order,good_receipt_note
6 WHERE PO_UNIQUE_REFERENCE_CODE = PO_UNQ_REF_CODE
7 ),
8 step1 as (
9 select po_no, po_quantity,
10 sum(GRN_QUANTITY) over (partition by po_no order by grn_no) granted,
11 po_quantity-sum(GRN_QUANTITY) over (partition by po_no order by grn_no) balance,
12 row_number() over (partition by po_no order by grn_no) rn,
13 count(*) over (partition by po_no) cnt
14 from data
15 )
16 select po_no, po_quantity, granted, balance
17 from step1
18 where rn = cnt
19 /
PO_NO PO_QUANTITY GRANTED BALANCE
---------- ----------- ---------- ----------
1001 550 250 300
1010 200 140 60
2 rows selected.
Third question:
SQL> with
2 data as (
3 SELECT PO_TXN_CODE, PO_NO, PO_DATE, PO_ITEM_CODE, PO_UNIQUE_REFERENCE_CODE,
4 GRN_TXN_CODE, GRN_NO, GRN_DATE,PO_QUANTITY, GRN_QUANTITY
5 FROM local_purchase_order,good_receipt_note
6 WHERE PO_UNIQUE_REFERENCE_CODE = PO_UNQ_REF_CODE
7 ),
8 step1 as (
9 select po_no, po_quantity,
10 sum(GRN_QUANTITY) over (partition by po_no order by grn_no) granted,
11 po_quantity-sum(GRN_QUANTITY) over (partition by po_no order by grn_no) balance,
12 row_number() over (partition by po_no order by grn_no) rn,
13 count(*) over (partition by po_no) cnt
14 from data
15 )
16 select po_no, po_quantity, granted, balance
17 from step1
18 where rn = cnt
19 and balance > 0
20 /
PO_NO PO_QUANTITY GRANTED BALANCE
---------- ----------- ---------- ----------
1001 550 250 300
1010 200 140 60
2 rows selected.
Remember:
1/ A statement ends with "/" or ";", your second create table misses them
2/ '01-JAN-2010' is not a DATE but a STRING:
SQL> select to_date('01-JAN-2010') from dual;
select to_date('01-JAN-2010') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
3/ Column in results should be align to be readable. Do you provide such report to your boss?
It's easy:
SQL> SELECT PO_TXN_CODE, PO_NO, PO_DATE, PO_ITEM_CODE, PO_UNIQUE_REFERENCE_CODE,
2 GRN_TXN_CODE, GRN_NO, GRN_DATE,PO_QUANTITY, GRN_QUANTITY
3 FROM local_purchase_order,good_receipt_note
4 WHERE PO_UNIQUE_REFERENCE_CODE = PO_UNQ_REF_CODE
5
SQL> /
PO_ PO_NO PO_DATE PO_ITEM_ PO_UNI GRN GRN_NO GRN_DATE PO_QUANTITY GRN_QUANTITY
--- ---------- ----------- -------- ------ --- ---------- ----------- ----------- ------------
LPO 1001 01-JAN-2010 C1000101 REF201 GRN 1102 05-JAN-2010 550 50
LPO 1001 01-JAN-2010 C1000101 REF201 GRN 1103 09-JAN-2010 550 80
LPO 1001 01-JAN-2010 C1000101 REF201 GRN 1104 22-JAN-2010 550 70
LPO 1001 01-JAN-2010 C1000101 REF201 GRN 1105 03-FEB-2010 550 50
LPO 1010 10-JAN-2010 C1000120 REF202 GRN 1106 15-JAN-2010 200 20
LPO 1010 10-JAN-2010 C1000120 REF202 GRN 1107 31-JAN-2010 200 80
LPO 1010 10-JAN-2010 C1000120 REF202 GRN 1108 18-MAR-2010 200 40
Regards
Michel
[Updated on: Sun, 31 January 2010 03:03] Report message to a moderator
|
|
|
|
|
Re: QUERY to get balances of running total [message #441402 is a reply to message #441398] |
Sun, 31 January 2010 08:35   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I think you should have a closer look at your own data.
I modified the first query then you will understand:
SQL> with
2 data as (
3 SELECT PO_TXN_CODE, PO_NO, PO_DATE, PO_ITEM_CODE, PO_UNIQUE_REFERENCE_CODE,
4 GRN_TXN_CODE, GRN_NO, GRN_DATE,PO_QUANTITY, GRN_QUANTITY
5 FROM local_purchase_order,good_receipt_note
6 WHERE PO_UNIQUE_REFERENCE_CODE = PO_UNQ_REF_CODE
7 )
8 select po_no, grn_no, po_quantity, GRN_QUANTITY,
9 sum(GRN_QUANTITY) over (partition by po_no order by grn_no) total_granted,
10 po_quantity-sum(GRN_QUANTITY) over (partition by po_no order by grn_no) balance
11 from data
12 order by po_no, grn_no
13 /
PO_NO GRN_NO PO_QUANTITY GRN_QUANTITY TOTAL_GRANTED BALANCE
---------- ---------- ----------- ------------ ------------- ----------
1001 1102 550 50 50 500
1001 1103 550 80 130 420
1001 1104 550 70 200 350
1001 1105 550 50 250 300
1010 1106 200 20 20 180
1010 1107 200 80 100 100
1010 1108 200 40 140 60
7 rows selected.
If it is wrong, EXPLAIN why.
Regards
Michel
[Updated on: Sun, 31 January 2010 09:08] Report message to a moderator
|
|
|
Re: QUERY to get balances of running total [message #441411 is a reply to message #441402] |
Sun, 31 January 2010 22:51   |
micro_oracle
Messages: 41 Registered: December 2007 Location: UAE
|
Member |
 
|
|
Michel,
in time of delivering such report, I will do my best to get my boss happy.
but I have an issue that with clause is not working properly in my oracle 10.2 server.
the following happend :
SQL*Plus: Release 8.0.6.0.0 - Production on Mon Feb 1 08:50:12 2010
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> with data as (
unknown command beginning "with data ..." - rest of line ignored.
SQL> SELECT PO_TXN_CODE, PO_NO, PO_DATE, PO_ITEM_CODE, PO_UNIQUE_REFERENCE_CODE,
2 GRN_TXN_CODE, GRN_NO, GRN_DATE,PO_QUANTITY, GRN_QUANTITY
3 FROM local_purchase_order,good_receipt_note
4 WHERE PO_UNIQUE_REFERENCE_CODE = PO_UNQ_REF_CODE
5 ),
6 step1 as (
7 select po_no, po_quantity,
8 sum(GRN_QUANTITY) over (partition by po_no order by grn_no) granted,
9 po_quantity-sum(GRN_QUANTITY) over (partition by po_no order by grn_no) balance,
10 row_number() over (partition by po_no order by grn_no) rn,
11 count(*) over (partition by po_no) cnt
12 from data
13 )
14 select po_no, po_quantity, granted, balance
15 from step1
16 where rn = cnt
17 ;
),
*
ERROR at line 5:
ORA-00933: SQL command not properly ended
Tell me what I can do here and also I'm getting the same result thru TOAD 8.6
thanks
|
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 13:44:47 CST 2025
|