I need a report that lists Medicaid clients seen between a specific date range by rendering provider.
The following query should deliver the goods. Because we are looking at RENDERING provider, the same patient may be listed two or more times, depending on the number of different providers seen during the specified period. Speaking of period, be sure to adjust the desired date range in the first condition under WHERE.
Notice that the second word of the query is DISTINCT. Adding that keyword after SELECT eliminates duplicate rows in the output. Without that word, there would be a row for every matching charge in each patient’s ledger.
SELECT DISTINCT (prv.provlname + ', '+ prv.provfname + ' (' + prv.provcode + ')') AS "provider", (UPPER(pt.lastname) +', '+ pt.firstname+ ' '+ pt.midinit) AS "ptfullname", ('Acct ID: '+ pt.id) AS "acctid", ('Medicaid #: ' + pol.insdid) AS "medicaidnum" FROM sos.patients pt JOIN sos.ptpayors ptp ON pt.ptnum = ptp.ptnum JOIN sos.carriers car ON ptp.payornum = car.payornum JOIN sos.ptpolicies pol ON ptp.ptpayornum = pol.ptpayornum JOIN sos.journal jou ON pt.ptnum = jou.ptnum JOIN sos.jcharges chg ON jou.jnum = chg.jnum JOIN sos.providers prv ON chg.providernum = prv.providernum WHERE -- desired date range of charge entries jou.trandate BETWEEN '2000-01-01' AND '2012-3-31' -- carrier is set as "Medicaid" AND (car.coverage = 'D' OR car.insflag = 'A') -- the Medicaid policy was active on the date of the charge entry AND jou.trandate BETWEEN COALESCE(pol.active,'1900-01-01') AND COALESCE(pol.inactive,'2100-12-31') ORDER BY "provider","ptfullname"