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