SELECT
COALESCE(b.provcode,'None') AS "Primary Prov",
a.lastname,
a.firstname,
a.ID,
DATEDIFF(year,DOB,intakedate) AS "Age At Intake",
DATEDIFF(year,DOB,CURRENT DATE) AS "Age Now"
FROM
sos.patients a
JOIN sos.providers b ON a.providernum = b.providernum
WHERE
flag = 0 /*active patient */
AND dischargedate IS NULL /*no discharge date has been entered */
AND "Age At Intake" < 18
AND "Age Now" >= 18
ORDER BY "Primary Prov", a.lastname, a.firstname, a.id
Author: SOS Blog
Active Patient Balances Sorted by Primary Provider
Our practice has 40+ therapists and we would like to be able to give
them a simple monthly report showing account balances due by patients.
SELECT
prv.provcode AS "primary provider", pt.lastname, pt.firstname, pt.id, bal.ptbalance
FROM
sos.patients pt
JOIN sos.providers prv ON pt.providernum = prv.providernum
JOIN sos.PT_NONINSBALANCE bal ON pt.ptnum = bal.ptnum
WHERE
pt.flag = 0
AND pt.dischargedate IS NULL
AND pt.licnum = 101
ORDER BY "primary provider"
Accounts and Payors in Collections
I need a list showing all patients that are in collections.
Actually, patients are not in collections in SOS, payors are. The following query produces a list of payors that have a balance in collections, along with their associated patient accounts. The first query sorts in payor order, with payor name in the first column. The second query is the same results, but the patient column is first, and the list is sorted in that order.
SELECT
a.payorname +', '+a.firstname AS "Payor",
a.collectdate AS "To Collections",
b.balance,
c.lastname+', '+c.firstname+' / '+id AS "Account"
FROM
sos.payors a
JOIN sos.ptpayors b ON a.payornum = b.payornum
JOIN sos.patients c ON b.ptnum = c.ptnum
WHERE
b.balance > 0
AND a.collectdate IS NOT NULL
ORDER BY "Payor"
Same as the above, but in Patient Account order:
SELECT
c.lastname+', '+c.firstname+' / '+id AS "Account",
a.collectdate AS "To Collections",
b.balance,
a.payorname +', '+a.firstname AS "Payor"
FROM
sos.payors a
JOIN sos.ptpayors b ON a.payornum = b.payornum
JOIN sos.patients c ON b.ptnum = c.ptnum
WHERE
b.balance > 0
AND a.collectdate IS NOT NULL
ORDER BY "Account"
Another example, this one with primary provider, payor address and phone numbers, and last date of service:
SELECT
c.lastname+', '+c.firstname AS "AccountName",
c.id AS "AccountNumber",
c.socsec AS "PatientSSNum",
d.provlname +' '+ d.provfname AS "PriProvider",
a.payorname +' '+a.firstname AS "Payor",
a.Addr1,
a.Addr2,
a.City,
a.State,
a.zip,
a.Phone1Desc,
a.Phone1Area +'-'+ a.Phone1 AS "Phone_1",
a.Phone1Ext,
a.Phone2Desc,
a.Phone2Area +'-'+ a.Phone2 AS "Phone_2",
a.Phone2Ext,
a.Phone3Desc,
a.Phone3Area +'-'+ a.Phone3 AS "Phone_3",
a.Phone3Ext,
lastchargedate(c.ptnum) AS "Last DOS",
b.balance
FROM
sos.payors a
JOIN sos.ptpayors b ON a.payornum = b.payornum
JOIN sos.patients c ON b.ptnum = c.ptnum
LEFT OUTER JOIN sos.providers d ON c.providernum = d.providernum
WHERE
b.balance > 0
AND a.collectdate IS NOT NULL
ORDER BY
"AccountName"
EMRs, PHRs, Patient Portals…and your privacy
In August, the National Coordinator for Health IT, Dr. Farzad Mostashari, spoke about the need for a ‘targeted strategy’ to involve consumers in eHealth. He is concerned that all of the efforts being expended to acquire EMRs and for eligible providers (EPs) to achieve meaningful use of those EMRs must become more patient-centered.
One provision of the meaningful use requirements that will become increasingly important is the need to be able to provide an electronic version of the medical note to the patient. While there are a variety of ways by which this can be accomplished, Personal Health Records (PHRs) and Patient Portals into the provider’s EMR are two.
My own insurer provides a PHR on their website. I can enter lots of personal information about myself and my health if I choose to do so. Superficially, it appears this information can be accessed only by me, but I do not believe this is the case. The insurer can likely access the information too. Since it is mostly info they can get from my claims (and the claims history is available on the page), I am not too concerned about it, but I have been somewhat careful about entering things. If I were not pretty healthy, I think I would enter nothing.
The PHR to which I have access is a proprietary one. In August, Google removed their hat from the ring by discontinuing Google Health as a service effective January 1, 2012. Microsoft is well-entrenched in the healthcare arena, so I believe they will keep their product, Microsoft HealthVault, around. I wonder of what use these will be until providers can send information to the PHR for the patient.
For the last few weeks, I have been working with our web designer on re-writing our website. Now that so many people access the internet from their smart phones, we want to be sure the site works well on mobile phones and tablets. This focus has me wondering about a few things. I hope you can clear up some of these for me.
- How many of you use a PHR?
- Do you feel secure storing your personal medical information online?
- Do you use a smart phone for email?
- If so, for what other purposes do you use the smart phone?
I am hoping I will get some comments and responses from you. I wonder how many of you feel comfortable with the speed with which our lives are moving into ‘the cloud’.
Please jump in now. This inquiring mind really wants to know!
Emailing Reports From First Contact
While secure email systems are available to send documents containing Protected Health Information (PHI), most regular email transmissions can be intercepted and viewed by parties other than the intended recipient. For this reason, it is a best practice to send such documents as encrypted, password-protected, PDF files.
When viewing an intake form in First Contact, you will notice that there is an icon in the toolbar with the caption “Email Provider”.
Clicking that icon generates a report in the same format as the screen display, which displays in a report preview window. Either click File > Export Document > PDF…
… or click the Export Document icon on the toolbar.
In the Export Options window, click the field next to Password Security. Another window will open. Check the first box to Require a password to open the document and type the desired password in the indicated field. When you click OK at the bottom of that window, a confirmation box will appear. Re-type the password and click OK. That window will close and you will be back at the Export Options. Click OK there, then enter the desired location and name for the protected PDF file. Take note of this information. You will need it in order to attach the file to your email.
You will find yourself back at the report preview window. Just close that window with the X icon.
Open your email application, create your message and attach the document you just created. If you have not pre-shared the document password with the recipient, then you should send it separately via fax or phone. Do not include it in the body of email, as that would defeat the whole purpose of encrypting the document. Sending the password in a second email is almost as insecure. If you will be sending documents to this recipient on a regular basis, it is probably best to establish a standard password that you will use for that purpose and share it with the recipient beforehand.
