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.
How to write a query to filter data with end date
-
- Posts: 370
- Joined: Sat Apr 28, 2018 3:33 am
- Location: India
- Contact:
-
- Posts: 197
- Joined: Fri Jun 17, 2016 7:10 am
- Location: Brisbane Australia
- Contact:
Re: How to write a query to filter data with end date
FIND Patient WHERE EndDate > CURRENT_DATE
AWS Linux, Windows Server, AIM 8.4 & 8.6
-
- Posts: 370
- Joined: Sat Apr 28, 2018 3:33 am
- Location: India
- Contact:
Re: How to write a query to filter data with end date
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
-
- Posts: 2418
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: How to write a query to filter data with end date
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
OR
FIND Patient WHERE COUNT OutPatient WHERE (OutPatient IN Patient.ps_OutPatientRecords AND OutPatient.EndDate>CURRENT_DATE)>0
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
OR
FIND Patient WHERE COUNT OutPatient WHERE (OutPatient IN Patient.ps_OutPatientRecords AND OutPatient.EndDate>CURRENT_DATE)>0
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
-
- Posts: 370
- Joined: Sat Apr 28, 2018 3:33 am
- Location: India
- Contact:
Re: How to write a query to filter data with end date
very glad to know that joins work in Aware IM! thanks a lot.
-
- Posts: 2418
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: How to write a query to filter data with end date
If it ever needs more complexity then Aware IM can also incorporate Stored Procedures.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
-
- Posts: 370
- Joined: Sat Apr 28, 2018 3:33 am
- Location: India
- Contact:
Re: How to write a query to filter data with end date
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.
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.
- Attachments
-
- issue2.png (13.58 KiB) Viewed 16094 times
-
- issue1.png (30.85 KiB) Viewed 16094 times
Re: How to write a query to filter data with end date
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.
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.
Re: How to write a query to filter data with end date
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.
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.
Tom - V8.8 build 3137 - MySql / PostGres
-
- Posts: 370
- Joined: Sat Apr 28, 2018 3:33 am
- Location: India
- Contact:
Re: How to write a query to filter data with end date
Thanks for the good alternative approach!