Category: Queries – Patients

This query demonstrates the use of a subquery to create values in a column — nextreviewdate, in this case. Adjust the date range as needed.

SELECT
    (DATE(COALESCE(
        (SELECT MIN(nextreviewdate)
                 FROM sos.v_tpreviews
                 WHERE ptepisodenum = b.ptepisodenum
                 AND tpheadernum = c.tpheadernum
                 AND spv_signandfinalize = 1
                 AND rowstatus = 'O' ),c.nextreviewdate)
    )) AS nextreviewdate,
    (a.lastname+', '+a.firstname+' / '+a.id) AS "Patient",
    d.provcode AS "Primary Provider"
FROM
  sos.patients a
  JOIN sos.ptepisodes b ON a.ptnum = b.ptnum
  JOIN sos.tpheaders c ON b.ptepisodenum = c.ptepisodenum
  LEFT OUTER JOIN sos.providers d ON b.providernum = d.providernum
WHERE
   a.flag = 0
   AND b.currentflag = 1
   AND a.dischargedate IS NULL
   AND nextreviewdate BETWEEN '2009-09-01' AND '2009-09-30'
ORDER BY "NextReviewDate","Patient"

I would like to have a SQL command that shows the patient name, date of birth, intake date, and only patients who had the service eval which is
90801 on the CPT code. Ultimately I am trying to find out who our new patients were in 2007 and the ages.

A bit more than a basic query, the check for a 90801 (intake) service uses a “correlated subquery” in the WHERE clause of the main query. The query also uses a custom function we have added to the SOS database called AGEINYEARS, which gives us a person’s age at any point in time. We have also included an alternate approach that gives the same result without using a subquery.

SELECT DISTINCT
pt.lastname, pt.firstname, pt.id, pt.intakeDate, pt.dob,
sos.AGEINYEARS(dob,intakeDate) AS "AgeAtIntake"
FROM
sos.Patients pt
JOIN sos.journal jou ON pt.ptnum = jou.ptnum
JOIN sos.jcharges chg ON jou.jnum = chg.jnum
JOIN sos.services srv ON chg.servicenum = srv.servicenum
WHERE
pt.intakeDate BETWEEN '2007-01-01' AND '2007-12-31'
/* at least one 90801 service in pt ledger */
AND jou.trandate >= pt.intakedate
AND srv.cptcode = '90801'
ORDER BY
pt.lastname, pt.firstname

The alternate syntax, without the subquery, becomes:

SELECT DISTINCT
  pt.lastname, pt.firstname, pt.id, pt.intakeDate, pt.DOB, 
sos.AGEINYEARS(DOB,intakeDate) AS "AgeAtIntake"
 FROM
  sos.Patients pt
  JOIN sos.journal jou ON pt.ptnum = jou.ptnum
  JOIN sos.jcharges chg ON jou.jnum = chg.jnum
  JOIN sos.services srv ON chg.servicenum = srv.servicenum
 WHERE
  pt.intakeDate BETWEEN '2007-01-01' AND '2007-12-31'
  /* at least one 90801 service in pt ledger */
  AND jou.trandate >= pt.intakedate
  AND srv.cptcode = '90801'
 ORDER BY
  pt.lastname, pt.firstname

We know that about 25% of patients stop coming after the third session (including intake). (Another query tells us that). We are interested in surveying these patients to learn what we could have done better to have them stay in treatment (assuming that in most cases, successful psychotherapy takes more than 3 sessions).

So, I’d like to query out patients with an intake in the last 8 months, who were seen for three or less sessions and who have not had a treatment session for at least the last 60 days. One needs to keep in mind that I want to count only active treatment sessions, not finance charges, records request, no show fees or any other non-treatment session.

SELECT
  lastname, firstname, id,
  lfeedate AS "Last Service",
  priprvcode AS "primary Provider",
  (SELECT COUNT(DISTINCT a.trandate)
      FROM sos.journal a JOIN sos.jcharges b ON a.jnum = b.jnum
      WHERE a.ptnum = pt.ptnum
      AND servicenum IN
         (SELECT servicenum
         FROM sos.services
         WHERE srvcode IN ( 'A','B','C' ) ) )  
         /*replace 'A','B','C' above with the codes you want to count */
  AS "SrvDateCount"
FROM
  sos.rv_patients AS pt
WHERE
  IntakeDate BETWEEN (TODAY( ) - 240) AND TODAY()  
  /*automatically does last 8 months. Note that TODAY() returns the same value as "CURRENT DATE" */
  AND lfeedate < (CURRENT DATE - 90)
  AND SrvDateCount <= 3