Revision [72]
This is an old revision of IntakesForPeriodWithAgeAtIntake made by admin on 2008-10-24 16:16:08.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
