Payment Type Totals by Date Range Query Help

[jclaydds]

I was noticing an uptick in my Credit Card Service Charges. I have great difficulty making a lot of sense out of my monthly statements from my credit card processing company. I suspect the uptick in charges may simply be from the fact that my patients are paying more often by credit card rather than by cash or check. I am attempting to run a Query to get totals for payments for each payment type for a date range. I would like to see these totals by year (date range) so I can confirm my suspicion that patients are using credit cards more often. I want to see how much more (totals) they are using their credit cards and compare the percentage increase to the increase in credit card service charges to see if they correlate. Another possibility is the credit card processing company is just charging more for their services.

I looked at Query 702 but I don't trust the numbers it is giving me. I don't see any allowance for adjustments in Query 702 but this report is GROSSLY overstating my total collections so I expect many of the other totals also are overstated. I also looked at Query 1030. It would almost do the trick (the totals are accurate) but it only gives grand totals on patient payments and insurance payments. My main goal is to get the patient payments broken down into payment types so I can look specifically at certain payment types such as credit cards, Care Credit, etc. There may be another Query that may give me what I am looking for but I couldn't locate it. Any help would be appreciated.

[rhaber123]

For credit card processing, we use http://www.paymentdepot.com
Their statements and bank deposits works well with OD reports and our bank statements, match nicely at the end of the month.
We tell our patients that we prefer and we accept debit cards now, they enter their debit card pins, and that saves us few hundred dollars of CC transactions fees at the end of the month.
My staff were hesitant first when we started, but now when collecting payments they ask the patient: " Debit or Credit? we prefer Debit"
If you join, ask them to ad that additional debit card programming to your terminal, where your patient have the option to enter their pin number.
Some payment processors may tell you that the terminal will know, I think that is not true. You will not get charged the discounted debit card fees if your patient does not enter their debit card pin.

The query below #702 has been slightly modified, change the dates accordingly. It works well in our office, matches our daily and monthly collections reports, etc...
Hope that help

Richard Haber DDS
Santa Monica CA
======================================================================================================

Code: Select all

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

SET @FromDate='2017-01-01', @ToDate='2017-01-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,

(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*/
Checks.PayAmt AS $Pat_Checks,
InsInc.TransAmount AS $Ins_Checks,
CareCredit.PayAmt AS $Care_Credt

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;   

[jclaydds]

Richard,

Thanks for the input. I copied and pasted Query 702 from your post and still am getting some significant errors in the data. I don't think the Query takes into account adjustments to give adjusted collection data but still yet....the totals are probably 35%+ higher than my actual totals for the complete year. Maybe someone from Open Dental can chime in with ideas on why the totals are so overstated in this Query.

Thanks!

[cmcgehee]

In what way are you adding adjustments to collections? Open Dental doesn't have an official way to make adjustments to collections. The Adjustment objects only affect production. Adjustments to collections could be made by using a negative patient payment, for example. This query was set up for a specific customer's specific payment types, so it could be that the way your payment types are named a payment could be counting in multiple categories.

[jclaydds]

My error......I was mistakenly thinking that adjustments would affect collections trying to account for the large discrepancies I am seeing in the totals in this query as compared to an yearly report for the same year. Since my payment types may differ from the customer for whom the query was written.....would you suggest I call in and discuss it with support? Thanks!

[cmcgehee]

would you suggest I call in and discuss it with support?

I would recommend that. It will be easier to troubleshoot when we can compare internal reports and run other queries to nail down the discrepancies.