I’ve seen several threads on calculating weekdays in a BAQ. Here is one way to do it, using jobhead.startdate and jobhead.duedate as an example.
Business Activity Query Designer, Calculated Field SQL Editor:
Data Type Integer
- (case when datepart(dw,JobHead.StartDate) = 1 then 1 else 0 end)
- (case when datepart(dw,JobHead.DueDate) = 7 then 1 else 0 end)
Line 1 calculates the date difference in days.
Line 2 counts the date difference in weeks, multiplying by two. This subtracts the Saturday and Sunday for each week.
Line 3 adjusts for the date range starting on a Sunday, subtracting 1 if yes
Line 4 adjusts for the date range ending on a Saturday, subtracting 1 if yes