Open Dental Forum

I haven't quite figured out how to write queries (SQL language).

I created a Patient Field Definition called "QDP Anniversary" with a date.

I'd like to run a report that would list all my QDP patients. So, the report would be active patients with a date in that field that is greater than January 1st, 2013.

This is what I came up with. But, I'm getting an "unknown column - QDPanniversary" error. I came up with this by copying from another report and modifying it where I thought was appropriate. But, so far, no go.

SELECT patient.LName, patient.FName, patient.EMail
FROM patient
WHERE PatStatus=0 AND QDPanniversary > "2013-01-01";


Anyone handy with SQL? Thanks!

Mike

Your problem is that those added fields get stored in another table called "patfield". So you would have to link both tables using a common field like the patnum field. This is what you would get:

select patient.Lname, patient.Fname, patient.email, patfield.fieldvalue
From patient, patfield
Where patient.patstatus = 0
AND patient.patnum = patfield.patnum
AND patfield.fieldname = 'QDPAnniversary''
AND patfield.fieldvalue > "2013-01-01"

Jorgebon wrote:Your problem is that those added fields get stored in another table called "patfield". So you would have to link both tables using a common field like the patnum field. This is what you would get:

select patient.Lname, patient.Fname, patient.email, patfield.fieldvalue
From patient, patfield
Where patient.patstatus = 0
AND patient.patnum = patfield.patnum
AND patfield.fieldname = 'QDPAnniversary''
AND patfield.fieldvalue > "2013-01-01"

thanks for your help. I ran that and got an error:

OK... figured out that the "anniversary" line had an extra " ' " at the end. Deleted that. No more error, but the report comes up empty.

Sorry about the double quotes. The date also should use single quotes, but for some reason it won't return any results. Apparently this field isn't recognized as a date. We'll have to wait for the solution from the guys at OD.

I tried treating the field value as a text instead of as a date, and came up with this:

Select patient.Lname, patient.Fname, patient.email, patfield.fieldvalue as QDPDate
From patient, patfield
Where patient.patstatus = 0
AND patient.patnum = patfield.patnum
AND patfield.fieldname = 'QDPAnniversary'
AND patfield.fieldvalue Like '%2013'

OK, my suspicion is confirmed. I wrote you a query where we convert the text string in your custom QDPAnniversary field to a date type value and it should work. Please try this:

Select patient.Lname, patient.Fname, patient.email, patfield.fieldvalue as QDPAnniversary
From patient, patfield
Where patient.patstatus = 0
AND patient.patnum = patfield.patnum
AND patfield.fieldname = 'QDPAnniversary'
AND str_to_date(patfield.fieldvalue,'%m/%d/%Y') > '2013/01/01'

Jorgebon wrote:OK, my suspicion is confirmed. I wrote you a query where we convert the text string in your custom QDPAnniversary field to a date type value and it should work. Please try this:

Select patient.Lname, patient.Fname, patient.email, patfield.fieldvalue as QDPAnniversary
From patient, patfield
Where patient.patstatus = 0
AND patient.patnum = patfield.patnum
AND patfield.fieldname = 'QDPAnniversary'
AND str_to_date(patfield.fieldvalue,'%m/%d/%Y') > '2013/01/01'

It worked! Thanks! Very cool.
Is it possible to have the rows numbered on the left side?

Like this?

SET @pos=0;
SELECT @pos:=@pos+1 as Patient, patient.Lname, patient.Fname, patient.email, patfield.fieldvalue as QDPAnniversary
From patient, patfield
Where patient.patstatus = 0
AND patient.patnum = patfield.patnum
AND patfield.fieldname = 'QDPAnniversary'
AND str_to_date(patfield.fieldvalue,'%m/%d/%Y') > '2013/01/01'

Jorgebon wrote:Like this?

SET @pos=0;
SELECT @pos:=@pos+1 as Patient, patient.Lname, patient.Fname, patient.email, patfield.fieldvalue as QDPAnniversary
From patient, patfield
Where patient.patstatus = 0
AND patient.patnum = patfield.patnum
AND patfield.fieldname = 'QDPAnniversary'
AND str_to_date(patfield.fieldvalue,'%m/%d/%Y') > '2013/01/01'

You're the best!
Muchas gracias! Te lo agradezco!