Example-1

==============

with t as

(

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 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

)

select *

from t

model

dimension by (daynum,cast(trantype as /*VARCHAR2(15)*/chAR(15)) trantype)

measures (amount)

rules upsert all

( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0

, amount[any,'Closing balance'] order by daynum = sum(amount)

[daynum <= cv(daynum),trantype != 'Closing balance']

, amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']

)

order by daynum

, decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)

/

===========================

EXAMPLE-2

===========================

with tran as

(

select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all

select 1 as daynum, 'DR' as trantype, 1 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 1 as daynum, 'CR' as trantype, 200 as amount from dual union all

select 1 as daynum, 'DR' as trantype, 1 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, 'CR' as trantype, 900 as amount from dual union all

select 3 as daynum, 'DR' as trantype, -500 as amount from dual

), days as

( SELECT LEVEL daynum FROM DUAL CONNECT BY LEVEL <= 4)

SELECT daynum

, DECODE (trantype,'OB','Opening Balance','CB','Closing Balance',trantype) descr

, CASE

/* For opening balance/closing balance

amount will = sum of previous days transactions

*/

WHEN (trantype IN ('OB','CB'))

THEN NVL (SUM(amount) OVER (ORDER BY daynum,DECODE (trantype,'OB',1,'CB',3,2)),0)

ELSE amount

END amount

FROM

(

SELECT d.daynum

, CASE

WHEN ( GROUPING_ID (d.daynum,1)>0

AND GROUPING_ID (d.daynum,2)>0

)

THEN t.trantype

WHEN ( GROUPING_ID (d.daynum,1)>0)

THEN 'OB'

WHEN ( GROUPING_ID (d.daynum,2)>0)

THEN 'CB'

END trantype

, t.amount

FROM tran t

, days d

WHERE d.daynum = t.daynum(+)

/* ROWNUM required to preserve duplicate transactions */

GROUP BY GROUPING SETS ((d.daynum,1),(d.daynum,2),(d.daynum,t.trantype,t.amount,ROWNUM))

)

WHERE trantype IS NOT NULL /* Do not show NULL transaction

for days with no transaction

(outer join)

*/

ORDER BY daynum,DECODE (trantype,'OB',1,'CB',3,2)

/

===========================

EXAMPLE-3

===========================

with t as

2 (

3 select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all

4 select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all

5 select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all

6 select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all

7 select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all

8 select 3 as daynum, 'DR' as trantype, -500 as amount from dual

9 ),

10 data

11 as

12 ( select daynum, r

13 from (select level daynum from dual connect by level <= 4),

14 (select level r from dual connect by level <= 2 )

15 )

16 select daynum,

17 r,

18 ttype,

19 amt,

20 case when ttype in ( 'close', 'open' )

21 then sum(samt) over (order by daynum, r)

22 end theamount

23 from (

24 select data.daynum,

25 data.r,

26 case when (nvl(t.trantype,'CR') = 'CR' and data.r = 1 )

27 then 'open'

28 when (nvl(t.trantype,'DR') = 'DR' and data.r = 2)

29 then 'close'

30 else t.trantype

31 end ttype,

32 case when (t.trantype = 'CR' and r = 2) or (t.trantype = 'DR' and r=1)

33 then t.amount

34 end amt,

35 case when (t.trantype = 'DR' and r = 2)

36 then sum(case when r=1 then t.amount end) over (partition by

data.daynum)

37 else 0

38 end samt

39 from t, data

40 where data.daynum = t.daynum (+)

41 )

42 order by daynum, r, decode( ttype, 'open', 1, 'CR', 2, 'DR', 3, 'close', 4 )

43 /

===========================

EXAMPLE-4

===========================

SQL> with my_tab as (select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all

2 select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all

3 select 1 as daynum, 'CR' as trantype, 400 as amount from dual union all

4 select 1 as daynum, 'DR' as trantype, -500 as amount from dual union all

5 select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all

6 select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all

7 select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all

8 select 3 as daynum, 'DR' as trantype, -500 as amount from dual union all

9 select 4 as daynum, 'CR' as trantype, 900 as amount from dual union all

10 select 5 as daynum, 'CR' as trantype, 900 as amount from dual union all

11 select 5 as daynum, 'CR' as trantype, 200 as amount from dual union all

12 select 5 as daynum, 'DR' as trantype, -500 as amount from dual union all

13 select 6 as daynum, 'DR' as trantype, -200 as amount from dual),

14 dummy as (select 1 id, 'Opening Balance' descr from dual union all

15 select 2 id, 'CR' descr from dual union all

16 select 3 id, 'DR' descr from dual union all

17 select 4 id, 'Closing Balance' descr from dual)

18 select mt.daynum,

19 d.descr,

20 mt.trantype,

21 decode(d.id, 1, 0,

22 2, amount,

23 3, amount,

24 4, sum(decode(d.id, 1, 0, 4, 0, amount)) over (partition by mt.daynum))

amo

unt

25 from (select mt1.*, row_number() over (partition by mt1.daynum order by mt1.trantype) rn

26 from my_tab mt1) mt

27 left outer join dummy d partition by (id, descr) on (mt.trantype = d.descr)

28 where case when d.id = 1 and rn = 1 then 1

29 when d.id = 2 and mt.trantype = 'CR' then 1

30 when d.id = 3 and mt.trantype = 'DR' then 1

31 when d.id = 4 and rn = 1 then 1

32 else 0

33 end = 1

34 order by mt.daynum, d.id;

===========================

EXAMPLE-2

===========================

with t as (

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

)

select daynum,

trim(trantype),

amount

from t

model

dimension by (

daynum,

cast(trantype as char(15)) trantype

)

measures (

amount,

daynum + 1 dnum

)

rules upsert all (

dnum[1,'Opening balance'] = max(dnum)[any,any],

amount[for daynum from 1 to dnum[1,'Opening balance'] increment 1,'Opening balance'] = 0,

amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance'],

amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']

)

order by daynum,

decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)

/

Presentation Feedback and Future Direction

23 hours ago

## No comments:

Post a Comment