Friday, April 15, 2011

Clients with No Recent Activity

The following is a simple query to identify clients that have had no activity over the past 2 years. It looks at "Current" clients opened more than two years ago.
SELECT
clnum
,clname1
,clopendt
FROM
client
WHERE
clstatus = 'C'
AND clopendt < DATEADD(yy,-2,GETDATE())
--No new time
AND NOT EXISTS (SELECT * FROM matter INNER JOIN timecard ON mmatter=tmatter WHERE mclient=clnum AND tworkdt >= DATEADD(yy,-2,GETDATE()))
--No new costs
AND NOT EXISTS (SELECT * FROM matter INNER JOIN cost ON mmatter=cmatter WHERE mclient=clnum AND cdisbdt >= DATEADD(yy,-2,GETDATE()))
--No new trust
AND NOT EXISTS (SELECT * FROM matter INNER JOIN trsttran ON mmatter=trmatter WHERE mclient=clnum AND trdate >= DATEADD(yy,-2,GETDATE()))
--No new invoices or payments
AND NOT EXISTS (SELECT * FROM matter INNER JOIN ledger ON mmatter=lmatter WHERE mclient=clnum AND ltradat >= DATEADD(yy,-2,GETDATE()))
--No new matters
AND NOT EXISTS (SELECT * FROM matter WHERE mclient=clnum AND mopendt >= DATEADD(yy,-2,GETDATE()))
--No A/R or WIP
AND NOT EXISTS (SELECT * FROM inqsnap WHERE icode='MC' AND ivalue=clnum AND (ar_fees+ar_cost+ar_other+ar_unall+unb_hours+unb_fees+unb_cost)<>0)

No comments:

Post a Comment