SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Intersperse daily totals with opening and closing balance

445476Apr 1 2010 — edited Apr 19 2010

Hi All

Suppose we have a data set (simplified from my query but will suffice)

(
  select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
  select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
  select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
  select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
  select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
  select 3 as daynum, 'DR' as trantype, -500 as amount from dual 
)

How would you go about getting a result set that looks like:

1, Opening Balance, 0
1, CR, 200
1, DR, -300
1, Closing Balance, -100
2, Opening Balance, -100
2, CR, 800
2, DR, -200
2, Closing Balance, 500
3, Opening Balance, 500
3, CR, 900
3, DR, -500
3, Closing Balance, 900
4, Opening Balance, 900
4, Closing Balance, 900

I'm thinking:
generate an arbitrary number of rows for the number of days I want to report over (in this case, 4)
cross join these with 2 row set of constants "opening" and "closing" balanace
-> I have now an opening and closing balance row for every day
union it with my data so that "opening/CLosing balance" is a type too (and nulls in the amount column)
case when the type is like '%Balance' then sum(amount) over(order by day, orderkind rows unbounded preceding) else amount

(orderkind is a constant 0 for opening, 1 for all the datarows, 2 for closing)

Is it right?
Is there a better way?

Edited by: charred on Apr 1, 2010 7:03 AM

439955

Oops.. Some formatting issue.. The result should be like this

col2 col3 col1
==== ==== ====
1 x a
2 y b
3 z c
4 u
5 v
6
7
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

RadhakrishnaSarma

Vasanth! You can paste the format between **[**pre] and [/pre] tags to preserve the spaces.

Use

break

SQL*Plus command.

Cheers
Sarma.

Keith Jamieson

Not quite finished but this should give you a huge start

with distinct_cols 
as
( select 
'a' col1 , 1 col2 , 'x' col3 from dual
union all select 
'a', 2, 'y' from dual
union all select 
'a', 3, 'z' from dual
union all select 
'a', 4, 'u' from dual
union all select 
'b', 5, 'v' from dual
union all select 
'b', 6, 'x' from dual
union all select 
'b', 7, 'x' from dual
union all select 
'b', 9, 'x' from dual
union all select 
'b', 10, 'y' from dual
union all select 
'b', 11, 'y' from dual
union all select 
'b', 12, 'y' from dual
union all select 
'b', 13, 'y' from dual
union all select 
'b', 14, 'x' from dual
union all select 
'b', 15, 'y' from dual
union all select 
'b', 16, 'z' from dual
union all select 
'b', 17, 'u' from dual
union all select 
'b', 18, 'v' from dual
union all select 
'b', 19, 'x' from dual
union all select 
'b', 20, 'x' from dual
union all select
'c', 21, 'x' from dual
union all select 
'c', 22, 'y' from dual
union all select 
'c',23, 'y' from dual
union all select 
'c', 24, 'y' from dual
union all select 
'c', 25, 'y' from dual
union all select 
'c', 26, 'x' from dual
union all select 
'c', 27, 'y' from dual
union all select 
'c', 28, 'z' from dual
)
select decode(marker,'1',col1,NULL) col1_distinct,
       decode(marker,'2',col1,NULL) col2_distinct,
       decode(marker,'3',col1,NULL) col3_distinct
from
(
select distinct '1' marker,col1  
from distinct_cols 
union all
select distinct '2',to_char(col2)
from distinct_cols 
union all
select distinct '3',col3 
from distinct_cols 
)

Alessandro Rossi

This is a solution but result is not deterministic, and this is also right because the properties of the row in the output are not completely defined.

Processing ...
with distinct_cols 
as
( select 
'a' col1 , 1 col2 , 'x' col3 from dual
union all select 
'a', 2, 'y' from dual
union all select 
'a', 3, 'z' from dual
union all select 
'a', 4, 'u' from dual
union all select 
'b', 5, 'v' from dual
union all select 
'b', 6, 'x' from dual
union all select 
'b', 7, 'x' from dual
union all select 
'b', 9, 'x' from dual
union all select 
'b', 10, 'y' from dual
union all select 
'b', 11, 'y' from dual
union all select 
'b', 12, 'y' from dual
union all select 
'b', 13, 'y' from dual
union all select 
'b', 14, 'x' from dual
union all select 
'b', 15, 'y' from dual
union all select 
'b', 16, 'z' from dual
union all select 
'b', 17, 'u' from dual
union all select 
'b', 18, 'v' from dual
union all select 
'b', 19, 'x' from dual
union all select 
'b', 20, 'x' from dual
union all select
'c', 21, 'x' from dual
union all select 
'c', 22, 'y' from dual
union all select 
'c',23, 'y' from dual
union all select 
'c', 24, 'y' from dual
union all select 
'c', 25, 'y' from dual
union all select 
'c', 26, 'x' from dual
union all select 
'c', 27, 'y' from dual
union all select 
'c', 28, 'z' from dual
)
select col1,col2,col3
from (
		select rownum rn
		from distinct_cols
	) natural left outer join (
		select rownum rn,col1
		from (
				select distinct col1
				from distinct_cols
			)
	) natural left outer join (
		select rownum rn,col2
		from (
				select distinct col2
				from distinct_cols
			)
	) natural left outer join (
		select rownum rn,col3
		from (
				select distinct col3
				from distinct_cols
			)
	)
Query finished, retrieving results...
COL1 COL2  COL3 
---- ----- -----
a        6 z  
b        7 x  
c       12 y  
        17 u  
        25 v  
        10    
        11    
        21    
        16    
         2    
         1    
        28    
        13    
        20    
        26    
         4    
        15    
         3    
        18    
        23    
        27    
        24    
         9    
         5    
        14    
        22    
        19    

27 row(s) retrieved

Bye Alessandro

439955

Thanks to every one... Sarma,Keith Jamieson and Alessandro .

I hopeI can retrive the result now. Thanks again

Vasanth

121256

No joins, just analytics:

exec dbms_random.seed(0)
with t as ( select chr(ascii('a') + dbms_random.value(0, 3)) as c1,
                   trunc(dbms_random.value(1, 20))           as c2,
                   chr(ascii('u') + dbms_random.value(0, 6)) as c3
              from dual connect by level <= 20
          ),
-------------------------------------------------------------------
   tt1 as ( select lag(          null,  1, c1) over (partition by c1 order by null) as c1,
                   lag(to_number(null), 1, c2) over (partition by c2 order by null) as c2,
                   lag(          null,  1, c3) over (partition by c3 order by null) as c3
              from t
          ),
   tt2 as ( select tt1.*,
                   row_number() over (order by c1) as rn1,
                   row_number() over (order by c2) as rn2,
                   row_number() over (order by c3) as rn3
              from tt1
          ),
   tt3 as ( select case when rn1 > rn2
                        then last_value(c1) over (order by rn1 range between abs(rn1 - rn2) preceding
                                                                         and abs(rn1 - rn2) preceding)
                        else last_value(c1) over (order by rn1 range between abs(rn2 - rn1) following
                                                                         and abs(rn2 - rn1) following)
                   end as c1,
                   c2,
                   case when rn3 > rn2
                        then last_value(c3) over (order by rn3 range between abs(rn3 - rn2) preceding
                                                                         and abs(rn3 - rn2) preceding)
                        else last_value(c3) over (order by rn3 range between abs(rn2 - rn3) following
                                                                         and abs(rn2 - rn3) following)
                   end as c3
              from tt2
          )
select c1, c2, c3 from tt3
  where c1 || c2 || c3 is not null
  order by c1, c2, c3
;

C1            C2 C3
-- ------------- --
a              1 u
b              3 v
c              4 w
               5 x
               7 y
               8 z
               9
              11
              13
              15
              16
              17
              19

13 rows selected.

572471

No joins, just analytics:

nice, but the only drawback - you need to know the column, comprising the biggest amount of distinct values beforehand.

121256

but the only drawback - you need to know the column, comprising the biggest amount of distinct values beforehand.

No. There is no any difference in which column will be chosen as "based".
For demo purposes I've added extra row number column to show full generality and symmetry of the approach:

exec dbms_random.seed(0)
with t as ( select chr(ascii('a') + dbms_random.value(0, 3)) as c1,
                   trunc(dbms_random.value(1, 20))           as c2,
                   chr(ascii('u') + dbms_random.value(0, 6)) as c3
              from dual connect by level <= 20
          ),
-------------------------------------------------------------------
   tt1 as ( select lag(          null,  1, c1) over (partition by c1 order by null) as c1,
                   lag(to_number(null), 1, c2) over (partition by c2 order by null) as c2,
                   lag(          null,  1, c3) over (partition by c3 order by null) as c3
              from t
          ),
   tt2 as ( select tt1.*,
                   row_number() over (order by c1) as rn1,
                   row_number() over (order by c2) as rn2,
                   row_number() over (order by c3) as rn3,
                   rownum rn
              from tt1
          ),
   tt3 as ( select case when rn1 > rn
                        then last_value(c1) over (order by rn1 range between abs(rn1 - rn) preceding
                                                                         and abs(rn1 - rn) preceding)
                        else last_value(c1) over (order by rn1 range between abs(rn - rn1) following
                                                                         and abs(rn - rn1) following)
                   end as c1,
                   case when rn2 > rn
                        then last_value(c2) over (order by rn2 range between abs(rn2 - rn) preceding
                                                                         and abs(rn2 - rn) preceding)
                        else last_value(c2) over (order by rn2 range between abs(rn - rn2) following
                                                                         and abs(rn - rn2) following)
                   end as c2,
                   case when rn3 > rn
                        then last_value(c3) over (order by rn3 range between abs(rn3 - rn) preceding
                                                                         and abs(rn3 - rn) preceding)
                        else last_value(c3) over (order by rn3 range between abs(rn - rn3) following
                                                                         and abs(rn - rn3) following)
                   end as c3
              from tt2
          )
select c1, c2, c3 from tt3
  where c1 || c2 || c3 is not null
  order by c1, c2, c3
;

C1            C2 C3
-- ------------- --
a              1 u
b              3 v
c              4 w
               5 x
               7 y
               8 z
               9
              11
              13
              15
              16
              17
              19

13 rows selected.

P.S. Thanks to [url http://www.sql.ru/forum/actualthread.aspx?bid=3&tid=482506&hl=over+range#4785373]Vladimir Sitnikov for the demo of the approach.

Aketi Jyuuzou

create table ColTable(Col1,Col2,Col3) as
select 'a', 1,'x' from dual union all
select 'a', 2,'y' from dual union all
select 'a', 3,'z' from dual union all
select 'a', 4,'u' from dual union all
select 'b', 5,'v' from dual union all
select 'b', 6,'x' from dual union all
select 'b', 7,'x' from dual union all
select 'b', 9,'x' from dual union all
select 'b',10,'y' from dual union all
select 'b',11,'y' from dual union all
select 'b',12,'y' from dual union all
select 'b',13,'y' from dual union all
select 'b',14,'x' from dual union all
select 'b',15,'y' from dual union all
select 'b',16,'z' from dual union all
select 'b',17,'u' from dual union all
select 'b',18,'v' from dual union all
select 'b',19,'x' from dual union all
select 'b',20,'x' from dual union all
select 'c',21,'x' from dual union all
select 'c',22,'y' from dual union all
select 'c',23,'y' from dual union all
select 'c',24,'y' from dual union all
select 'c',25,'y' from dual union all
select 'c',26,'x' from dual union all
select 'c',27,'y' from dual union all
select 'c',28,'z' from dual;

col COl1    for a8
col COl3    for a8
col NewCol1 for a8
col NewCol3 for a8

select Col1,Col2,Col3,
case when Rn <= count(distinct Col1) over()
     then max(Col1) over(order by Col1SumFlag
                         range between Rn-Col1SumFlag following
                                   and Rn-Col1SumFlag following) end as NewCol1,
case when Rn <= count(distinct Col3) over()
     then max(Col3) over(order by Col3SumFlag
                        range between Rn-Col3SumFlag following
                                  and Rn-Col3SumFlag following) end as NewCol3
from (select Col1,Col2,Col3,Rn,
      case Col1FirstFlag when 1 then sum(Col1FirstFlag) over(order by Col2)
                         else 0 end as Col1SumFlag,
      case Col3FirstFlag when 1 then sum(Col3FirstFlag) over(order by Col2)
                         else 0 end as Col3SumFlag
      from (select Col1,Col2,Col3,
            Row_Number() over(order by Col2) as Rn,
            case Row_Number() over(partition by Col1 order by Col2)
                 when 1 then 1 else 0 end as Col1FirstFlag,
            case Row_Number() over(partition by Col3 order by Col2)
                 when 1 then 1 else 0 end as Col3FirstFlag
              from ColTable))
order by Col2;

COL1  COL2  COL3  NEWCOL1   NEWCOL3
----  ----  ----  --------  --------
a        1  x     a         x
a        2  y     b         y
a        3  z     c         z
a        4  u     null      u
b        5  v     null      v
b        6  x     null      null
b        7  x     null      null
b        9  x     null      null
b       10  y     null      null
b       11  y     null      null
b       12  y     null      null
b       13  y     null      null
b       14  x     null      null
b       15  y     null      null
b       16  z     null      null
b       17  u     null      null
b       18  v     null      null
b       19  x     null      null
b       20  x     null      null
c       21  x     null      null
c       22  y     null      null
c       23  y     null      null
c       24  y     null      null
c       25  y     null      null
c       26  x     null      null
c       27  y     null      null
c       28  z     null      null

I used "Baaiwake" two times on case expression.
http://aozoragakuen.sakura.ne.jp/houhou5/houhou52/node6.html

Aketi Jyuuzou

easy version

create table Tumeru as
select 'A' as Val,10 as SortKey from dual union
select 'A', 20 from dual union
select 'B', 30 from dual union
select 'B', 40 from dual union
select 'B', 50 from dual union
select 'C', 60 from dual union
select 'C', 70 from dual union
select 'D', 80 from dual union
select 'D', 90 from dual union
select 'E',100 from dual union
select 'E',110 from dual union
select 'E',120 from dual union
select 'E',130 from dual union
select 'E',140 from dual union
select 'A',150 from dual union
select 'B',160 from dual union
select 'C',170 from dual union
select 'F',180 from dual union
select 'G',190 from dual;

col Val    for a8
col NewVal for a8

select Val,SortKey,
case when Rn <= count(distinct Val) over()
     then max(Val) over(order by SumFlag
                        range between Rn-SumFlag following
                                  and Rn-SumFlag following) end as NewVal
from(select Val,SortKey,Rn,
     case FirstFlag when 1 then sum(FirstFlag) over(order by SortKey)
                           else 0 end as SumFlag
     from (select Val,SortKey,
           Row_Number() over(order by SortKey) as Rn,
           case Row_Number() over(partition by Val order by SortKey)
                when 1 then 1 else 0 end as FirstFlag
             from Tumeru))
order by SortKey;

VAL  SORTKEY  NEWVAL
---  -------  -------
A         10  A
A         20  B
B         30  C
B         40  D
B         50  E
C         60  F
C         70  G
D         80  null
D         90  null
E        100  null
E        110  null
E        120  null
E        130  null
E        140  null
A        150  null
B        160  null
C        170  null
F        180  null
G        190  null

572471

but the only drawback - you need to know the

column, comprising the biggest amount of distinct
values beforehand.

No. There is no any difference in which column will
be chosen as "based".
For demo purposes I've added extra row number column
to show full generality and symmetry of the
approach:

agreed, I was wrong

1 - 11

Locked Post

New comments cannot be posted to this locked post.

Locked on May 17 2010

Added on Apr 1 2010

25 comments

4,105 views

SQL & PL/SQL

Intersperse daily totals with opening and closing balance

Comments

Post Details