Payment by Type, Previous Month
Posted: Tue Mar 17, 2015 1:23 pm
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
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;