Category: G4 Queries

We have a report that need to be done for the state each month for some of our clients. what I need is a query (to go out to a Lotus or excel spreadsheet) that has first and last names, client ID, DOB, insurance coverage, date of admission (intake) and number of times seen in that month, rendering provider. Ideally we could do this for multiple sort codes but not all of them. For example, I would need it for our sort codes MI, DB, DN, DC but not for the others and if it could be done in one query rather than combining the results of four that would be great.

This query is pretty straightforward, except that you specified RENDERING provider. That would cause multiple lines for each patient account, one for
each provider the patient has seen during the period specified. I have substituted primary provider, but the other is possible if you wanted it (with multiple lines as described).

Another issue is that this query will report dates of service rather than distinct service entries, which could also be done with a slight modification. Further, I have added a condition requiring a fee > 0 (“AND b.amount > 0”), but that may or may not be appropriate in your case. In addition, for your requested “insurance coverage” I have included the payor name and insured’s ID, but just for the first listed policy, if any. You did not specify exactly what you wanted.

Note that the OUTPUT statement you use depends on whether you run the query using dbisqlc or dbisqlg.

SELECT
  a.lastname, a.firstname, a.id, a.dob, a.intakedate,
  e.provcode,
  COUNT(DISTINCT b.trandate) AS "SrvDateCount",
  d.payorname AS "Insurer",
  d.insdid AS "Subscriber#"
FROM
  sos.patients a
  JOIN sos.journal b
  JOIN sos.jcharges c
  LEFT OUTER JOIN sos.rv_policies d ON a.ptnum = d.ptnum
  LEFT OUTER JOIN sos.providers e ON a.providernum = e.providernum
  LEFT OUTER JOIN sos.lookups f ON b.sortcode = f.lunum
WHERE
  b.trandate BETWEEN '2001-01-01' AND '2001-03-31'
  AND d.inspos = 1 //only PRIMARY insurance
  AND f.lucode IN ('MI','DB','DN','DC')
  AND b.amount > 0
GROUP BY
  a.lastname, a.firstname, a.id, a.dob, a.intakedate, e.provcode,"insurer","subscriber#"
;
OUTPUT TO c:\sos\statereport.html FORMAT HTML