Medicaid Patients by Rendering Provider within Specified Date Range

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"