Saturday, April 16, 2011

Matters with No Recent Activity

This is similar to yesterday's Client post, but for matters and with a one year qualification. It identifies "Open" matters opened more than a year ago with no activity in the past year.
mstatus = 'OP'
AND mopendt < DATEADD(yy,-1,GETDATE())
--No new time
AND NOT EXISTS (SELECT * FROM timecard WHERE mmatter=tmatter AND tworkdt >= DATEADD(yy,-1,GETDATE()))
--No new costs
AND NOT EXISTS (SELECT * FROM cost WHERE mmatter=cmatter AND cdisbdt >= DATEADD(yy,-1,GETDATE()))
--No new trust
AND NOT EXISTS (SELECT * FROM trsttran WHERE mmatter=trmatter AND trdate >= DATEADD(yy,-1,GETDATE()))
--No new invoices or payments
AND NOT EXISTS (SELECT * FROM ledger WHERE mmatter=lmatter AND ltradat >= DATEADD(yy,-1,GETDATE()))
--No A/R or WIP
AND NOT EXISTS (SELECT * FROM inqsnap WHERE icode='M' AND ivalue=mmatter AND (ar_fees+ar_cost+ar_other+ar_unall+unb_hours+unb_fees+unb_cost)<>0)

No comments:

Post a Comment