List of Active Accounts with no activity for a Fiscal Year

Your rating: None Average: 5 (1 vote)

SQL to find active accounts that have no activity in a specific fiscal year. Notice the accounting_period <> 0 in the where clause. This is needed as accounts that had activities in 2007, for example, will have a row on the ledger table under year 2008 and accounting period of 0.

SELECT   a.ACCOUNT, a.descr
    FROM ps_gl_account_tbl a
   WHERE a.eff_status = 'A'
     AND NOT EXISTS (SELECT 'x'
                       FROM ps_ledger b
                      WHERE a.ACCOUNT = b.ACCOUNT
                      AND b.fiscal_year = :year
                      AND accounting_period <> 0)
GROUP BY a.ACCOUNT, a.descr;

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
13 + 7 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.