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.
Payment reports
Re: Payment reports
Income during a certain period, cash credit checks
Query 702 should do it. or try this modified version
CHANGE THE DATES ACCORDINGLY
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;
-
- Posts: 64
- Joined: Thu Aug 20, 2015 11:08 am
Re: Payment reports
Query 1030 and 783 might be good options too! http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
Re: Payment reports
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.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.
Robert A Scott.