Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

calculating Business daysHelpful Member!(2) 

luvrexx (Programmer) (OP)
16 May 06 14:33
Hi,
  Given a date range I need to calculate the no. of business days in it.Can any one help me with the sql query that i should use in QMF.

Thanks,
Kunal
Helpful Member!  ddiamond (Programmer)
16 May 06 14:49
Are you just excluding weekends, or are you also excluding holidays?
luvrexx (Programmer) (OP)
16 May 06 14:55
Hi ddiamond,

 I want to exclude the weekends only.

Kunal
ddiamond (Programmer)
16 May 06 14:56

CODE

SELECT count(*)
FROM myTable
WHERE dt BETWEEN StartDate AND EndDate
  AND DAYOFWEEK_ISO(dt)<6
  ;
ddiamond (Programmer)
16 May 06 14:57
Your start date in end date must be either actual dates, or strings in the format 'yyyy-mm-dd'.
ddiamond (Programmer)
16 May 06 15:24
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
ddiamond (Programmer)
16 May 06 16:06
never mind, my 2nd solution doesn't work in all cases.
luvrexx (Programmer) (OP)
16 May 06 16:39
Hi,

  what are the cases and what is the logic for taking
3 as the condition.......and of doing 2 the 2-1
then 2-1.Please reply
blom0344 (TechnicalUser)
17 May 06 2:12
"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
 

ddiamond (Programmer)
17 May 06 8:34
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.
Helpful Member!  MarcLodge (Programmer)
18 May 06 6:10
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 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)
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;

 
ddiamond (Programmer)
18 May 06 14:50
Good point Marc.  I believe there should be a simple mathematical formula for this, but I have not come up with one.
sathyarams (IS/IT--Management)
21 Sep 06 13:55
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 www.db2click.com
More DB2 questions answered at www.dbforums.com/f8 & http://www.idug.org/user/UserLogin.asp

sathyarams (IS/IT--Management)
21 Sep 06 13:56
BTW, I should have mentioned, on the mainframe, this solution will work only from v8

Sathyaram

For db2 resoruces visit www.db2click.com
More DB2 questions answered at www.dbforums.com/f8 & http://www.idug.org/user/UserLogin.asp

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close