Intakes for Period with Age at Intake


Back to Query Directory
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,
AGEINYEARS(dob,intakeDate) AS "AgeAtIntake"
FROM
Patients pt
JOIN journal jou ON pt.ptnum = jou.ptnum
JOIN jcharges chg ON jou.jnum = chg.jnum
JOIN 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, >
AGEINYEARS(DOB,intakeDate) AS "AgeAtIntake"
 FROM
  Patients pt
  JOIN journal jou ON pt.ptnum = jou.ptnum
  JOIN jcharges chg ON jou.jnum = chg.jnum
  JOIN 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


CategoryQueries
Valid XHTML 1.0 Transitional :: Valid CSS :: Powered by WikkaWiki