You want to build a query that will automatically calculate contacts on a daily basis who registered for a product within a specified time period, without having to adjust the query daily.

Capture date periods for a rolling query, using queries

In the newest version of the query builder, you can filter criteria by months or days. Filtering by months can provide better results as it takes leap years into account.

In this example, you look for contacts who registered for a product greater than 1 year ago but less than 2 years ago. This example uses the RegDate database field.

Note: The operators in the new query builder are different from the operators in classic queries. Because you want this query to be a rolling query where you do not have to change the start and end date each day manually, you can not use the Is Between operator. The new query builder uses fixed dates only. As a result, use the is within the last range of operator and specify the range as 12 to 24 months.

The query rule: WHEN RegDate is within the last range of 12 to 24 months

Here's how:

1. Login to the Acoustic Campaign and go to Data > Queries > Create.
2. Complete the Save Query As dialog box:
3. Choose Database to Query - Select the database you want to query
4. Save Query As - Give the query a descriptive name.
5. Location - Save it in a Shared or a Private folder.
6. Type - Select Query.
7. Click OK to open the query builder.
8. Enter the query criteria:
9. Data Field - Select a database field.
10. Operator - Select is within the last range of.
11. Value - Enter 2 and 24 and select months.
12. Click Add and save your query.

When you build a query, you can verify the contacts who meet the query's criteria. Open your query and check the contacts within by clicking the Search tab.

Then, you can perform spot checks by reviewing the data or if there are a lot of contacts who meet the criteria, use the Search Where filters to look for contacts who should not meet the query criteria. You should not have contacts that meet your spot check criteria. If you do, then review your query logic.

Note: Before you click the Search button, slide the bottom scroll bar slightly over to the right. When the search is complete, it will bounce the scroll bar back over to the left.

Capture date periods for a rolling query, using classic queries

This example, looks for contacts who registered for a product greater than 1 year ago but less than 2 years ago.

In the classic query builder, define the number of days in your query. This example uses the RegDate database field.

The query rule is: [RegDate] is between [Current Date]-730 and [Current Date]-365

Because you are working with a number of days, this query does not take leap years into consideration.

Here's how:

1. Login to the Acoustic Campaign and go to Create under Queries.
2. Complete the Save Query As dialog box.
3. Choose Database to Query - Select the database you want to query
4. Save Query As - Give the query a descriptive name.
5. Location - Save it in a Shared or a Private folder.
6. Type - Select Classic Query.
7. Click OK to open the query builder.
8. Enter the query criteria.
9. Criteria - Select a database field.
10. Operator - Select is between.
11. Begin Value - Choose the Current Date option to enter each time period.

Specify how far back in days you need to go to set the start period by entering the latest time period. In this example, the start date is 2 years ago or 730 days (365 days *2). Enter the negative value for 730, that is '- 730'.

1. In the Value field, choose the Current Date option to enter the time period again and then type the negative value for 365 (where 1 year is 365 days) '-365'.
2. Click Add and save your query.

When you build a query, you can verify the contacts who meet the query's criteria. Open your query and check the contacts by clicking the Search tab.

Then, you can perform spot checks by reviewing the data or if there are a lot of contacts who meet the criteria, use the Search Where filters to look for contacts who should not meet the query criteria. You should not have contacts that meet your spot check criteria. If you do, then review your query logic.

Note: Before you click the Search button, slide the bottom scroll bar slightly over to the right. When the search is complete, it will bounce the scroll bar back over to the left.