How to write a query to filter data with end date

swiftinitpvtltd

How to write a query to filter data with end date greater than today's date in aware IM query section?
Basically I want patients thats still admitted(meaning dismissal date greater than today's date.

UnionSystems

FIND Patient WHERE EndDate > CURRENT_DATE

swiftinitpvtltd

Thank you! Can I use joins meaning 2 tables? I have outpatient table where this date exists but all actual patient data exist in patient table(name, photo). So I want to show patient table(photo, name, mobile, email) where outpatient table end date >current_date

customaware

Not sure of the relationship you have between the Patient Business Object and the OutPatient Business Object

However I would assume each OutPatient record has a single Patient assigned and a Patient can be assigned to many OutPatient records.

Hence you probably have a Peer Many (pm_OutPatientRecords Attribute of type OutPatient) in Patient and the reverse Peer Single (ps_Patient Attribute of type Patient) in OutPatient.

Assuming that is correct...something like this should work....

FIND Patient WHERE COUNT OutPatient WHERE (OutPatient.ps_Patient = Patient AND OutPatient.EndDate>CURRENT_DATE)>0

FIND Patient WHERE COUNT OutPatient WHERE (OutPatient IN Patient.ps_OutPatientRecords AND OutPatient.EndDate>CURRENT_DATE)>0

swiftinitpvtltd

very glad to know that joins work in Aware IM! thanks a lot.

customaware

If it ever needs more complexity then Aware IM can also incorporate Stored Procedures.

swiftinitpvtltd

Thanks. I tried first query and it did not give me error but no data returned. In "patient" I have first name, last name and ID. When I built the relationship does it store ID as FK into "InPatient" table from "Patient"? I will try few more things.

2 Tables-
Patient- FirstName, LastName, ID
InPatient- AdmissionDate, DismissalDate, Patient(relationship)

Now based on dismissalDate I want patient data with dismissaldate>current_date.
Will try to see mysql data as well. Also the query returns all dismissal dates with current dates as values in quick preview but actual data contains different dates in UI and mysql. I am trying to figure out why thats happening.

idpSteve

Mark's query should give you what you need. I think you're finding InPatient rather than Patient since you're getting InPatient attributes in your results grid. You need to find the Patient objects if you want to show the Patient information, or you need to use shortcuts.

I'd suggest:

FIND Patient WHERE ( EXISTS InPatient WHERE (InPatient.DismissalDate > CURRENT_DATE AND InPatient.Patient=Patient ) )

Then under display options you can select which attributes of Patient you want to see. You can tick as many attributes as you want to show in the grid.

tford

I agree that Mark's approach should definitely work.

Another alternative which might be helpful in the long run is to have a Y/N attribute in the Patient BO: Patient.Admitted_YN (In my naming conventions I always have a _YN suffix on Yes/No attributes.

I could envision Admitted_YN be used in at least three ways:
1) In your query, you would simply find Patients with Admitted_YN=Yes
2) In a pick list query of patients to admit, Patients with Admitted_YN=No
3) Admitted_YN would be easily available to show on queries and forms

Admitted_YN would be easy to maintain by setting the value appropriately during processes you have to record admissions and dismissals.

swiftinitpvtltd

Thanks for the good alternative approach!