Common Extended Query Filters

 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))) <= 6ANDdatediff(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))) <= 11ANDdatediff(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))) <= 13ANDdatediff(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) = 3ANDlast_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) >=20ANDday(dob) <= 27

 Reactivation

Patients who last visited this Month 24 months (2 years) ago:

last_visit >= DATEADD (month, -24 , Now())ANDlast_visit <= DATEADD (month, -23 , Now())

Patients who last visited this Month 12 months (1 year) ago:

last_visit >= DATEADD (month, -12 , Now())ANDlast_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())ANDlast_visit <= DATEADD (month, -23 , Now())ANDAge >= 18ANDAge <= 24

Patients who last visited this Month 24 months (2 years) ago with a health fund:

last_visit >= DATEADD (month, -24 , Now())ANDlast_visit <= DATEADD (month, -23 , Now())ANDinsurance_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'ANDdate_treatment_item >= DATEADD (month, -24 , Now())ANDdate_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())andlast_visit < DATEADD (month, -18 , Now())