Payment reports

For users or potential users.
Post Reply
khdilger
Posts: 112
Joined: Wed May 05, 2010 5:56 am

Payment reports

Post by khdilger » Tue Jan 30, 2018 8:21 am

Is there a query out there that will return a summary of payments?

I am looking for a report that generates the sum of payments for each day they were made. I would like them totaled for each day seperately for checks/cash and credit cards. I want to be able to cross reference bank deposits and find the amount on my bank statement, as well as cross reference credit card payments and find those deposits as well.

rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Re: Payment reports

Post by rhaber123 » Wed Jan 31, 2018 5:29 pm

Income during a certain period, cash credit checks
Query 702 should do it. or try this modified version
CHANGE THE DATES ACCORDINGLY

Code: Select all

     
/*702 Income during a certain period, cash credt checks*/

SET @FromDate='2017-10-01', @ToDate='2017-10-31';
SELECT A.PayDate AS 'Date', DATE_FORMAT(A.PayDate, "%W") AS 'Weekday',

Cash.PayAmt AS $Cash,

(COALESCE(VisaMc.PayAmt,0)+COALESCE(Discover.PayAmt,0)+COALESCE(AMEX.PayAmt,0)+COALESCE(CCAuto.PayAmt,0)) AS $CC_cards, 




CareCredit.PayAmt AS $Care_Credit,





Checks.PayAmt AS $Pat_Checks,

InsInc.TransAmount AS $Ins_Checks,

(COALESCE(InsInc.TransAmount,0)+COALESCE(Checks.PayAmt,0)) AS $Checks_Total,








(COALESCE(Cash.PayAmt,0)+COALESCE(Checks.PayAmt,0)+COALESCE(VisaMc.PayAmt,0)+COALESCE(Discover.PayAmt,0)+COALESCE(AMEX.PayAmt,0)+COALESCE(CCAuto.PayAmt,0)+COALESCE(Moneyorder.PayAmt,0)+COALESCE(InsIntRec.PayAmt,0)+COALESCE(EFT.PayAmt,0)+COALESCE(CareCredit.PayAmt,0)+COALESCE(PatRefCheck.PayAmt,0)+COALESCE(RefIns.PayAmt,0)+COALESCE(RefCC.PayAmt,0)+COALESCE(MedicaidPayRev.PayAmt,0)+COALESCE(InsInc.TransAmount,0)) AS $Total_Income


/*Total of all columns*/















FROM
(
(SELECT PayDate FROM payment GROUP BY PayDate)
UNION 
(SELECT CheckDate AS PayDate FROM claimpayment GROUP BY PayDate)
) A 
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%Check%'
	GROUP BY PayDate
) Checks ON Checks.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%Cash%'
	GROUP BY PayDate
) Cash ON Cash.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%VISA/MC%'
	GROUP BY PayDate
) VisaMc ON VisaMc.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%Discover%'
	GROUP BY PayDate
) Discover ON Discover.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%AMEX%'
	GROUP BY PayDate
) Amex ON Amex.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%Credit Card%'
	GROUP BY PayDate
) CCAuto ON CCAuto.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%Money Order%'
	GROUP BY PayDate
) MoneyOrder ON MoneyOrder.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%EFT%'
	GROUP BY PayDate
) EFT ON EFT.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%CARE CREDIT%'
	GROUP BY PayDate
) CareCredit ON CareCredit.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%PATIENT REFUND CHECK%'
	GROUP BY PayDate
) PatRefCheck ON PatRefCheck.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%REFUND INSURANCE%'
	GROUP BY PayDate
) RefIns ON RefIns.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%REFUND CC%'
	GROUP BY PayDate
) RefCC ON RefCC.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%MEDICAID PAYMENT REVERSAL%'
	GROUP BY PayDate
) MedicaidPayRev ON MedicaidPayRev.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%Insurance Interest Received%'
	GROUP BY PayDate
) InsIntRec ON InsIntRec.PayDate=A.PayDate
LEFT JOIN (
	SELECT cpay.CheckDate AS TransDate,SUM(cproc.InsPayAmt) AS TransAmount
	FROM claimpayment cpay,claimproc cproc
	WHERE cproc.ClaimPaymentNum=cpay.ClaimPaymentNum
	GROUP BY TransDate
) InsInc ON InsInc.TransDate=A.PayDate
WHERE A.PayDate BETWEEN (@FromDate) AND (@ToDate)
ORDER BY A.PayDate;







JoeMontano
Posts: 64
Joined: Thu Aug 20, 2015 11:08 am

Re: Payment reports

Post by JoeMontano » Thu Feb 01, 2018 8:01 am

Query 1030 and 783 might be good options too! http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
Joe Montaño
Software Engineer
Open Dental Software
Joe@OpenDental.com

khdilger
Posts: 112
Joined: Wed May 05, 2010 5:56 am

Re: Payment reports

Post by khdilger » Thu Feb 01, 2018 10:59 am

Thanks!

rscottdds
Posts: 15
Joined: Fri Feb 24, 2012 11:21 am

Re: Payment reports

Post by rscottdds » Wed Feb 07, 2018 9:34 am

khdilger wrote:Is there a query out there that will return a summary of payments?

I am looking for a report that generates the sum of payments for each day they were made. I would like them totaled for each day seperately for checks/cash and credit cards. I want to be able to cross reference bank deposits and find the amount on my bank statement, as well as cross reference credit card payments and find those deposits as well.
I use the built in payment report and export it to Excel. Then use the excels pivot table function to do exactly what you are looking to do (reconsile bank and credit card statements). Works really well.
Robert A Scott.

Post Reply