GL Account Numbers -Cleanup of Accounts Not Used

(0) Share

Report

Posted on by Community Member

We are going through a mass cleanup of account numbers and looking for the best means to determine if an account has been dormant (ex. not used for xx number of days). Anyone have some best practice parameters to determine if an account is inactive (and location of parameters within tables.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

Mahmoud Saadi 32,738 on at

Like (1)

Report
Hello Billie

There are definitely various possible methods to get this settled, it first depends on your specific requirement. Are you looking for the accounts with no transaction at all ? Or the accounts which were used long time ago and has not been used at all in the current year ?

In all possible scenarios, you may consider the following script which will provide the following piece of information to be used in your analysis:

Whether the account has ever been used or not (Under column, ever used)

The last time the account was used

The duration in days, months or years ... since the account was lastly used

Here is the data set :

The script will provide a report for all the accounts in your COA, here are the columns interpretation

Account Number

Account Description

Never used: this flag provides a simple answer for whether this account has ever been used or not at all

Last used: the last transaction recorded on this account

Not used since (days, month or years), it provides a date difference between today and the last time this account was used.

Here is the SQL Script

SELECT B.ACTNUMST 'Account Number' , A.ACTDESCR 'Account Description' , CASE WHEN ISNULL(D.TRX_Date, 0) < A.CREATDDT THEN 'Yes' ELSE '' END AS NeverUsed , ISNULL(D.TRX_Date, 0) AS 'Last Used' , DATEDIFF(YY, ISNULL(D.TRX_Date, 0), GETDATE()) 'Not Used Since (In Years)' , DATEDIFF(MM, ISNULL(D.TRX_Date, 0), GETDATE()) 'Not Used Since (In Months)' , DATEDIFF(DD, ISNULL(D.TRX_Date, 0), GETDATE()) 'Not Used Since (In Days)' FROM dbo.GL00100 AS A LEFT OUTER JOIN dbo.GL00105 AS B ON A.ACTINDX = B.ACTINDX LEFT OUTER JOIN ( SELECT ACTINDX , MAX(TRX_Date) TRX_Date FROM ( SELECT ACTINDX , MAX(TRXDATE) AS TRX_Date FROM dbo.GL20000 GROUP BY ACTINDX UNION ALL SELECT ACTINDX , MAX(TRXDATE) AS TRX_Date FROM dbo.GL30000 GROUP BY ACTINDX ) AS C GROUP BY C.ACTINDX ) AS D ON B.ACTINDX = D.ACTINDX

Your feedback is highly appreciated,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks so much for this information!

Was this reply helpful? Yes No
Mahmoud Saadi 32,738 on at

Like (0)

Report

You are most welcome

Was this reply helpful? Yes No
Napolo 766 on at

Like (0)

Report

This is great Mahmoud!

Can you guide me on the WHERE clause from your Case statement?

I am trying to say WHERE Not Used Since(In Days) >= '730'

WHERE 'Not Used Since (In Days)' >= '730';

I am missing something. It pulls data in, but doesn't filter out the accounts used less than the total numb days specified.

Can you help me?

Was this reply helpful? Yes No
AB-17022328-0 37 on at

Like (0)

Report

Hello Mahmoud,

Thank you so much for this script!! I have a customer that has over 13,000 accounts and we found about 8000 that had never been used!!

I added additional columns to help with filtering and updating (Created Date, Account Index (to help with linking to other tables), Account Type, and Active (Yes/No))

We did find that some accounts had been used...but Netted to Zero on the GL20000 and GL30000 table. We filtered the Date Last Used and the Never Used columns in SmartList (I used this in SmartList Builder). Do you have any ideas on how to exclude Accounts that have history...even though Debits = Credits for a year/period/etc?

SELECT B.ACTNUMST 'Account Number' ,

A.ACTDESCR 'Account Description' ,

A.CREATDDT 'Created Date',

A.ACTINDX 'Account Index',

A.ACCTTYPE 'Account Type',

A.ACTIVE 'Active',

CASE WHEN ISNULL(D.TRX_Date, 0) < A.CREATDDT THEN 'Yes'

ELSE ''

END AS NeverUsed ,

ISNULL(D.TRX_Date, 0) AS 'Last Used' ,

DATEDIFF(YY, ISNULL(D.TRX_Date, 0), GETDATE()) 'Not Used Since (In Years)' ,

DATEDIFF(MM, ISNULL(D.TRX_Date, 0), GETDATE()) 'Not Used Since (In Months)' ,

DATEDIFF(DD, ISNULL(D.TRX_Date, 0), GETDATE()) 'Not Used Since (In Days)'

FROM dbo.GL00100 AS A

LEFT OUTER JOIN dbo.GL00105 AS B ON A.ACTINDX = B.ACTINDX

LEFT OUTER JOIN ( SELECT ACTINDX ,

MAX(TRX_Date) TRX_Date

FROM ( SELECT ACTINDX ,

MAX(TRXDATE) AS TRX_Date

FROM dbo.GL20000

GROUP BY ACTINDX

UNION ALL

SELECT ACTINDX ,

MAX(TRXDATE) AS TRX_Date

FROM dbo.GL30000

GROUP BY ACTINDX

) AS C

GROUP BY C.ACTINDX

) AS D ON B.ACTINDX = D.ACTINDX

Was this reply helpful? Yes No