Open Dental Forum

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;