Daily payment summary

[aussiedentist]

I use the daily payment summary a lot, and also for tracking income, etc

I get very large 50 page reports,

Is there a way in which I can get a summary of each payment type

eg cheque, amount, cash, amount, health fund, amount, medicaid, amount, credit card, amount, etc etc, and then a total for everything?

A lot of the time, I find I need to go through manually and write things down which is very time consuming

[KevinRossen]

Try this:
-- edited originally posted query
-- edit2: added totals

Code: Select all

SET 
	@StartDate = CURDATE(), -- To set a specific date range use 'YYYY-MM-DD'
	@EndDate = CURDATE();
DROP TABLE IF EXISTS tmp1;
CREATE TABLE tmp1
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 p.PayDate BETWEEN @StartDate AND @EndDate
GROUP BY p.PayDate,d.ItemName;
INSERT INTO tmp1
SELECT 
	DATE(cp.DateCP) AS PayDate,
	'InsPay' AS PayType,
	ROUND(SUM(cp.InsPayAmt),2) AS $Total
FROM claimproc cp
INNER JOIN claimpayment cpay ON cpay.ClaimPaymentNum=cp.ClaimPaymentNum
WHERE cp.DateCP BETWEEN @StartDate AND @EndDate
GROUP BY PayDate
ORDER BY PayDate, PayType;
SELECT * FROM tmp1
UNION
SELECT
	'',
	'TOTAL',
	SUM($Total)
FROM tmp1;
DROP TABLE IF EXISTS tmp1;