I don't think that Dan's first example works as it counts rows in the table rather than the days between the dates.
In order to do this you need to know both the start day of the week and the end day of the week, so that you can adjust the business days accordingly . Like Dan, I played around with subtracting one date from another giving the number of days, and then dividing by 7 in order to giving the number of weeks. I toyed with both CEIL and FLOOR but couldn't get round the problem that you need to adjust for both start and end. When the CASE statement I had, needed to go down two levels, I decided it was getting too complicated to maintain easily.
I am therefore of the opinion that it's probably too complicated to do easily in straight SQL. I'm not saying it can't be done, just that whatever is produced would be so complicated as to be non-maintainable.
Page 387 of Graeme Birchall's cookbook http://mysite.verizon.net/Graeme_Birchall/cookbook/DB2V82CK.PDF
creates a user function as follows:
Business Day Calculation
The following function will calculate the number of business days (i.e. Monday to Friday) between to two dates:
CREATE FUNCTION business_days (lo_date DATE, hi_date DATE)
DECLARE bus_days INTEGER DEFAULT 0;
DECLARE cur_date DATE;
SET cur_date = lo_date;
WHILE cur_date < hi_date DO
IF DAYOFWEEK(cur_date) IN (2,3,4,5,6) THEN
SET bus_days = bus_days + 1;
SET cur_date = cur_date + 1 DAY;
Important – This example uses an “!” as the stmt delimiter.
Below is an example of the function in use:
WITH temp1 (ld, hd) AS
,DAYS(hd) - DAYS(ld) AS diff
,business_days(ld,hd) AS bdays
FROM temp1 t1;