I have a report that runs daily, M-F. From Tuesday-Friday, I only need to pull the previous days data. On Monday, I need to pull the weekend data, 3 days(Sat, Sun, Mon morning). I can configure the report to pull one date range or the other. However, I want to report to automatically determine the date range.
The following statement works every time by itself. I can change the -1 to -3 or whatever at will.
[SQL1].[DocTimestamp]>_add_days(current_date,-1)
But when I try to put that inside an IF statement, it fails.
IF (_day_of_week(current_date,1)=1) THEN ([SQL1].[DocTimestamp]>_add_days(current_date,-3))
My understanding is the above statement says "If the current day is Monday, then grab the last 3 days of info"
Effectively, I want to report to determine if it's a weekday or a weekend, and run the date range accordingly.
Help.
Instead of using the IF statement, try using this in the filter:
( _day_of_week(current_date,1) = 1 AND ([SQL1].[DocTimestamp] > _add_days(current_date,-3) )
OR
( _day_of_week(current_date,1) <> 1 AND ([SQL1].[DocTimestamp] > _add_days(current_date,-1) )
It is always safer to use <= also in the Date range filter limiting the data to current date. In case there is future data, that will be avoided.
That works great!
I had to modify the parentheses, though, to get it to work. The corrected version is:
( _day_of_week(current_date,1) = 1 AND ([SQL1].[DocTimestamp] > _add_days(current_date,-3))) OR
( _day_of_week(current_date,1) <> 1 AND ([SQL1].[DocTimestamp] > _add_days(current_date,-1)))
I then ultimately wanted to catch Sunday in there also, so I added and extra check:
((( _day_of_week(current_date,1) = 1) OR ( _day_of_week(current_date,1) = 7)) AND ([SQL1].[DocTimestamp] > _add_days(current_date,-3))) OR
((( _day_of_week(current_date,1) <> 1) OR ( _day_of_week(current_date,1) <> 7)) AND ([SQL1].[DocTimestamp] > _add_days(current_date,-1)))
Thank you, sir! One more tool in the arsenal against Chaos!
Might want to use code blocks to show these better.
((( _day_of_week(current_date,1) = 1) OR ( _day_of_week(current_date,1) = 7)) AND ([SQL1].[DocTimestamp] > _add_days(current_date,-3))) OR
((( _day_of_week(current_date,1) <> 1) OR ( _day_of_week(current_date,1) <> 7)) AND ([SQL1].[DocTimestamp] > _add_days(current_date,-1)))