Zero Payors
Back to Query Directory
I need a list of active patients who have two or more non-insurance payors, but at least one is set to zero percent responsibility.
SELECT p.lastname,p.firstname,p.id,(ptp.payorname+', '+ptp.firstname) AS "Zero Payor"
FROM
patients p
JOIN rv_ptpayors ptp ON p.ptnum = ptp.ptnum
JOIN (SELECT ptnum,COUNT(*) AS payorcount FROM rv_ptpayors WHERE payortype <> 'I'
GROUP BY ptnum HAVING payorcount > 1)
AS payorcount ON p.ptnum = payorcount.ptnum
WHERE
p.licnum = 101
AND p.flag = 0
AND p.dischargedate IS NULL
AND ptp.payortype <> 'I'
AND ptp.pctpays = 0
ORDER BY
p.lastname,p.firstname,p.id
FROM
patients p
JOIN rv_ptpayors ptp ON p.ptnum = ptp.ptnum
JOIN (SELECT ptnum,COUNT(*) AS payorcount FROM rv_ptpayors WHERE payortype <> 'I'
GROUP BY ptnum HAVING payorcount > 1)
AS payorcount ON p.ptnum = payorcount.ptnum
WHERE
p.licnum = 101
AND p.flag = 0
AND p.dischargedate IS NULL
AND ptp.payortype <> 'I'
AND ptp.pctpays = 0
ORDER BY
p.lastname,p.firstname,p.id
CategoryQueries
