You’re offline. This is a read only version of the page.
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
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
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?
You are most welcome
Thanks so much for this information!
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:
Here is the data set :
The script will provide a report for all the accounts in your COA, here are the columns interpretation
Here is the SQL Script
Share
Report
All responses (
Answers (
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,
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156