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 Go to next message
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 Go to previous messageGo to next message
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 #441398 is a reply to message #441387] Sun, 31 January 2010 07:57 Go to previous messageGo to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

Michel,

First query is brining an incorrect running balance like
 PO_NO     GRN_NO     PO_QUANTITY    GRANTED BALANCE    crt. bal 
---------- ---------- ----------- ---------- ---------  ---------
      1001       1102         550         50       500        500
      1001       1103         550        130       420        370
      1001       1104         550        200       350        170
      1001       1105         550        250       300        -80
      1010       1106         200         20       180        180
      1010       1107         200        100       100         80
      1010       1108         200        140        60        -60


please note the correct running balance which it should be there in the report (crt. bal)

and for second query with clause is not working in my environment, please advise



thanks
Re: QUERY to get balances of running total [message #441400 is a reply to message #441398] Sun, 31 January 2010 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
please note the correct running balance which it should be there in the report (crt. bal)

How do you calculate your "crt. bal"?
What prevent you from modifying my query to fit your needs?

Quote:
and for second query with clause is not working in my environment

Is not working is not an Oracle error.
Same remarks.

Please explain.

Regards
Michel
Re: QUERY to get balances of running total [message #441402 is a reply to message #441398] Sun, 31 January 2010 08:35 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: QUERY to get balances of running total [message #441416 is a reply to message #441411] Sun, 31 January 2010 23:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Change your SQL*PLus to something that is greater, at least 9.2.
The problem is not your database, just your SQL*Plus.

Regards
Michel
icon7.gif  Re: QUERY to get balances of running total [message #441484 is a reply to message #441416] Mon, 01 February 2010 06:53 Go to previous message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

thanks deeply michel

it's working fine now yaw

Surprised
Previous Topic: Diffrence between object export using expdp and dbms_metadata
Next Topic: Negative values in LOB
Goto Forum:
  


Current Time: Tue Feb 11 13:44:47 CST 2025