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)
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.
Labels:
activity,
client,
enterprise,
sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment