Query

[DrDRH]

Jordan, can one of your team write or has a QUERY for finding list of all patients who started treatment (by initial procedure code, D0150 or D8820, etc)
on or before a prescribed date? We are an ortho practice looking for all starts greater 2 yrs ago... Thanks.
DrDon

[steveng]

I have a query that we use in our office to track Ortho visits:

Code: Select all

SET  @fromDate = ' 2009-01-01', /* starting date */
      @toDate  =  'current_date',   /* leave as is or replace with date as above */

  @pos=0;
SELECT patient.lname, patient.fname, patient.address, patient.address2, 
patient.city, patient.state, patient.zip, proccode,
CASE
WHEN (pl.procstatus = 1) THEN "Treatment Planned" 
WHEN (pl.procstatus = 2) THEN "Complete"   
WHEN (pl.procstatus = 3) THEN "Existing Current Provider"  
WHEN (pl.procstatus = 4) THEN "Existing Other Provider"  
WHEN (pl.procstatus = 5) THEN "Referred Out"  
WHEN (pl.procstatus = 6) THEN "Deleted"
END as "Procedure Status", 
pl.procdate

FROM patient 
INNER JOIN procedurelog  pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum

Inner JOIN definition df ON df.DefNum=pc.ProcCat
AND df.DefNum = 83 /* Ortho */

WHERE date(pl.procdate) >= @fromDate 
and date(pl.procdate) <=@toDate
AND pl.procstatus < 6

ORDER BY 1 asc ,10 asc

Hope this helps,

[DrDRH]

Steven G, thanks for the info. This is ALL NEW to me. What part of the Query is editable from the date we are looking or before? If we have a specific procedure code that we know initiates treatment (ie, D8820) and what to know all patients that have been in treatment 24 months or longer since that procedure code date...how does that work.
Again, many thanks since I do not understand queries.
DrDRH

[steveng]

Dr DRH,

Try this:
You can change date ranges as needed and @procCode#. I included 3 variables for codes. If you're only interested in 1 code then keep it as it is.
Keep in mind that if you add more procedure codes you may be interested in sorting it to you liking as it will pull many records for one patient if more than 1 code exists. Since you're new to queries, then export your results to excel spreadsheet and sort it there or apply filters.
That is what our girls do with results.

Code: Select all

SET  @fromDate = ' 2009-01-01', /* starting date */
       @toDate  =  '2009-12-31',   /* to date*/

     @procCode1 =  'D8820',    /*procedure code 1*/
     @procCode2 = 'D8820',  /*procedure code 2*/
     @procCode3 = 'D8820',  /*procedure code 3*/

/************** do NOT edit anything beyond this line ****************/  
  @pos=0;
SELECT patient.lname, patient.fname, patient.address, patient.address2, 
patient.city, patient.state, patient.zip, proccode,
CASE
WHEN (pl.procstatus = 1) THEN "Treatment Planned" 
WHEN (pl.procstatus = 2) THEN "Complete"   
WHEN (pl.procstatus = 3) THEN "Existing Current Provider"  
WHEN (pl.procstatus = 4) THEN "Existing Other Provider"  
WHEN (pl.procstatus = 5) THEN "Referred Out"  
WHEN (pl.procstatus = 6) THEN "Deleted"
END as "Procedure Status", 
pl.procdate
FROM patient 
INNER JOIN procedurelog  pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
WHERE date(pl.procdate) >= @fromDate 
and date(pl.procdate) <=@toDate
AND proccode in (  @procCode1 , @procCode2, @procCode3 )
AND pl.procstatus < 6;

Hope this helps,