Query Wizard Operators¶
The Query Wizard mode of the Daybreak Data Builder allows users with no knowledge of the SQL language to build queries against the datamart. Through the guided user interface, users can select fields to query on and specify a query using a basic, visual syntax premised on the concept of applying certain limiting criteria called operations to control the results returned when the query is run.
Each limiting operation has three parts: - the datamart field whose data the operation will be run against - the operator or logical function to be applied to that data - the operand or data supplied as an argument to the operator to decide if a datamart record should be returned.
Because operators are the only non-user supplied part of the limiting operations, this guide describes the logic of each operator to enable uses to have a better idea about how to build queries. Because the Query Wizard effectively serves as an abstraction of certain query functions in the SQL language, knowledge of SQL or database queries in general may be helpful. Additionally, although the operators provided by the Query Wizard will usually allow users to construct the most commonly used queries, there are some instances where the Query Wizard operators may not be sufficient to construct certain types of queries. For these instances, users build the basic framework of their query in the Wizard and then switch to SQL mode to fine-tune their desired WHERE statements.
equals¶
Applies to: numbers, text Operand: User input field
The equals operator performs simple comparison between the source field and the operand, in this case, a user input field, and returns all results matching that input.
For example, if the user selects the City field and types "South Bend" into the user input field, the query will return all records where the value of City is equal to South Bend.
Note that the operand must be an exact match for the target datamart field as Query Wizard does not support the SQL LIKE operator for wildcard searching. If you would like to perform a wildcard search, try using the SQL Builder tool.
Additionally, the equals operator will also match with records where the input field is equal to null. If you would like to exclude these records, try combining the operator with an additional is not null expression.
not equals¶
Applies to: numbers, text
Operand: User input field
The not equals operator performs simple comparison between the source field and the operand, in this case, a user input field, and returns all results not matching that input.
For example, if the user selects the City field and types "Chicago" into the user input field, the query will return all records where the value of City is not equal to Chicago.
Note that the operand must be an exact match for the target datamart field as Query Wizard. For example, a not equals match on Chicago will exclude records where City is equal to Chicago, but not where the city is something similar, like "East Chicago." If you would like to perform a wildcard search for these cases, try using the SQL Builder tool.
is one of¶
Applies to: enumerated fields
Operand: User input from a drop-down
The is one of operator performs a simple equality check on one of a set of selected values where the field in question contains enumerated values. Many Daybreak datamart fields have one of a limited set of possible values (an enumeration) such as ProductType. This field can only have one of a set of possible values such as "Mortgage", "Checking," "Savings," etc. The is one of operator allows a user to select one or more of such possible values as the condition for that query.
For example, if a user wants to select mortgage or HELOC accounts, they can now use the is one of operator on the ProductType field and check the "Mortgage" and "HELOC" boxes from the operands field. The resulting query will return only records whose ProductType field equals "Mortgage" or "HELOC."
is not one of¶
Applies to: enumerated fields
Operand: User input from a drop-down
The is not one of operator performs a simple inequality check on one of a set of selected values where the field in question contains enumerated values. Many Daybreak datamart fields have one of a limited set of possible values (an enumeration) such as ProductType. This field can only have one of a set of possible values such as "Mortgage", "Checking," "Savings," etc. The is not one of operator allows a user to select one or more of such possible values to exclude as the condition for that query.
For example, if a user wants to select mortgage or HELOC accounts, they can now use the is not one of operator on the ProductType field and check the "Mortgage" and "HELOC" boxes from the operands field. The resulting query will return only records whose ProductType field does not equal "Mortgage" or "HELOC." (i.e. all other account types excluding mortgages and home equity loans.)
is null¶
Applies to: numbers, strings, dates
Operand: None
The is null operator returns a list of all records where the target field is equal to null.
Fields equal to null are often the result of incomplete data in the source data system.
is not null¶
Applies to: numbers, strings, dates
Operand: None
The is not null operator returns records where the target field is not equal to null.
Fields equal to null are often the result of incomplete data in the source data system.
The is not null operator can be a useful expression to add alongside other expressions since many comparison operators will return results for fields that are null. The is not null operator can be added to exclude incomplete records from your query results.
greater than¶
Applies to: numbers
Operand: User input field
The greater than operator returns records where the source field value is greater than the amount specified in the user input field.
For example, if a user selects the Customer_age source field and types "17" into the input field, this will return records for all customers aged 18 or older.
Note that the greater than operator compares numbers exclusively, so if a user types "18" into the input field, it will only return customers aged 19 or older. If this is not the desired behavior, use the greater than or equal to operator to perform inclusive comparison.
greater than or equal to¶
Applies to: numbers
Operand: User input field
The greater than or equal to operator returns records where the source field value is less than the amount specified in the user input field.
For example, if a user selects the CheckingAccountBalance source field and types "2000" into the input field, this will return records for all customers with $2,000 or more in their account.
Note that the greater than or equal to operator compares numbers inclusively, which is different from the exclusive comparison of the greater than operator.
less than¶
Applies to: numbers
Operand: User input field
The less than operator returns records where the source field value is less than the amount specified in the user input field.
For example, if a user selects the Customer_age source field and types "65" into the input field, this will return records for all customers aged 64 or younger.
Note that the less than operator compares numbers exclusively, so if a user types "66" into the input field, it will only return customers aged 65 or higher. If this is not the desired behavior, use the less than or equal to operator to perform inclusive comparison.
less than or equal to¶
Applies to: numbers
Operand: User input field
The greater than or equal to operator returns records where the source field value is less than the amount specified in the user input field.
For example, if a user selects the CheckingAccountBalance source field and types "2000" into the input field, this will return records for all customers with $2,000 or more in their account.
Note that the greater than or equal to operator compares numbers inclusively, which is different from the exclusive comparison of the greater than operator.
in the range¶
Applies to: dates
Operand: Preset or custom range expression
The in the range operator returns records where the date contained in the source field falls within the relative range expression used as the operand. Range expressions can be chosen from a list of predefined ranges or constructed using a sub-expression constructed specially for the current expression.
Note that all range expressions are relative to the date on which the query is run. If yesterday is chosen as the desired range operand, the results it returns will vary from day to day since the date value of yesterday will always be one day before the current date when the query is run. For queries whose dates are static (such as "the fourth quarter of 2019") see the between dates operator.
Predefined Ranges¶
The data builder user interface includes a number of predefined ranges for relative dates in the past, present, and future. The following table summarizes lists these dates along with a description of the logic used to calculate the dates and an example of the range as it would be generated if the query were run on June 15th, 2021.
| Range | Calculated Range from Tuesday, June 15th 2021 | Description |
|---|---|---|
| Yesterday | June 14, 2021 | the day before today |
| Last week | Sunday, June 6 through Saturday June 12 2021 | the Sunday-Saturday of the last week, i.e. 7-13 days ago till 1-6 days ago depending on the current day of the Week |
| Last month | May 1 through May 31 2021 | The 1st through the 28th-31st of the past month |
| Last quarter | January 1 through March 31 2021 | The 1st of the January, April, July, or October of the previous quarter through the 30th-31st of the March, June, September, or December of the previous quarter |
| Last year | January 1 through December 31 2021 | January 1st-December 31st of the previous calendar year |
| The last seven days | June 8 through June 15 2021 | seven days ago till today |
| The last thirty days | May 16 through June 15 2021 | thirty days ago till today |
| Today | June 15 | The current day |
| This week | Sunday, June 13 through Saturday, June 19 2021 | The Sunday-Saturday of the present week |
| This month | June 1 through June 30 2021 | The 1st through the 28th-31st of the present month |
| This quarter | April 1 through June 30 2021 | The 1st of the January, April, July, or October of the current quarter through the 30th-31st of the March, June, September, or December of the current quarter |
| This year | January 1 through December 31 2021 | January 1st-December 31st of the present calendar year |
| Tomorrow | January 16 | The day after tomorrow |
| Next week | Sunday, June 20 through Saturday, June 26 2021 | The Sunday-Saturday of the coming week |
| The next two weeks | Sunday, June 20th through Saturday, July 3 2021 | The Sunday of the coming week through two Saturdays from now |
| The next month | July 1 through July 31 2021 | The 1st through the 28th-31st of the coming month |
| The next quarter | July 1 through September 30 2021 | The 1st of the January, April, July, or October of the coming quarter through the 30th-31st of the March, June, September, or December of the coming quarter |
Special Ranges¶
The data builder user interface allows special ranges to be defined using a three part expression syntax just like the Query Wizard operators themselves. These in the range sub-expressions allow users to create custom date ranges by specifying a span of time (a day, a week, etc.), an interval (days, weeks, months), and a multiple (integer) to multiply the interval by.
For example, to create an expression for the range of dates in the quarter that occurred two years ago, the user could select the span of a quarter to indicate the span of time desired, the interval of years ago to specify the interval of time to apply to the desired span, and the multiple of two to indicate that the user desires the quarter that occurred two (or three or five, etc.) years in the past.
In cases where the interval (i.e. years) is larger than the span of time requested (e.g. days, months), it is not possible to clearly indicate the desired dates, so special ranges only allow certain intervals to be specified for certain spans of time. The following table summarizes this logic:
| Day | Week | Month | Quarter | Year | |
|---|---|---|---|---|---|
| Day(s) ago/from now | x | x | x | x | x |
| Week(s) ago/from now | x | x | x | x | |
| Month(s) ago/from now | x | x | x | ||
| Quarter(s) ago/from now | x | x | |||
| Year(s) ago/from now | x |
between dates¶
Applies to: dates Operand: Two user supplied dates
The between dates operator returns records where the date contained in the source field falls between the two dates specified as operands.
For example, if a user wants a list of customers who joined between April 20 and May 20 of 2020, they can use a between dates operation on the DateJoined field with the supplied values of 04-20-2020 and 05-20-2020.
Note that this operator works inclusively, so a between dates query with the dates of 05-21-2017 and 10-15-2017 will return records with dates on May 21, 2017 (05-21-2017) and October 15, 2017 (10-15-2017).
on date¶
Applies to: dates
Operand: One user supplied date
The on date operator returns records where the date contained in the source field matches that of the operand supplied.
For example, if a user wants a list of accounts opened on a certain day, they can use a on date operation on the OpenDate field of the Accounts table and supply the desired date as the operand.
not on date¶
Applies to: dates
Operand: One user supplied date
The not on date operator returns records where the date contained in the source field is not the same as that of the operand supplied. This operator works in an opposite fashion to the on date operator.
For example, if a user wants a list of accounts minus those opened on a certain day, they can use a not on date operation on the OpenDate field of the Accounts table and supply the desired date as the operand.
after date¶
Applies to: dates
Operand: One user supplied date
The after date operator returns records where the date contained in the source field is after that of the operand supplied.
For example, if a user wants a list of all accounts opened after a certain day, they can use a after date operation on the OpenDate field of the Accounts table and supply the desired date as the operand.
Note that the after date operator is exclusive and will not return results occurring on the date supplied as an operand. So for example, if a user wants a year-to-date query, they should supply December 31st, and not January 1st as the operand, since the latter will only return records occurring on or after January 2nd. For a similar operator working on an inclusive basis, see the on or after date operator.
on or after date¶
Applies to: dates
The on or after date operator returns records where the date contained in the source field is on or after that of the operand supplied.
For example, if a user wants a list of all accounts opened on or after a certain day, they can use a on or after date operation on the OpenDate field of the Accounts table and supply the desired date as the operand.
Note that the after date operator is inclusive and will return results occurring on the date supplied as an operand. So for example, if a user wants a year-to-date query, they can supply January 1st as the operand to return the list of records occurring on or after January 1st.
before date¶
Applies to: dates
Operand: One user supplied date
The before date operator returns records where the date contained in the source field is before that of the operand supplied.
For example, if a user wants a list of all accounts opened before a certain day, they can use a before date operation on the OpenDate field of the Accounts table and supply the desired date as the operand.
Note that the before date operator is exclusive and will not return results occurring on the date supplied as an operand. So for example, if a user wants a list of accounts opened before the end of January, they should supply February 1st, and not January 31st as the operand, since the former will only return records occurring on or after January 30th, and not any on January 31st. For a similar operator working on an inclusive basis, see the on or before date operator.
on or before date¶
Applies to: dates
The on or before date operator returns records where the date contained in the source field is on or before that of the operand supplied.
For example, if a user wants a list of all accounts opened before the end of last month, they can use an on or before date operation on the OpenDate field of the Accounts table and supply the last day of the previous month as the operand.
Note that the on or before date operator is inclusive and will return results occurring on the date supplied as an operand. So for example, if a user wants a list of all accounts opened before the beginning of this year, they can use a on or before date operation on the OpenDate field of the Accounts table and supply December 31st of the past year as the operand.
in month¶
Applies to: dates Operand: month name.
The in month operator returns records where the date in the source field is within the month selected from the drop down, regardless of the year.
For example, if a user wants a list of customers or members who have birthdays in a particular month, they can use the in month operation on the DateofBirth field from the Customer table and select the desired month.
in quarter¶
Applies to: dates Operand: calendar quarter number (1,2,3,4).
The in quarter operator returns records where the month portion of the date in the source field is within the calendar quarter selected for the operand. Note that the quarters are presented as 1, 2, 3, and 4 within the operand drop down.
For example, if a user wants a list of accounts that opened during the holiday season (during the fourth quarter), they can use the in quarter operator for the OpenDate field in the Account table and select four (4) as the operand. This will provide accounts opened October, November, and December regardless of year.
Note that this operator may be best used in conjunctions with another date operator to limit the window of time.
on day¶
Applies to: dates Operand: user input field for day of month.
The on day operator returns records where the numeric day value in the source field is the same as the operand supplied, regardless of the month or year in the source value.
For example, if a user wants a list of expected loan pay offs that fall on the 5th of the month, they can use the on day operation for the ExpectedPayoffDate field in the Loans table. Add the ExpectedPayoffDate field with the on or after date operator to limit the list to a specific year.
Note that this operator maybe best used in conjunctions with another date operator to limit the window of time.
on weekday¶
Applies to: dates Operand: day of the week (Sunday – Saturday).
The on weekday operator returns records where the date in the source field was on the day of the week selected from the operand drop down.
For example, if a user wants to see customers whose last interaction was on a Friday, they can use the on weekday operator for the LastInteractionDate on the Customer table and select Friday for the operand.