I am looking to do a calculated filed in a BAQ that will show the dates of today, and the past tow days, not including weekends.
That is not easy… since a BAQ is simply a front end for SQL, you would need to use SQL commands… there are multiple articles about doing this in SQL but it starts getting complex, and possibly impossible since you cannot insert SQL Functions into a BAQ. DO a quick internet search for “SQL Work Days calculation” and you will find hundreds of articles and many solutions.
One trick that I have done is:
- I convince people that they really want to look at one WEEKS worth of work instead of 3 or 4 days…
- then I use “Today plus 7” or “Today minus 7” to make up my buckets… SInce 7 will always include a weekend, we can assume that there are only 5 work days. (unless there is a holiday, at which point this falls apart).
This will give you what you want I believe you’ll need two different calculated fields. One for the first case and one for the second. Assumes Sunday is Day 1
DECLARE @CurDate as DATETIME SET @CurDate = '8/4/2021'; SELECT CASE WHEN DATEPART(dw,@CurDate)=2 THEN DATEADD(day,-3,@CurDate) ELSE DATEADD(day,-1,@CurDate) END, -- One Day before CurDate CASE WHEN DATEPART(dw,@CurDate)=2 THEN DATEADD(day,-4,@CurDate) WHEN DATEPART(dw,@CurDate)=3 THEN DATEADD(day,-4,@CurDate) ELSE DATEADD(day,-2,@CurDate) END -- Two Days Before CurrDate