Extended Queries allow for more complex filters to be used compared to Simple Queries. The following filters can be copied and pasted to Extended Queries where relevant.
|
Birthdays |
Patients with a DOB this Month | MONTH (dob) = month (today()) |
|
|
Patients with a DOB Today | MONTH (dob) = month (today()) AND DAY (dob) = day (today()) |
|
|
Patients with a DOB Yesterday | datediff (day, (today()), YMD(Year(today()), month(dob), day(dob))) =-1 |
|
|
Patients with a DOB the Day Before Yesterday | datediff (day, (today()), YMD(Year(today()), month(dob), day(dob))) =-2 |
|
|
Patients with DOB within the next Week (including today) | datediff (day, (today()), Ymd(Year(today()), month(dob), day(dob))) <= 6 AND datediff(day, (today()), Ymd(Year(today()), month(dob), day(dob))) >=0 |
|
|
Patients with DOB within the Next Calendar Week from Thursday (ie: Mon-Sun) | datediff (day, (today()), Ymd(Year(today()), month(dob), day(dob))) <= 11 AND datediff(day, (today()), Ymd(Year(today()), month(dob), day(dob))) >= 4 Note: This would vary depending on the day of the week. For example from Tuesday:datediff (day, (today()), Ymd(Year(today()), month(dob), day(dob))) <= 13 AND datediff(day, (today()), Ymd(Year(today()), month(dob), day(dob))) >= 6 |
|
|
Patients whose DOB is in a particular month | month(dob) = 5 (month number)This will return patients with a birthday in May. Note: Replace “5” with any month required. |
|
|
Patients who have a birthday in March and have visited the practice since 01/01/2019 | month(dob) = 3 AND last_visit >= Date('2019-01-01')Note: Replace “3” with any month required and insert start date as required. |
|
|
Patients whose DOB is between 20/08 and 27/08 | month(dob) = 8 and day(dob) >=20 AND day(dob) <= 27 |
|
Reactivation |
Patients who last visited this Month 24 months (2 years) ago: | last_visit >= DATEADD (month, -24 , Now()) AND last_visit <= DATEADD (month, -23 , Now()) |
|
|
Patients who last visited this Month 12 months (1 year) ago: | last_visit >= DATEADD (month, -12 , Now()) AND last_visit <= DATEADD (month, -11 , Now()) |
|
|
Patients aged 18-24 who last visited This Month 24 months (2 years) ago: | last_visit >= DATEADD (month, -24 , Now()) AND last_visit <= DATEADD (month, -23 , Now()) AND Age >= 18 AND Age <= 24 |
|
|
Patients who last visited this Month 24 months (2 years) ago with a health fund: | last_visit >= DATEADD (month, -24 , Now()) AND last_visit <= DATEADD (month, -23 , Now()) AND insurance_level = 'BUPA'Note: ‘BUPA’ can be replaced with any health fund. This relates to the insurance plan code. |
|
|
Patients who had item #011 performed this Month 24 months (2 years) ago: | treatment_item_name = '011' AND date_treatment_item >= DATEADD (month, -24 , Now()) AND date_treatment_item <= DATEADD (month, -23 , Now()) |
|
|
Patients who had item #011 OR item #013 performed this Month 24 months (2 years) ago: | date_treatment_item >= DATEADD (month, -24 , Now()) ANDdate_treatment_item <= DATEADD (month, -23 , Now()) ANDtreatment_item_name = '011' OR treatment_item_name = '013'Note: Any item number can be used instead of 011 and 013. Note the item numbers with the OR clause is listed last. This will return any patient that had either one of these items performed within the date range specified. |
| Patients who had treatment performed on a Monday: | DayName(date_treatment_item) = 'Monday'Note: Replace with any day as required. | |
| Patients who had item #011 performed on a Monday: | DayName(date_treatment_item) = 'Monday'ANDtreatment_item_name = '011' | |
|
Patients who have not been seen since 19 months ago. |
last_visit > DATEADD (month, -19 , Now())
and
last_visit < DATEADD (month, -18 , Now())
|
Comments
Article is closed for comments.