How can I FIlter by PayType?

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
ironhawk1307
Posts: 4
Joined: Fri Jan 05, 2024 12:26 am

How can I FIlter by PayType?

Post by ironhawk1307 » Fri Jan 05, 2024 12:35 am

I've below query ortho payments here I also want to filter by Payment Group currently I can able to filter by payment PayType
Image

SELECT
SUM(amt) AS total_sum
FROM (
SELECT
payment.PayDate AS DatePay,
MAX(CONCAT(patient.LName, ', ', patient.FName, ' ', patient.MiddleI)) AS lfname,
GROUP_CONCAT(DISTINCT provider.Abbr) AS ProviderAbbr,
payment.CheckNum,
COALESCE(SUM(paysplit.SplitAmt), 0) AS amt,
payment.PayNum,
definition.ItemName,
payment.PayType
FROM
payment
LEFT JOIN paysplit ON payment.PayNum = paysplit.PayNum
LEFT JOIN patient ON payment.PatNum = patient.PatNum
LEFT JOIN provider ON paysplit.ProvNum = provider.ProvNum
LEFT JOIN definition ON payment.PayType = definition.DefNum
WHERE
payment.PayDate BETWEEN '2024-01-01' AND '2024-01-05'
AND payment.PayType IN (70, 71)
AND paysplit.UnearnedType NOT IN (301, 325)
GROUP BY
payment.PayNum,
payment.PayDate,
payment.CheckNum,
definition.ItemName,
payment.PayType,
provider.ProvNum
) AS subquery;

Post Reply