How to get the WORKING day diff in db2 without saturdays and sundays?

Question

I have a table with some dates intervals like this:

|id|group|date_start|date_end|

and I would like to make a view that looks like:

|id|working_days_diff|

and

|id|group|working_days_diff|

How may I do something like this? thanks

I was thinking about a custom function that will loop for each day in the difference and sum if DAYOFWEEK is not saturday and sunday.. but I don't know how to make...

The algorithm should be similar to the top answer for this question, which asks how to achieve the same using T-SQL. No need for loops. http://stackoverflow.com/questions/252519/count-work-days-between-two-dates-in-t-sql — Ian Nelson, Nov 15 '11 at 11:17
DATEDIFF doesnt exists in db2... you have to make days(date1)-days(date2) and then I can't use that answer.. — Totty.js, Nov 15 '11 at 11:30

score 3 · Answer 1 · edited May 23 '17 at 11:52

3

Taken from Count work days between two dates

(days(PAID_DATE) - days(SUBMISSION_DATE) + 1) -
((52 * (year(PAID_DATE) - year(SUBMISSION_DATE)) + week(PAID_DATE) - week(SUBMISSION_DATE)) * 2) - 
(case when dayofweek(SUBMISSION_DATE) = 1 then 1 else 0 end) -
(case when dayofweek(PAID_DATE) = 7 then 1 else 0 end)
as SUBMISSION_TO_PAID

edited May 23 '17 at 11:52

Community

1
1

answered Jan 27 '15 at 06:20

Irawan Soetomo

1,315
14
35

score 1 · Answer 2 · answered Sep 19 '13 at 08:27

select 
TIMESTAMPDIFF(32,CHAR(
    TIMESTAMP(
        (CHAR(date('2013-09-19') - (DAYOFWEEK_ISO('2013-09-19')-1) DAYS ))||'-00.00.00.000000'
    ) 
    - TIMESTAMP(
        (CHAR(date('2013-09-13') - (DAYOFWEEK_ISO('2013-09-13')-1) DAYS )||'-00.00.00.000000')
    )
))*5 + DAYOFWEEK_ISO('2013-09-19')- DAYOFWEEK_ISO('2013-09-13')

score 1 · Answer 3 · answered Nov 15 '11 at 11:49

1

db2 has a function called: TIMESTAMPDIFF and not DATEDIFF. Use it to find the number of weeks between the dates, then multiply with 5 (weekdays).

 TIMESTAMPDIFF(32,CHAR(TIMESTAMP('2001-09-29-11.25.42.123456') - TIMESTAMP('2001-09-26-12.07.58.123456')))

NB: the function is an estimate

answered Nov 15 '11 at 11:49

Eystein Bye

5,016
2
20
18

that is not useful.. the error is to big to consider doing this way. we have difference of days of 1, 2, 10, 15, 30 – Totty.js Nov 15 '11 at 12:10
If you cant use estimate then I think your function is the only way. Why does it not work? – Eystein Bye Nov 15 '11 at 12:40
now it works. I don't know nothing about db2 ibm programming so I just looked at other codes to figure out how to do.. finally I've made it... – Totty.js Nov 15 '11 at 13:44

score 1 · Accepted Answer · answered Nov 15 '11 at 12:15

CREATE FUNCTION stkqry.WORKING_DAY_DIFF(DATE_START date, DATE_END date)          
RETURNS INTEGER                                            
LANGUAGE SQL
BEGIN
    DECLARE WORKING_DAYS INTEGER DEFAULT 0;
    DECLARE DATE_COUNTER DATE;
    SET DATE_COUNTER = DATE_START;
    WHILE DAYS(DATE_COUNTER) < DAYS(DATE_END) DO
        SET DATE_COUNTER = DATE(days(DATE_COUNTER)+1);

        CASE WHEN DAYOFWEEK_ISO(DATE_COUNTER) = 6 THEN
            SET WORKING_DAYS = WORKING_DAYS;
        WHEN DAYOFWEEK_ISO(DATE_COUNTER) = 7 THEN
            SET WORKING_DAYS = WORKING_DAYS;
        ELSE
            SET WORKING_DAYS = WORKING_DAYS + 1;
        END CASE;
    END WHILE;
    RETURN WORKING_DAYS;
END

While this may work, look at @Stuart's answer (use a calendar file) - this is of course to include/exclude things like Christmas (and any company-selected holidays). You will also likely get better performance (especially because DB2 can `and`/`or` indicies...) — Clockwork-Muse, Nov 15 '11 at 17:15

score 1 · Answer 5 · edited Nov 13 '18 at 23:14

1

A calendar table makes this sort of query easy (here's a SQL Server specific link):

http://web.archive.org/web/20070611150639/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

However, you can build these for any database; below is a link suggesting how to do it for db2:

http://bytes.com/topic/db2/answers/181183-calculating-business-days

edited Nov 13 '18 at 23:14

Community

1
1

answered Nov 15 '11 at 14:59

Stuart Ainsworth

12,792
41
46

score 0 · Answer 6 · edited Sep 22 '20 at 07:10

0

If '2020/09/28' is end day and '2020/09/14' is start day:

5 * ((DAYS('2020-09-28') -  DAYS('2020-09-14')) / 7) + SUBSTRING('0123444401233334012222340111123400012345001234550', 7 * (DAYOFWEEK_ISO('2020-09-14')-1)
+ DAYOFWEEK_ISO('2020-09-28'), 1)

(traslate to db2 from function in mysql)

edited Sep 22 '20 at 07:10

Vivek Jain

2,730
6
12
27

answered Sep 22 '20 at 05:58

JesusAguire

1

score 0 · Answer 7 · answered Nov 16 '20 at 16:01

The following worked well for me:

-- TABLE WITH ALL DATES (FOR TESTING ONLY)
CREATE TABLE CALENDAR(THEDAY DATE)
-- INSERT INTO CALENDAR VALUES ( CURRENT_DATE - x DAY);
-- INSERT AS MANY DATES YOU WANT FOR TESTING
SELECT * FROM CALENDAR ORDER BY THEDAY DESC

-- TABLE OF HOLIDAY

CREATE TABLE CAL_HOLIDAY(HOLDAY DATE);
DELETE FROM CAL_HOLIDAY
INSERT INTO CAL_HOLIDAY VALUES(DATE('2020-11-11')) -- NOV 11:HOLIDAY IN FRANCE
INSERT INTO CAL_HOLIDAY VALUES(DATE('2020-08-15')) -- AUG 15:HOLIDAY IN FRANCE
SELECT * FROM CAL_HOLIDAY

Once the stage is set:

-- SELECT DATES out of our test table and THE DIFF (with today here)

SELECT THEDAY,
-- DIFFERENCE BETWEEN DATES
DAYS(CURRENT_DATE) - DAYS(THEDAY) 
-- PLACE WEEK ENDS CORRECTLY AND REMOVE AS MANY W-E
- FLOOR((DAYS(CURRENT_DATE) - DAYS(THEDAY) + 8 - DAYOFWEEK(CURRENT_DATE))/7)*2 
-- CORRECT SUNDAY OFFSET
+ FLOOR((7-DAYOFWEEK(THEDAY))/6)
 -- REMOVE NUMBER OF HOLIDAY (if they are not in the W-E)
-(SELECT COUNT(*) FROM CAL_HOLIDAY WHERE 
    HOLDAY BETWEEN THEDAY AND CURRENT_DATE AND 
    DAYOFWEEK(HOLDAY) NOT IN (1,7))
FROM CALENDAR ORDER BY THEDAY DESC

How to get the WORKING day diff in db2 without saturdays and sundays?

7 Answers7

Linked