I have a need for a filter on the encounter report. I need the filter to restrict the results to a CPT code that I am searching for. This is to produce are report for the hospital to show how many procedures that the doctor has done in past year in order to keep his privileges at the hospital.
Here is the original request sent to me:
"What I need is to obtain reports based upon procedures performed by Dr. East over the past year, actually, from 09/07/10 - present.
As we discussed, I went everywhere I told you under reports and administration. I am tapped out of ideas. We needs this urgently, yesterday, to meet credentialing deadlines.
Sherwin,
The fastest/easiest way to do this would be to query the database directly if you don’t need a “formal printed report”
If all you need are “numbers of” each of those procedures from those time periods as a “one time thing” I think I could whip up the appropriate queries pretty quickly. (A couple of hours).
Anyway, if you want to engage me on this, you can email me directly.
-Kevin Yeh kevin.y@integralemr.com
I have a ton of reports with features very much like this. I have to convert them to work using the billing provider vs. a custom provider designation we use, and tap in your codes, but if you just need hard-coded stuff, I will post you a modified report you can use right away.
Feel free to donate to oemr.org if you want to pay for it. Earmark your donation in any way you like. We would love to have a few extra bucks to help fund a larger development project!
I’ll post it by 5 EST today.
BTW, are you billing through the EMR? It matters for what type of report you need. I assume not, because you could otherwise just use the billing screen to get your data.
OK,here is the basic SQL query, but you really ought to get someone (like Kevin) to set you up with configurable reports that handle your output if you are not billing.
SELECT `date` , `code` , `pid` , `provider_id` , `authorized` , `encounter` , `code_text` , `modifier` , `units` , `fee` , `justify`
FROM `billing`
WHERE `code` = ‘93451’
OR `code` = ‘93453’
OR `code` = ‘93454’
OR `code` = ‘93455’
OR `code` = ‘93456’
OR `code` = ‘93458’
OR `code` = ‘93459’
OR `code` = ‘93460’
OR `code` = ‘93461’
OR `code` = ‘92980’
OR `code` = ‘92981’
OR `code` = ‘92982’
OR `code` = ‘92984’
AND `provider_id` = ‘01’
LIMIT 0 , 1000
First, you need to find out the doctor’s provider number in the EMR. Go to Administration/Other/Database. Find the ‘users’ table on the left hand side of the MyPHPAdmin window. Click on it to bring it up.
Click the “Browse” tab. Find his name in the “username” column and make a note of the first field ‘ID’. That is his id number in the EMR.
Now go to the SQL tab and paste in the sql query code from above.
Next change the line
AND `provider_id` = ‘01’
from ‘01’ to whatever the number is.
Now click on GO
You will get your results. Now go to the bottom of the page and find the Export button.
Use it.
Select whatever format you can handle…best OpenDocument spreadsheet if you have OpenOffice or Libreoffice. You will need to use CSV if you do not.
You might want to make sure to select the option to add the row headers to the top row.
Export it.
You now have data in a spreadsheet or csv that you can pretty up however you like.
Art,
Here’s a helpful bit of sql syntax. The “IN CLAUSE” http://www.w3schools.com/sql/sql_in.asp
That way you can just do where code IN (“99201”,“99202”,“92984,…,”)
OK, I am ready to pop the queries or even optional AND/OR billing code search boxes into a modified sl_receipts or a encounters_report, but it is useless unless they are using the EMR for billing. If they are not using fee sheets, we will have to search through an encounter description or another form to find the billing code (wherever they document the stuff. I wouldn’t think of this except for the fact that if they were doing billing, they could get this data via the billing manager fairly easily.
OOOOOH! Thanks Kevin! That’s like using CASE in a real language, right? I notice the documentation is for.asp, but it is good in both?
I am so bad at php and sql I am even unsure of the proper syntax to extend the WHERE part of the query for the equal to the C style:
if (provider_id==doc_id &&(code==xxxx||code==yyyy||code==zzzz){do stuff;}
I guess it is done as so:
AND ( b.code = 'xxx' OR b.code = 'yyy' OR b.code='zzz')";
and I would guess that using == or === would not help me in any way here….
Just gotta make sure what the situation on the ground is with how they are doing business, so we can make sure of the correct approach.
If you are using single quotes like that you would, but if you use double quotes for the “outside” then the single quotes will be ok on the inside without escaping like:
$sql = “SELECT * FROM billing WHERE code IN (‘H0032’,‘X00001’,‘X00002’)”
No, you can’t “reverse” an in clause like that and use field names that way.
Well, I just tried it, not knowing that I couldn’t.
Apparently I do these stupid things when the documentation is kinda iffy. MY mysql book doesn’t even HAVE the IN clause described!
……so I tried this:
SELECT *
FROM users
WHERE ‘Karin’
IN (
fname, mname, lname, username
)
LIMIT 0 , 30
It freaking worked like….perfect.
It tried it to look in the patient data, looking for Michael. Returned 204 rows. Single quotes on the value, no quote on the fields.
I have been a way fighting other fires but I like what I am reading here. They are using OpenEMR for billing and this informatin is in the billing table. it is needed for a formal report.
I will run some of the queries listed here and post back what worked.