Day wise total SQL Query with opening and colsing balance

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