GL All Transactions Related to an Account

GL All Transactions Related to an Account

01/15/2020 This query returns all transactions from all sessions that have activity for the selected account and date range when the activity is created in G/L or C/M.

select distinct(x.session_no) sno,xx.trans_no,xx.gl_account, a.description, xx.trx_date, xx.source_code, xx.source_app, xx.trx_amount, session_no from glttrx x
inner join glttrx xx on xx.session_no=x.session_no
inner join gltacct a on a.gl_account=xx.gl_account
 where  x.source_app in('C','G') and x.trx_date between:Begin_date and:End_date
and x.gl_account = :"Acct"
order by session_no

The main query selects all sessions that contain gl activity for the selected account and date range with glttrx aliased to X. Then this is inner joined to XX which selects all of the activity from each of those sessions. It's important to specify xx.<fieldname> in the select query to get the individual transaction information. For the same reason GLTACCT is joined to XX. NOTE: make sure to keep the distinct(x.session_no) and xx.trans_no as the first 2 fields. This makes sure that each transaction is included AND only included once. If you remove the distinct then you'll get duplicate transactions. If you remove trans_no then similar transactions will be merged.