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;