Function / Macro Impromptu to calculate working days 1

[jdrulez]

Hello,

I want to have the working days between two days ...
Does somebody know a DLL, macro, function that I can use in my Impromptu report to have this calcul ?!
Example :
working days : 3rd -> 7th January 2003 = 3

(Note : I'm French so I will have to change the code to define the bank holiday...)
Thanks for help

 

[griffindm]

jdrulez,

Giving it some thought, you could do it with a macro and a table that contains the dates of the holidays. The macro would do a running count (via a loop) by incremental days, skipping weekends, and also skipping any dates returned from an ODBC call to the holiday table. It could then write the result back into a single table row via ODBC. A final report could then report on the summary total written out.

Hope this helps,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

 

[Recce]

Hi Dave, do you perhaps have an example of your suggestion or more info for me. I am interrested in trying this.

Thanks

 

[griffindm]

Hi Recce,

I haven't really had a need for this. If I had to do it now I would investigate creating a User-Defined Function in the database. But, you can do it with a macro with the following steps.

1. Create the Holiday table. This would be a table of the full dates of the holidays, as some can shift based on the year.

2. Write the macro. In the macro, gather the dates from the holiday table into an array, using either GetDataValue() or with a SQLRetrieve call using ODBC.

3. Construct two loops in the macro. The outer would loop based on the raw number of days between the start and end date ranges passed. The inner would loop based on the number of rows found in the holiday table.

4. Within the inner loop compare the ith date of the outer loop to each of the holidays in the array. If it is not found, increment a counter by one. If found do nothing.

5. The number of days between the dates is the aggregate value of this counter.

pseudocode:

get start and end dates from user
convert input strings to real dates:
sdate = CVDate(startdate)
edate = CVDate(enddate)
get the number of raw days between the two dates
(I haven't found a more elegant way to do this yet):
cntr = 0
Do while true
tdate = CVDate(sdate) + cntr
if tdate = edate then
exit Do
end if
cntr = cntr+1 'cntr becomes the raw number of days between
Loop

Get number of holidays as column value in report
hcnt
fill array hdates from same report
idate = sdate
initialize a counter:
i = 1
initialize result for net days between:
tdays = 0
for i = 1 to cntr 'do for all raw days between
k = 1
found = 0
for k = 1 to hcnt 'do for each holiday in array
if sdate + i = hdates(k) then 'if day is in array then set variable to skip counter
found = 1
end if
next k
if found = 0 then 'If not found then increment counter
tdays = tdays +1
end if
Next i

The write the result back into a table using a SQLExecute statement.

Not perfect code. Just a conceptual example.

Hope this helps you.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[Recce]

Hi Dave, thanks for your time and for sending this. I will look at this since I have a project coming up where this will come in handy.