Need help writing User Query

[mikebarrdds]

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

[Jorgebon]

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"

[mikebarrdds]

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:

[mikebarrdds]

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

[Jorgebon]

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.

[Jorgebon]

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'

[Jorgebon]

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'

[mikebarrdds]

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?

[Jorgebon]

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'

[mikebarrdds]

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!