GL Trial Balance by Group Query

From KeystoneIntranet
Revision as of 00:01, 10 January 2017 by Intra>User (1 revision imported)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Standard: 

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

Alternate: By Group Sorted by Group Sequence 

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

Most Excellent Queries

Retrieved from "http://in.compucrete.com/index.php?title=GL_Trial_Balance_by_Group_Query&oldid=225"