Calculate Workdays and exclude weekends in Impromptu?

[glenbrann]

How do I Calculate Workdays and exclude weekends in Impromptu?

Thanks
Glen

 

[ict2000]

One option is: using UDF (User-Defined Function). Check out this document "How To Create User-Defined Functions".

First you need to create a function in your database to return the number of weekdays only. Then follow the instructions in the above document to make the database function visible for selection in Impromptu.

The general logic to calculate the number of weekdays without including Sat and Sun in a database function is: Keep adding 1 day from StartDate until StartDate is Sun and keep subtracting 1 day to EndDate until Endate is Sat. Then multiply DateDiff(week,StartDate,EndDate) by 5 to get the number of weekdays, then add it to the number of weekdays found while doing adding and subtracting above. REMEMBER to add logics to check first hand how many days apart the orginal StartDate and EndDate are before doing the adding and subtracting and deal with each case accordingly.

Your DBA might already have such a similar function.

 

[ALSAP]

Here's one way of doing it.



SELECT SUM(days) "Total Working Days"
FROM (SELECT ((TO_DATE('11-APR-07') + TRUNC((TO_DATE('13-APR-07') - TO_DATE('11-APR-07') + 1)/7)*7 -
1) - TO_DATE('11-APR-07') + 1)*5/7 days
FROM DUAL


UNION ALL
SELECT CASE
WHEN TO_CHAR((TO_DATE('11-APR-07') + TRUNC((TO_DATE('13-APR-07') -
TO_DATE('11-APR-07') + 1)/7)*7 - 1)+ROWNUM, 'D')
NOT IN (1, 7)
THEN 1
ELSE 0
END
FROM (SELECT 'x'
FROM DUAL
GROUP BY CUBE (1,2,3)
)
WHERE (TO_DATE('11-APR-07') + TRUNC((TO_DATE('13-APR-07') - TO_DATE('11-APR-07') + 1)/7)*7 -
1) + ROWNUM <= TO_DATE('13-APR-07')
)




SELECT SUM(days) "Total Working Days"
FROM (SELECT ((TO_DATE('11-APR-07') + TRUNC((TO_DATE('13-APR-07') - TO_DATE('11-APR-07') + 1)/7)*7 -
1) - TO_DATE('11-APR-07') + 1)*5/7 days
FROM DUAL
UNION ALL
SELECT CASE
WHEN TO_CHAR((TO_DATE('11-APR-07') + TRUNC((TO_DATE('13-APR-07') -
TO_DATE('11-APR-07') + 1)/7)*7 - 1)+ROWNUM, 'D')
NOT IN (1, 7)
THEN 1
ELSE 0
END
FROM (SELECT 'x'
FROM DUAL
GROUP BY CUBE (1,2,3)
)
WHERE (TO_DATE('11-APR-07') + TRUNC((TO_DATE('13-APR-07') - TO_DATE('11-APR-07') + 1)/7)*7 -
1) + ROWNUM <= TO_DATE('13-APR-07')
)

 

[ALSAP]

or...


CREATE OR REPLACE function weekenddays(startrange in date, endrange in date)
return number
as
weekenddays number;
BEGIN
SELECT (trunc((trunc(endrange+1) - trunc(startrange))/7) * 2)+
decode(decode((trunc(startrange) - trunc(startrange,'DAY')),
0, decode(mod((trunc(endrange+1) - trunc(startrange)),7),
0,0,7),
((mod( ( trunc(endrange+1) - trunc(startrange) ),7) ) +
(trunc(startrange) - trunc(startrange,'DAY'))))
,7,1,8,2,9,2,10,2,11,2,12,2,13,2,0)
INTO weekenddays
FROM dual;

RETURN weekenddays;
END;

 

[Vivarto]

The other way to do this is to have a table of dates (like a date dimension table) and have a flag column to indicate whether the day is a workday or not, something like:

cal_date dow_name is_work_day
--------- ------------- ------------
1/01/2008 Tuesday N
2/01/2008 Wednesday Y
3/01/2008 Thursday Y
4/01/2008 Friday Y
5/01/2008 Saturday N
6/01/2008 Sunday N
7/01/2008 Monday Y
8/01/2008 Tuesday Y
9/01/2008 Wednesday Y
10/01/2008 Thursday Y
11/01/2008 Friday Y
12/01/2008 Saturday N
13/01/2008 Sunday N
14/01/2008 Monday Y
15/01/2008 Tuesday Y

Note that Jan 1, while a Tuesday, is not a work day - it's a public holiday. Programatic solutions can be complicated and usually can't deal with situations like Easter / Passover / Diwali that don't fall on set dates, a table can. What's more the table only needs to be created once and requires minimal maintenance. If you've got a data warehouse you'll already have a date dimension. Other columns can be used to support other date related features such as accounting calendars.

Impromptu is a reporting tool so I'd be recommending a date table in the reporting database to your DBA if you don't already have one.