Thursday, October 22, 2009

Collections by Worked Period for Specific Collections Month

Collections is an important aspect of any law firm's monthly stats. Delving a little deeper into the collections number, though, can yield some interesting information. The following query will tell you which worked period your collections have come from. For example, let's say in September you collected $5 million dollars. Of that $5 million, 3 million was worked in August and 1 million was worked in July and the other million was worked prior to July. You could now compare this to prior months or prior years to see if your worked period comparisons are staying consistent.

SELECT
peendt,
SUM(lfamnt) AS 'Fee Collections'
FROM ledger l1
INNER JOIN ledgfee l2 ON l1.lindex=l2.lindex
INNER JOIN periodt p1 ON l2.lwoper=p1.pe
INNER JOIN ledcode ON llcode=lccode
WHERE l1.lperiod = '0909' --Collections Period
AND lccollhs = 'Y'
GROUP BY peendt
ORDER BY peendt DESC

2 comments:

  1. I was just directed to your site by our billing manager. HURRAY!

    I'm a Financial Systems manager for a large Vault 100 firm in Manahattan. I'm always looking for resources to help in the various data requests, etc. that I come across.

    ReplyDelete