Query Builder Filter date field for previous Week

[cowannbell]

I have a data source that is very large so I try to build a query using the query builder to filter down to just bring in records I want to work with.

I have a report that I'm about to schedule to run weekly but I need to filter on an effective date field for the prior week.  It will run on a Monday and I want the prior week's data.

How using query builder can I do that so it always pulls the prior weeks records?

Thanks,

Carol

[RolandSchubert]

Hi @cowannbell ,

you can solve your problem using a Formula tool to find start and end of last week and a Dynamic Input tool to execute the SQL query.

First step is to create the date filter, Monday and Sunday of last week a needed. The find Monday, you can use the formula:

DateTimeAdd(DateTimeToday(), - (ToNumber(DateTimeFormat(DateTimeToday(), '%u')) + 6), 'days')

DateTimeToday() returns the current date. DateTimeFormat([...], '%u') finds the weekday number (1 for Monday). Using DateTimeAdd you calculate the last Monday (if today is Monday, 1+6 = 7 days are subtracted from current date, result is last weeks Monday, if today is Wednesday, 3+6 is subtracted.

To find the last day of last week, simply add 6 days to your start date (again DateTimeAdd can help).

Now you have to pass this to your SQL statement. 

Use a Dynamic Input tool and create your query using the query builder.

If you switch to SQL Editor now, you can add a WHERE clause restricting the query by setting a start and an end date:

Now you can pass your calculated dates to the SQL statement (both start and end date).

You should get only data for prior week now.

Hope this is helpful, let me know if it works for you.

Best,

Roland

[cowannbell]

I was a little confused by this but I did find the solution that worked for me.

It is found under the discussions titled Filter data by last week.  Not sure if this is what you meant or not.

Thanks for your reply.