Vendors with No Activity

It is often a good idea to sift through records to determine active records that have had no activity to see if they need to be made inactive. Below is a query to identify active vendor records that have had no activity.

SELECT
ap.apnum,
ap.apname,
ap.*
FROM ap
WHERE ap.lckdate IS NULL
AND NOT EXISTS (SELECT * FROM apamt WHERE ap.apnum=apamt.apnum)
AND NOT EXISTS (SELECT * FROM apvo WHERE ap.apnum=apvo.apnum)
AND NOT EXISTS (SELECT * FROM trcheck WHERE ap.apnum=trcheck.trvendor)
AND NOT EXISTS (SELECT * FROM csd WHERE ap.apnum=csd.apnum)
AND NOT EXISTS (SELECT * FROM rapvo WHERE ap.apnum=rapvo.apnum)

While the ap.lckdate (last check date) is an obvious criteria here, vendors can have activity other than checks that needs to be searched for. Therefore, we include the voucher table, repeating voucher table, vendor summary totals, trust check table and the check detail table. If the vendor doesn't appear in any of these, then it's definitely an inactive vendor.