Volumes - Credits

This will be the first in a series of queries on volumes of transactions. Understanding the volume of transactions flowing through the system is an important aspect of managing the accounting department. The following measures the number of documents and dollar value of non-reversed payments received by period.

SELECT
YEAR(peendt) AS 'Year',
MONTH(peendt) AS 'Month',
SUM(numdocs) AS '# Documents',
SUM(sumpmt) AS '$ Payments'
FROM
(SELECT
lbatch,
lperiod,
llcode,
peendt,
COUNT(DISTINCT ldocumnt) AS numdocs,
SUM(lamount) AS sumpmt
FROM
ledger
INNER JOIN periodt ON lperiod=pe
INNER JOIN ledcode ON llcode=lccode
WHERE lccollhs='Y'
AND peendt >= '1/1/05'
AND lzero <> 'R'
GROUP BY lbatch,lperiod,llcode,peendt) AS batchledg
GROUP BY YEAR(peendt),MONTH(peendt)
ORDER BY YEAR(peendt),MONTH(peendt)