/*Conv - Accounts Needing An Income Transfer*/
SELECT 
	g.PatNum AS Guarantor,
	g.LName,
	g.FName,
	g.BalTotal,
	GROUP_CONCAT((CASE WHEN PatBal > 0.005 THEN p.FName END)) AS PatsWithPosBals,
	SUM(CASE WHEN PatBal > 0.005 THEN PatBal ELSE 0 END) AS PosBalTotal,
	GROUP_CONCAT((CASE WHEN PatBal < -0.005 THEN p.FName END)) AS PatsWithNegBals,
	SUM(CASE WHEN PatBal < -0.005 THEN PatBal ELSE 0 END) AS NegBalTotal
FROM (
	/*Get patient balances per patient*/
	SELECT 
		trans.PatNum,
		SUM(TranAmount) AS PatBal
	FROM (
		/*Get procedure fees for completed procedures*/
		SELECT 
			pl.PatNum,
			pl.ProcFee * (pl.UnitQty + pl.BaseUnits) AS TranAmount
		FROM procedurelog pl
		WHERE pl.ProcStatus = 2 -- Completed procedures
		
		UNION ALL
		
		/*Get paysplit amounts not attached to a payment plan*/
		SELECT 
			ps.PatNum,
			-ps.SplitAmt AS TranAmount
		FROM paysplit ps
		WHERE ps.PayPlanNum = 0
		
		UNION ALL
		
		/*Get adjustment amounts*/
		SELECT 
			a.PatNum,
			a.AdjAmt AS TranAmount
		FROM adjustment a
		
		UNION ALL
		
		/*Get insurance payments - writeoffs*/
		SELECT 
			cp.PatNum,
			-cp.InsPayAmt - cp.Writeoff AS TranAmount
		FROM claimproc cp
		WHERE cp.Status IN (1,4,5,7) -- Received, supplemental, CapClaim, CapComplete
		
		UNION ALL
		
		/*Get completed payment plan charges*/
		SELECT 
			pp.PatNum,
			-pp.CompletedAmt AS TranAmount
		FROM payplan pp
	) trans
	GROUP BY trans.PatNum
) patbal
INNER JOIN patient p 
	ON p.PatNum = patbal.PatNum
INNER JOIN patient g 
	ON g.PatNum = p.Guarantor
WHERE g.BalTotal = 0
GROUP BY g.PatNum
HAVING ABS(SUM(PatBal)) < 0.005 AND SUM(ABS(PatBal)) > 0.005