Payment by Type, Previous Month

[KevinRossen]

I wrote this query today and thought others might find it useful. It returns all payments from the previous month by date and payment type. Let me know if you have any questions:

EDIT: 3/19-Changed the query to work nicely with replication

Code: Select all

SET @SetDate=DATE_FORMAT(CURDATE()-INTERVAL 1 MONTH, '%y-%m');
-- Default month in prior month. Change above line to change date range
-- Example for current month: SET @SetDate=DATE_FORMAT(CURDATE(), '%y-%m');
-- Example for specific month: SET @SetDate=DATE_FORMAT('2014-12-01', '%y-%m');
SELECT 
	p.PayDate,
	d.ItemName AS PayType,
	ROUND(SUM(p.PayAmt),2) AS $Total
FROM payment p
LEFT JOIN definition d ON p.PayType=d.DefNum
WHERE DATE_FORMAT(p.PayDate, '%y-%m')=@SetDate AND p.PayType<>0
GROUP BY p.PayDate,d.ItemName
UNION
SELECT 
	DATE(cp.DateCP) AS PayDate,
	'InsPay' AS PayType,
	ROUND(SUM(cp.InsPayAmt),2) AS $InsTotal
FROM claimproc cp
INNER JOIN claimpayment cpay ON cpay.ClaimPaymentNum=cp.ClaimPaymentNum
WHERE DATE_FORMAT(cp.DateCP, '%y-%m')=@SetDate
GROUP BY PayDate
ORDER BY PayDate, PayType;

[allends]

For any customers on 15.1, the Payments report will now give you the payments grouped by payment type and ordered by date.

[KevinRossen]

Nice! I just built 15.1 last night from source on my test system, so I haven't had time to play with it yet. That's a nice update!