Intakes for Period with Age at Intake

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

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.