View source for GL Trial Balance by Group Query

[[Category: Balancing]]
[[Category: General Ledger]]
[[Category: SQL]]
Standard:
<PRE>
select gl_group,GL_Account Account,Description Name,
(select sum(gta.trx_amount) from glttrx gta where ga.gl_account=gta.gl_account 
and gta.trx_date between :PD_Beg_Date and :PD_End_Date and gta.trANS_type<>'B') Cur_Pd_Amt,
(select sum(gta.trx_amount) from glttrx gta where ga.gl_account=gta.gl_account 
and gta.trx_date <= :PD_End_Date and gta.trANS_type<>'B') End_Amt
from gltacct ga 
where ga.account_type='B' 
group by gl_group,gl_account,Description
union 
select gl_group, GL_Account Account,Description Name,
(select sum(gta.trx_amount) from glttrx gta where ga.gl_account=gta.gl_account 
and gta.trx_date between :PD_Beg_Date and :PD_End_Date and gta.trANS_type<>'B') Pd_Amt,
(select sum(gta.trx_amount) from glttrx gta where ga.gl_account=gta.gl_account 
and gta.trx_date between :yr_Beg_Date and :PD_End_Date and gta.trANS_type<>'B') End_Amt
from gltacct ga 
where ga.account_type='I'
group by gl_group,gl_account,Description
</PRE>

Alternate: By Group Sorted by Group Sequence
<PRE>
select gr.sequence,gl_group,GL_Account Account,Description Name,
(select sum(gta.trx_amount) from glttrx gta where ga.gl_account=gta.gl_account 
and gta.trx_date between :PD_Beg_Date and :PD_End_Date and gta.trANS_type<>'B') Cur_Pd_Amt,
(select sum(gta.trx_amount) from glttrx gta where ga.gl_account=gta.gl_account 
and gta.trx_date <= :PD_End_Date and gta.trANS_type<>'B') End_Amt
from gltacct ga 
inner join gltacgrp gr on ga.gl_group=gr.group_id
where ga.account_type='B' 
group by gr.sequence,gl_group,gl_account,Description
union 
select gr.sequence,gl_group, GL_Account Account,Description Name,
(select sum(gta.trx_amount) from glttrx gta where ga.gl_account=gta.gl_account 
and gta.trx_date between :PD_Beg_Date and :PD_End_Date and gta.trANS_type<>'B') Pd_Amt,
(select sum(gta.trx_amount) from glttrx gta where ga.gl_account=gta.gl_account 
and gta.trx_date between :yr_Beg_Date and :PD_End_Date and gta.trANS_type<>'B') End_Amt
from gltacct ga 
inner join gltacgrp gr on ga.gl_group=gr.group_id
where ga.account_type='I'
group by gr.sequence,gl_group,gl_account,Description
</PRE>

[[Most Excellent Queries]]