with
tx
as
(
select
null
as
transaction_date,
null
as
date_time_encoded, 1
as
balance_effect,
'Opening Balance'
as
category,
''
as
memo, opening_balance
as
amount
from
account_with_type_opening_balance
where
(account_id,account_type_id) = (
'{3F52FED8-90D1-4249-A04E-42AB49D118F0}'
,
'{960B8DAE-1C4C-432C-96E6-33974B6B2B19}'
)
union
all
select
ws.date_withdrawn_spent, ws.date_time_encoded, -1, ac.account_category_full_description, ws.memo, ws.amount
from
withdrawn_spent ws
join
account_category ac
on
ac.account_category_id = ws.account_category_id
where
(drawn_from_account_id,drawn_from_account_type_id) = (
'{3F52FED8-90D1-4249-A04E-42AB49D118F0}'
,
'{960B8DAE-1C4C-432C-96E6-33974B6B2B19}'
)
union
all
select
ws.date_withdrawn_spent, ws.date_time_encoded, 1,
ac.account_category_full_description
||
' FX SOURCE-->'
|| a.account ||
':'
|| c.currency ||
'('
|| atx.account_type ||
') '
|| to_char(ws.amount,
'FM9,999,999.90'
) ||
' '
|| c.currency_symbol
||
' #'
|| to_char(ws.exchange_rate,
'FM9,999,999.90'
),
ws.memo, ws.transferred_to_amount
from
withdrawn_spent ws
join
account_category ac
on
ac.account_category_id = ws.account_category_id
join
account a
on
a.account_id = ws.drawn_from_account_id
join
currency c
on
c.currency_id = a.currency_id
join
account_type atx
on
atx.account_type_id = ws.drawn_from_account_type_id
where
(transferred_to_account_id,transferred_to_account_type_id) = (
'{3F52FED8-90D1-4249-A04E-42AB49D118F0}'
,
'{960B8DAE-1C4C-432C-96E6-33974B6B2B19}'
)
union
all
select
dr.date_deposited_received, dr.date_time_encoded, 1, ac.account_category_full_description, dr.memo, dr.amount
from
deposited_received dr
join
account_category ac
on
ac.account_category_id = dr.account_category_id
where
(received_in_account_id,received_in_account_type_id) = (
'{3F52FED8-90D1-4249-A04E-42AB49D118F0}'
,
'{960B8DAE-1C4C-432C-96E6-33974B6B2B19}'
)
union
all
select
fx.date_transferred, fx.date_time_encoded, 1,
'FX SOURCE-->'
|| a.account ||
':'
|| c.currency ||
'('
|| atx.account_type ||
') '
|| to_char(fx.from_amount,
'FM9,999,999.90'
) ||
' '
|| c.currency_symbol
||
' #'
|| to_char(fx.exchange_rate,
'FM9,999,999.90'
),
fx.memo, fx.to_amount
from
fund_transfer fx
join
account a
on
a.account_id = fx.from_account_id
join
currency c
on
c.currency_id = a.currency_id
join
account_type atx
on
atx.account_type_id = fx.from_account_type_id
where
(fx.to_account_id,fx.to_account_type_id) = (
'{3F52FED8-90D1-4249-A04E-42AB49D118F0}'
,
'{960B8DAE-1C4C-432C-96E6-33974B6B2B19}'
)
union
all
select
fx.date_transferred, fx.date_time_encoded, -1,
'FX DEST-->'
|| a.account ||
':'
|| c.currency ||
'('
|| atx.account_type ||
') '
|| to_char(fx.to_amount,
'FM9,999,999.90'
) ||
' '
|| c.currency_symbol
||
'#'
|| fx.exchange_rate,
fx.memo, fx.from_amount
from
fund_transfer fx
join
account a
on
a.account_id = fx.to_account_id
join
currency c
on
c.currency_id = a.currency_id
join
account_type atx
on
atx.account_type_id = fx.to_account_type_id
where
(fx.from_account_id,fx.from_account_type_id) = (
'{3F52FED8-90D1-4249-A04E-42AB49D118F0}'
,
'{960B8DAE-1C4C-432C-96E6-33974B6B2B19}'
)
),
sequenced
as
(
select
transaction_date, date_time_encoded, category, memo,
amount,
balance_effect,
row_number() over(
order
by
transaction_date nulls
first
, date_time_encoded)
as
seq
from
tx
)
select
transaction_date, date_time_encoded, category, memo,
case
when
balance_effect = -1
then
'-'
else
'+'
end
as
effect,
amount,
sum
(amount * balance_effect) over(
order
by
seq)
as
balance
from
sequenced
limit 30 offset 0