Home » SQL & PL/SQL » SQL & PL/SQL » FIND INVOICE NOS AGAINST BALANCE VALUE (Oracle 10G (10.2.0.1.0 ), Windows 2003)
FIND INVOICE NOS AGAINST BALANCE VALUE [message #577981] Fri, 22 February 2013 05:59 Go to next message
mmohsinaziz
Messages: 79
Registered: May 2012
Member
Dear Experts,
I have a table in which we record sale and payment received. I want to know the invoice nos. against the balance value.
TYPE 'INV' for Sales and 'REC' from Receipt of payment

sample data is as below.


drop table trans;

CREATE TABLE SCOTT.trans
(
  ttype  VARCHAR2(3),
  tdate  DATE,
  tno    NUMBER(4),
  acode  NUMBER(3),
  dr     NUMBER(5),
  cr     NUMBER(5)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;

INSERT INTO trans
     VALUES ('INV', TO_DATE ('01-FEB-13', 'DD-MON-RR'), 1, 101, 300, 0);

INSERT INTO trans
     VALUES ('INV', TO_DATE ('03-FEB-13', 'DD-MON-RR'), 5, 101, 450, 0);

INSERT INTO trans
     VALUES ('INV', TO_DATE ('05-FEB-13', 'DD-MON-RR'), 3, 101, 225, 0);

INSERT INTO trans
     VALUES ('REC', TO_DATE ('06-FEB-13', 'DD-MON-RR'), 1, 101, 0, 250);

INSERT INTO trans
     VALUES ('INV', TO_DATE ('08-FEB-13', 'DD-MON-RR'), 3, 101, 350, 0);

INSERT INTO trans
     VALUES ('REC', TO_DATE ('08-FEB-13', 'DD-MON-RR'), 2, 101, 0, 500);

INSERT INTO trans
     VALUES ('INV', TO_DATE ('11-FEB-13', 'DD-MON-RR'), 4, 101, 150, 0)

commit;




SQL>select * from trans;

TTY TDATE            TNO      ACODE         DR         CR
--- --------- ---------- ---------- ---------- ----------
INV 01-FEB-13          1        101        300          0
INV 03-FEB-13          5        101        450          0
INV 05-FEB-13          3        101        225          0
REC 06-FEB-13          1        101          0        250
INV 08-FEB-13          3        101        350          0
REC 08-FEB-13          2        101          0        450
INV 11-FEB-13          4        101        150          0

7 rows selected.

SQL>SELECT ACODE,TDATE,SUM(BAL) OVER (ORDER BY TDATE,ACODE)BAL
    FROM
    (
    SELECT ACODE,TDATE,SUM(DR)-SUM(CR) BAL
    FROM TRANS
    GROUP BY ACODE,TDATE ) T
    ORDER BY TDATE , ACODE;

     ACODE TDATE            BAL
---------- --------- ----------
       101 01-FEB-13        300
       101 03-FEB-13        750
       101 05-FEB-13        975
       101 06-FEB-13        725
       101 08-FEB-13        625
       101 11-FEB-13        775

6 rows selected.


Pending invoices and amount will be calculated from most recent invoice no. to backward.
In output invoice no 5 dated 03-feb-13 is changed to 50 as we required only 50 to meet the balance 775. we go backward in order and can not skip any invoice no.

Required Output is

TTY TDATE            TNO      ACODE         DR  
--- --------- ---------- ---------- ---------- 
INV 03-FEB-13          5        101         50  
INV 05-FEB-13          3        101        225  
INV 08-FEB-13          3        101        350  
INV 11-FEB-13          4        101        150


Thanks in advance
M. Mohsin

Report message to a moderator

Re: FIND INVOICE NOS AGAINST BALANCE VALUE [message #577987 is a reply to message #577981] Fri, 22 February 2013 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In output invoice no 5 dated 03-feb-13 is changed to 50 as we required only 50 to meet the balance 775.


From what? What is the operation that returns 50? What do the operands come from?

Regards
Michel

Report message to a moderator

Re: FIND INVOICE NOS AGAINST BALANCE VALUE [message #578018 is a reply to message #577987] Fri, 22 February 2013 15:46 Go to previous messageGo to next message
Bill B
Messages: 985
Registered: December 2004
Senior Member
 
SELECT ACODE,MAX(TDATE) TDATE,SUM(DR)-SUM(CR) BAL
    FROM TRANS
    GROUP BY ACODE;

Report message to a moderator

Re: FIND INVOICE NOS AGAINST BALANCE VALUE [message #578025 is a reply to message #578018] Fri, 22 February 2013 23:12 Go to previous messageGo to next message
mmohsinaziz
Messages: 79
Registered: May 2012
Member
Dear Michel,
Our target is to get invoice wise break up of balance value 775. We will start from the latest invoice no. to backward.
Our latest invoice no.is 4 dated 11-feb-13 value 150 target is 775.
775-150= 625
Now we move to previous invoice no. 3 dated 8-feb-13 value is 350 target is 625
625 - 350 = 275
Now we move to further previous inoice no. 3 dated 5-feb-13 value is 225 target is 275
275-225 = 50
Now we move to further previous inoice no. 5 dated 3-feb-13 value is 450 target is 50
Here the target value is less than invoice value and we can say that partial value 50 is balance against invoice no. 5 dated 03-feb-13

I have tried to clear every things, please let me know if further clarification is required.

Regards
M. Mohsin

Report message to a moderator

Re: FIND INVOICE NOS AGAINST BALANCE VALUE [message #578026 is a reply to message #578025] Fri, 22 February 2013 23:56 Go to previous messageGo to next message
Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where does 775 come from?
SQL> select * from trans order by tdate, acode;
TTY TDATE              TNO      ACODE         DR         CR
--- ----------- ---------- ---------- ---------- ----------
INV 01-FEB-2013          1        101        300          0
INV 03-FEB-2013          5        101        450          0
INV 05-FEB-2013          3        101        225          0
REC 06-FEB-2013          1        101          0        250
INV 08-FEB-2013          3        101        350          0
REC 08-FEB-2013          2        101          0        500
INV 11-FEB-2013          4        101        150          0

For me 300+450+225-250+350-500+150 = 725

Regards
Michel

[Updated on: Sat, 23 February 2013 00:13]

Report message to a moderator

Re: FIND INVOICE NOS AGAINST BALANCE VALUE [message #578027 is a reply to message #578026] Sat, 23 February 2013 00:11 Go to previous messageGo to next message
Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I understand your test case does not match your result.
Fixing the test case ("update trans set cr=450 where tdate=date '2013-02-08' and ttype='REC';"):
SQL> with
  2    data as (
  3      select ttype, tdate, tno, acode, dr,
  4             sum(dr-cr) over (partition by acode) 
  5             - nvl(sum(dr) over (
  6                     partition by acode
  7                     order by tdate desc
  8                     rows between unbounded preceding and 1 preceding), 0)
  9               rest_dr
 10      from trans
 11    )
 12  select ttype, tdate, tno, acode, least(dr, rest_dr) dr
 13  from data
 14  where ttype = 'INV' and rest_dr > 0
 15  order by acode, tdate
 16  /
TTY TDATE              TNO      ACODE         DR
--- ----------- ---------- ---------- ----------
INV 03-FEB-2013          5        101         50
INV 05-FEB-2013          3        101        225
INV 08-FEB-2013          3        101        350
INV 11-FEB-2013          4        101        150

Regards
Michel

[Updated on: Sat, 23 February 2013 00:16]

Report message to a moderator

Re: FIND INVOICE NOS AGAINST BALANCE VALUE [message #578029 is a reply to message #578027] Sat, 23 February 2013 00:24 Go to previous messageGo to next message
mmohsinaziz
Messages: 79
Registered: May 2012
Member
Thanks Michel,
You are realy great.

Sorry, for typeing mistake in insert statement.

Regards
M. Mohsin

Report message to a moderator

Re: FIND INVOICE NOS AGAINST BALANCE VALUE [message #578031 is a reply to message #578029] Sat, 23 February 2013 00:57 Go to previous messageGo to next message
mmohsinaziz
Messages: 79
Registered: May 2012
Member
Dear Michel,
Could you please explain the following line of your code or refer me a link to study this.

"rows between unbounded preceding and 1 preceding"

Thanks
Muhammad Mohsin

Report message to a moderator

Re: FIND INVOICE NOS AGAINST BALANCE VALUE [message #578035 is a reply to message #578031] Sat, 23 February 2013 01:38 Go to previous message
Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It means that the SUM applies to all rows of the partition that appear before (and not including) the current row within the given order.

SQL Reference, Analytic Functions.
Data Warehousing Guide, chapter 21 SQL for Analysis and Reporting (read the overview and the first section about the ranking functions, it is the same for other ones).

Regards
Michel

[Updated on: Sat, 23 February 2013 01:43]

Report message to a moderator

Previous Topic: SQL_QUERY
Next Topic: How is naming Integrity Constants helpful?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun May 19 11:19:22 CDT 2013

Total time taken to generate the page: 0.17503 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.