Home » SQL & PL/SQL » SQL & PL/SQL » how to write Sql script for culmalative balance for 2 table
how to write Sql script for culmalative balance for 2 table [message #199694] Wed, 25 October 2006 21:14 Go to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

Dear sir,

Please refer to the below table.

table A
Partno     Date          Qty
123        12-Jul-06       10       
211        12-Aug-06       8      
134        13-Aug-06       -20 

Table B
Partno      Date           Qty
222        20-Aug-06       10
111        30-Aug-06       20
111        01-Sep-06       5




The result output that i need is as below.

Result output,

Partno        Date         Qty      bal_culmative
123        12-Jul-06       10       10
211        12-Aug-06       8        18
134        13-Aug-06       -20      -2
222        20-Aug-06       10        8
111        30-Aug-06       20        28
111        01-Sep-06       5         33



Below is my sql scripts & please help me how to write the scripts for the bal_culmative column. Thanks

select A.partno, A.date, A.qty
from table A
union
select b.partno, b.date, b.qty
from table b



regards
Mabel

[mod-edit]added code tags.

[Updated on: Thu, 26 October 2006 01:22] by Moderator

Report message to a moderator

Re: how to write Sql script for culmalative balance for 2 table [message #199712 is a reply to message #199694] Wed, 25 October 2006 23:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You can use your query as an inline view, then in an outer query use the SUM analytic function, like "SUM (qty) OVER (ORDER BY your_date_column)".
Re: how to write Sql script for culmalative balance for 2 table [message #199713 is a reply to message #199712] Wed, 25 October 2006 23:52 Go to previous messageGo to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

Can you please write the scripts out. Thanks

mabel
Re: how to write Sql script for culmalative balance for 2 table [message #199738 is a reply to message #199713] Thu, 26 October 2006 01:34 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
NGSOOKFONG wrote on Thu, 26 October 2006 06:52
Can you please write the scripts out.
Mabel,

Barbara Boehmer wrote
You can use your query as an inline view...

SELECT partno
     , thedate
     , qty
FROM ( SELECT partno
            , thedate
            , qty
       FROM   a
       UNION
       SELECT partno 
            , thedate
            , qty    
       FROM   b
     )



Barbara Boehmer wrote
...then in an outer query use the SUM analytic function, like "SUM (qty) OVER (ORDER BY your_date_column)".

Not much work left for you... Come on, give it a try!


MHE
Previous Topic: About Oracle 9i Forms & Reports CD
Next Topic: Query challenge - complex logic
Goto Forum:
  


Current Time: Wed Dec 07 03:08:31 CST 2016

Total time taken to generate the page: 0.12941 seconds