Prior week(s) of data regardless of current day of week within SQL query

[MNewt9]

Hello All,

I've seen similar questions/answers, but all referencing using a filter or formula tool.  I am looking to generate the above within the initial SQL query of the In-DB query.

Our weeks are Sunday through Saturday based on pickup date.  Moving to server and looking to schedule the query to run each Monday morning.  However, there will be instances we'd need to manually kick off the query (data refresh/ad hoc requests).  I want to be able to use a single WF regardless of it being scheduled on Monday for the prior Sunday to Saturday OR a manual 'one off' on a Wednesday for example.

I'd believe it'd require a nested IF statement.  Any thoughts?

This is what I currently have to pull the prior week of data, but it clearly would only work on Mondays to generate the prior Sunday to Saturday.

[BenMoss]

I've had a look over stackoverflow and the following syntax allows you to get the monday of the current week...

DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

Remember syntax is database specific but this worked for my on MS SQL. You sould replace your CURRENT_DATE value in your query with the above.

Ben

[MNewt9]

Thanks Ben, so your thought is to get to the Monday of the current week and then apply the logic I had listed to generate the prior week data set?

That sounds easier than doing a nested IF based on if today is Tuesday or Wednesday, etc.

[BenMoss]

Yep, that's exactly how I'd approach it, will make the statement a lot clearner.

Ben