List of Active Accounts with no activity for a Fiscal Year

Your rating: None Average: 3.5 (2 votes)

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!