Category: General

Intake Count for Period

Posted on | by SOS Blog | Leave a Comment on Intake Count for Period 
“Is there a way that I might be able to run a report that will tell me the exact number of intakes in a given
period of time?”
SELECT
   Patients.LicNum, 
   Providers.ProvCode, 
   Count(*) as "Count"
FROM
   sos.Patients
   LEFT OUTER JOIN sos.Providers ON Patients.ProviderNum = Providers.ProviderNum
WHERE
   Patients.IntakeDate BETWEEN '2003-01-01' AND '2003-12-31'
GROUP BY
   Patients.LicNum, 
   Providers.ProvCode
ORDER BY
   Patients.LicNum, 
   Providers.ProvCode
The "Licnum" represents the data set number. If you have only one data set, you can leave that out.

Working With User-Defined Field Dates

Posted on | by SOS Blog | Leave a Comment on Working With User-Defined Field Dates

Dates in UD fields that are configured to use date entry pictures (such as “@D2”) are stored as numbers that represent the number of days since 12/28/1800. So if you query the value in such a field, you will output something like “75899” rather than the date you expect. In addition, if your WHERE clause makes comparisons that depend on dates in those fields, you won’t get anything in your result set. To convert those values in your query to dates that you can work with, you must add the value stored in the UD field to the 12/28/1800 base date. Here is an example:

SELECT
  lastname,
  firstname,
  dob,
  id,
  DATEADD(day,fld1,'1800-12-28') AS "Closed chart",
  DATEADD(day,fld2,'1800-12-28') AS "RT to Therapist",
  DATEADD(day,fld3,'1800-12-28') AS "Back/OK",
  DATEADD(day,fld5,'1800-12-28') AS "To scan",
  DATEADD(day,fld6,'1800-12-28') AS "Last scan/reopen"
FROM
  sos.rv_patients
WHERE
  "Back/OK" BETWEEN '2007-05-01' AND '2007-05-31'

Staff Productivity

Posted on | by SOS Blog | Leave a Comment on Staff Productivity

Recently we have allowed one of our part time staff to telecommute from home. She has a VPN connection to our Terminal Server here at the main office. Any ideas of how I can track her time–or at least verify her hours? While her work is on the “honor” system, I’d still like the ability to monitor the quantity of work. Any ideas?

While I believe I can see the time she logs on and off of SOS, her log on time doesn’t mean she is actually working during that time. How about looking at amount of work on her daysheet?

Actually, there is a good bit of literature that indicates higher productivity in home workers, and this will allow your staff to show that they can be at least as productive at home as at the office. The following query would do the trick if you just want to use number of transactions entered as a metric. I would suggest that you use it over a substantial “in-office” period to establish a baseline, then once she settles in at home, do it again with the “at-home” date range. This query would give you all users who enter transactions, but you can certainly add a condition to limit to certain adduser values if you like.

SELECT
   COALESCE(STRING(adddate), 'ALL DATES') AS "DATE",
   COALESCE(adduser, 'ALL USERS') AS "USER",
   COUNT(*) AS "ENTRIES"
FROM
   sos.journal
WHERE
   adddate BETWEEN '2008-01-01' AND '2008-01-31'
GROUP BY
   ROLLUP(adddate, adduser)
ORDER BY
   "DATE", "USER"

Patients with Scheduler and Transaction Screen Warnings

Posted on | by SOS Blog | Leave a Comment on Patients with Scheduler and Transaction Screen Warnings

As we move into next year, we must prepare for our usual mad flurry of activity in updating patient records to reflect the new calendar year. Deductibles need to be re-configured. Copays that increase with session frequency also need to be readjusted. We usually put such information under the ADDITIONAL tab in the patient record, specifically in the spaces called Scheduler reminder and Transaction Screen Reminder. I need a query that shows the data entered in these fields for each patient.

This query will output a line if there is either a “don’t schedule” reason OR a transaction memo. The list will include only Active list patients with no discharge date entered and something entered in either or both of the reminder fields.

SELECT
  lastName+','+firstName+' '+ id AS "Patient",
  noschedreason,
  trannote
FROM
  sos.patients
WHERE
  flag = 0
  AND dischargedate IS NULL
  AND (noSchedReason <>''  OR trannote <>'')
ORDER By LastName, FirstName