Patients with Identifying Data, Provider, and Visit Count for Specified SortCodes
Back to Query Directory
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
patients a
JOIN journal b
JOIN jcharges c
LEFT OUTER JOIN rv_policies d ON a.ptnum = d.ptnum
LEFT OUTER JOIN providers e ON a.providernum = e.providernum
LEFT OUTER JOIN 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.xls FORMAT EXCEL
/* Must use DBISQLG for EXCEL format. If using dbisqlc, then use . . .
OUTPUT TO c:\sos\statereport.wks FORMAT LOTUS
instead. Excel can open either. */
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
patients a
JOIN journal b
JOIN jcharges c
LEFT OUTER JOIN rv_policies d ON a.ptnum = d.ptnum
LEFT OUTER JOIN providers e ON a.providernum = e.providernum
LEFT OUTER JOIN 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.xls FORMAT EXCEL
/* Must use DBISQLG for EXCEL format. If using dbisqlc, then use . . .
OUTPUT TO c:\sos\statereport.wks FORMAT LOTUS
instead. Excel can open either. */
CategoryQueries
