Hosted By Open Dental
http://opndn.com/forum/
Code: Select all
/*702 modified - Income report from @FromDate to @ToDate day totalled, separated by specific payment type with totals*/
SET @FromDate='2016-12-01', @ToDate='2016-12-30';
SELECT A.PayDate AS 'Date', DATE_FORMAT(A.PayDate, "%W") AS 'Weekday',
Cash.PayAmt AS $PatIncCash_,
Checks.PayAmt AS $PatIncCheck_,
(COALESCE(VisaMc.PayAmt,0)+COALESCE(Discover.PayAmt,0)+COALESCE(AMEX.PayAmt,0)+COALESCE(CCAuto.PayAmt,0)) AS $PatIncCC_,
Payment.PayAmt AS $Payment_,
EFT.PayAmt AS $EFTPayment_,
CareCredit.PayAmt AS $CARECREDIT_,
InsIntRec.PayAmt AS $InsInterestReceived_,
InsInc.TransAmount AS $InsPayment_,
PatRefCheck.PayAmt AS $PATREFUNDCHECK_,
RefIns.PayAmt AS $REFINSURANCE_,
RefCC.PayAmt AS $REFCC_,
MedicaidPayRev.PayAmt AS $MEDICAIDPAYMENTREVERSAL_,
(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(RefCC.PayAmt,0)+COALESCE(PatRefCheck.PayAmt,0)+COALESCE(CareCredit.PayAmt,0)+COALESCE(Payment.PayAmt,0)) AS $PatIncTotal_,/*Total of PatIncCash, PatIncCheck, VisaMc, Discover, AMEX, CCAuto, RefundCC, PatRefCheck, CareCredit, and Payment*/
(COALESCE(InsInc.TransAmount,0)+COALESCE(InsIntRec.PayAmt,0)+COALESCE(MedicaidPayRev.PayAmt,0)+COALESCE(RefIns.PayAmt,0)) AS $InsIncTotal_,/*Total of InsInterestReceived, RefundIns, MedicaidPayRev, and InsInc*/
(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(Payment.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 $TotalIncome_ /*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 '%Payment%'
GROUP BY PayDate
) Payment ON Payment.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;