Weekly Dynamic: SQL to Show Transactions for a Certain Account

Let's say that you are analyzing an account and want to see all the transactions that hit a particular account to see the related and offsetting accounts. Sure you can do that transaction by transaction via Detail Inquiry but it's tough to do analysis that way. Smartlists won't really give you what you want. Since you are looking for the full transaction detail whenever part of that transaction hits a certain account. You can't really limit the account and get what you want. You need a SQL statement that can be used in Smartlist Builder, Excel Builder, SSRS or someplace else.

Well here you go. This statement gets the transaction detail from the open year when that transaction contains a certain account number in segment 2. You'll of course need to modify it for your segments and accounts but it's a good starting framework.


SELECT *

FROM GL20000

Where GL20000.jrnentry in (Select distinct(jrnentry) FROM GL20000 INNER JOIN

GL00100 ON GL20000.ACTINDX = GL00100.ACTINDX

Where GL00100.ACTNUMBR_2='1100')