Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
calculating Business days 2
- Thread starter luvrexx
- Start date
-
1
- #2
- Thread starter
- #3
alternatively:
select
case
when DAYOFWEEK_ISO(StartDate)<3
then days(enddate)-days(startdate)-floor((days(enddate)-days(startdate))/7)*2
when DAYOFWEEK_ISO(StartDate)=3
then days(enddate)-days(startdate)-floor((days(enddate)-days(startdate))/7)*2-1
when DAYOFWEEK_ISO(StartDate)>3
then days(enddate)-days(startdate)-floor((days(enddate)-days(startdate))/7)*2-2
end as BusDays
from myTable
select
case
when DAYOFWEEK_ISO(StartDate)<3
then days(enddate)-days(startdate)-floor((days(enddate)-days(startdate))/7)*2
when DAYOFWEEK_ISO(StartDate)=3
then days(enddate)-days(startdate)-floor((days(enddate)-days(startdate))/7)*2-1
when DAYOFWEEK_ISO(StartDate)>3
then days(enddate)-days(startdate)-floor((days(enddate)-days(startdate))/7)*2-2
end as BusDays
from myTable
- Thread starter
- #8
If startdate is either monday or tuesday then take the number of days between startdate and enddate and correct for the number of days that belong to the weekends in the period between start and enddate."
That is you should read the first part of the case statement.
You will probably need to extend the logic to get all cases covered. I'd say the first solution offered is pretty neat and simple!
Ties Blom
That is you should read the first part of the case statement.
You will probably need to extend the logic to get all cases covered. I'd say the first solution offered is pretty neat and simple!
Ties Blom
Ties is correct. The cases I listed above are correct, but I am missing some. When I saw that the number of special cases was growing, I abandoned that approach. I believe I have covered all of the special cases for date ranges of 7 days or more. I discovered you have an additional set of special cases when the date span is less than 7 days.
-
1
- #11
Hi all,
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 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)
RETURNS INTEGER
BEGIN ATOMIC
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;
END IF;
SET cur_date = cur_date + 1 DAY;
END WHILE;
RETURN bus_days;
END!
Important – This example uses an “!” as the stmt delimiter.
Below is an example of the function in use:
WITH temp1 (ld, hd) AS
(VALUES (DATE('2006-01-10'),DATE('2007-01-01'))
,(DATE('2007-01-01'),DATE('2007-01-01'))
,(DATE('2007-02-10'),DATE('2007-01-01')))
SELECT t1.*
,DAYS(hd) - DAYS(ld) AS diff
,business_days(ld,hd) AS bdays
FROM temp1 t1;
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 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)
RETURNS INTEGER
BEGIN ATOMIC
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;
END IF;
SET cur_date = cur_date + 1 DAY;
END WHILE;
RETURN bus_days;
END!
Important – This example uses an “!” as the stmt delimiter.
Below is an example of the function in use:
WITH temp1 (ld, hd) AS
(VALUES (DATE('2006-01-10'),DATE('2007-01-01'))
,(DATE('2007-01-01'),DATE('2007-01-01'))
,(DATE('2007-02-10'),DATE('2007-01-01')))
SELECT t1.*
,DAYS(hd) - DAYS(ld) AS diff
,business_days(ld,hd) AS bdays
FROM temp1 t1;
sathyarams
IS-IT--Management
Just in case you want SQL:
Assuming you want number of weekdays betwwn 1st Jan 2006 and 1st May 2006
with temp(day1) as
(
select date('2006-01-01') from sysibm.sysdummy1
union all
select day1+1 day from temp where day1<=date('2006-05-01')
)
select count(*) from temp
where dayofweek(day1) between 2 and 6
HTH
Sathyaram
For db2 resoruces visit More DB2 questions answered at &
Assuming you want number of weekdays betwwn 1st Jan 2006 and 1st May 2006
with temp(day1) as
(
select date('2006-01-01') from sysibm.sysdummy1
union all
select day1+1 day from temp where day1<=date('2006-05-01')
)
select count(*) from temp
where dayofweek(day1) between 2 and 6
HTH
Sathyaram
For db2 resoruces visit More DB2 questions answered at &
sathyarams
IS-IT--Management
BTW, I should have mentioned, on the mainframe, this solution will work only from v8
Sathyaram
For db2 resoruces visit More DB2 questions answered at &
Sathyaram
For db2 resoruces visit More DB2 questions answered at &
- Status