All Forums Connectivity
nithyanandam 65 posts Joined 10/04
17 Apr 2007
UDF in place of a Macro

HiI've the following macro, which calculates the business hours for a time period leaving out the weekends:CREATE MACRO BHRS (CLOSEDTM TIMESTAMP, STRTDTM TIMESTAMP, BUSHRSPERDAY INTEGER, WKNDHRS INTEGER) AS(SELECT CAST (((:CLOSEDTM - :STRTDTM) DAY(4)) * :BUSHRSPERDAY AS INTEGER) + CAST (ABS (EXTRACT(HOUR FROM :STRTDTM) - EXTRACT(HOUR FROM :CLOSEDTM)) AS INTEGER) - CAST(COUNT(CALENDAR_DATE) * :WKNDHRS AS INTEGER) FROM SYS_CALENDAR.CALENDAR WHERE DAY_OF_WEEK IN (1,7) AND CALENDAR_DATE BETWEEN CAST(:STRTDTM AS DATE) AND CAST(:CLOSEDTM AS DATE););Just wondering, if I can replace the above macro as a UDF, so that this can be called at a row level. Any help & thoughts?Thanks for your time.

trojancjs 17 posts Joined 03/07
30 Apr 2007

Are you asking for some help writing some C code?

nithyanandam 65 posts Joined 10/04
02 May 2007

Not really. I was looking for some inputs on using an equivalent for sys_calendar.calendar table.

joedsilva 505 posts Joined 07/05
03 May 2007

Can't you just join your table with the calendar table on calendar_date = yourtable.datecol ?

nithyanandam 65 posts Joined 10/04
03 May 2007

Yes. That's exactly I did, if you take a look at my macro. But, I was trying to ask if the same logic can be replicated as an UDF so that, Business Objects can use this at the row level.

You must sign in to leave a comment.